Calling Views, Stored Procedures and Functions in EF Core

When you work with a database, you usually let EF Core build the SQL for you. You write context.Products.Where(...) and EF Core turns it into a SELECT statement. That works most of the time. But sometimes your database already has ready-made building blocks: views, stored procedures, and functions. This guide shows you how to call all three from EF Core in a simple, friendly way.

A everyday analogy first

Think about cooking at home.

• A view is like a jar of ready-made spice mix. Someone already combined many spices for you. You just open the jar and use it. You cannot change what is inside the jar, but you can sprinkle it on your food.
• A stored procedure is like a pressure cooker recipe written on a card. You give it some ingredients (parameters), press start, and it does many steps for you. You do not see each step. You just get the final dish.
• A function is like a small kitchen tool, such as a lemon squeezer. You put one thing in, you get one thing out. It does one small job and gives back one value.

Your kitchen (the database) already has these tools. EF Core is the cook who knows how to use them. Let me show you how.

What each one is, in plain words

Before the code, here is a quick table so the words are clear.

Now let me walk through each one.

Part 1: Calling a database view

A view is a saved query. Imagine your boss often asks for "active customers in Mumbai." Instead of writing that query again and again, your database admin saves it as a view called vw_ActiveMumbaiCustomers. Now anyone can read from it like a table.

A view usually has no primary key. That is fine. In EF Core you map it as a keyless entity type. Keyless means "no key," and EF Core treats it as read-only. It will never try to insert, update, or delete it.

Here is a model class for the view:

public class ActiveCustomer
{
    public string FullName { get; set; } = string.Empty;
    public string City { get; set; } = string.Empty;
    public int OrderCount { get; set; }
}

Now we tell EF Core that this class maps to a view. We do this inside OnModelCreating:

public class ShopDbContext : DbContext
{
    public DbSet<ActiveCustomer> ActiveCustomers => Set<ActiveCustomer>();
 
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<ActiveCustomer>(eb =>
        {
            eb.HasNoKey();                              // no primary key
            eb.ToView("vw_ActiveMumbaiCustomers");      // point to the view
            eb.Property(v => v.FullName).HasColumnName("full_name");
        });
    }
}

Two important points:

1. ToView assumes the view already exists in the database. EF Core migrations will not create the view for you. You (or your DBA) create the view with plain SQL.
2. Because it is keyless, you can only read from it.

Now using it feels just like a normal table:

var topCustomers = await context.ActiveCustomers
    .Where(c => c.OrderCount > 5)
    .OrderByDescending(c => c.OrderCount)
    .ToListAsync();

Notice the nice part: you can still use LINQ on top of a view. EF Core adds your Where and OrderBy on top of the view's query. This works because a view is just a SELECT, so the database can compose more SQL around it.

Part 2: Calling a stored procedure

A stored procedure is a saved set of SQL steps. It can take parameters, do several things, and give back rows or nothing. There are two situations, and EF Core has a method for each.

When the procedure returns rows

Say you have a stored procedure sp_GetCustomersByCity that returns customer rows. You use FromSql (or FromSqlInterpolated on older EF Core versions). FromSql arrived in EF Core 7.0 and is the friendly, safe choice.

var city = "Pune";
 
var customers = await context.Customers
    .FromSql($"EXEC sp_GetCustomersByCity {city}")
    .ToListAsync();

Look closely. The $ makes this an interpolated string. EF Core does not paste Pune into the SQL text. Instead it creates a real SQL parameter. This protects you from SQL injection, which is when a bad value sneaks extra commands into your query. Always prefer this style.

There is one trap to remember. SQL Server cannot compose more SQL on top of a stored procedure call. So this will fail:

// This breaks on SQL Server, because EF Core tries to wrap the proc.
var bad = await context.Customers
    .FromSql($"EXEC sp_GetCustomersByCity {city}")
    .Where(c => c.IsVip)          // EF tries to add SQL around the proc
    .ToListAsync();

The fix is to call AsEnumerable() (or AsAsyncEnumerable()) right after FromSql. This tells EF Core to stop adding SQL and do the rest of the filtering in memory:

var good = context.Customers
    .FromSql($"EXEC sp_GetCustomersByCity {city}")
    .AsEnumerable()               // stop composing SQL here
    .Where(c => c.IsVip)          // now this runs in C# memory
    .ToList();

When the procedure does not return rows

Some procedures just do work, like archiving old orders. They do not return a result set. For these, use ExecuteSql. It returns the number of rows affected, not data.

int daysOld = 365;
 
int affected = await context.Database
    .ExecuteSqlAsync($"EXEC sp_ArchiveOldOrders {daysOld}");
 
Console.WriteLine($"Archived {affected} orders.");

Here is a simple table comparing the two methods so you can choose quickly.

Part 3: Calling a function

Database functions come in two flavors.

Scalar functions

A scalar function returns one value. Think of a function fn_GetDiscount(customerId) that returns a discount percent. The neat trick in EF Core is that you can map a C# method to a database function and then use it inside LINQ.

public class ShopDbContext : DbContext
{
    // This C# method stands in for the DB function.
    public decimal GetDiscount(int customerId)
        => throw new NotSupportedException(); // never actually runs in C#
 
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder
            .HasDbFunction(() => GetDiscount(default))
            .HasName("fn_GetDiscount");
    }
}

Now you can call it inside a query, and EF Core translates it into SQL:

var deals = await context.Customers
    .Where(c => context.GetDiscount(c.Id) > 10)
    .ToListAsync();

The function never runs in C#. EF Core sees GetDiscount and writes fn_GetDiscount into the SQL instead. The whole thing runs in the database, which is fast.

Table-valued functions

A table-valued function returns a table of rows. You map it to a class (often keyless) and use it like a method that gives a query you can filter further:

public IQueryable<OrderSummary> OrdersByYear(int year)
    => FromExpression(() => OrdersByYear(year));

Reading a single scalar with SqlQuery

Sometimes you just want one column of simple values, like a list of counts or names, not full entity objects. For that, EF Core gives you SqlQuery. It returns scalar (simple) types without you mapping any entity.

var ids = await context.Database
    .SqlQuery<int>($"SELECT CustomerId FROM Orders WHERE Total > {1000}")
    .ToListAsync();

SqlQuery<int> reads a column of integers. You can use string, decimal, DateTime, and other simple types too. It is the easiest way to grab raw scalar values.

Putting it together: the full picture

Let me show how a request flows through all the pieces. Imagine an API that loads a customer dashboard. It reads from a view, calls a procedure, and uses a function, all in one request.

Safety: always parameterize

The biggest rule across all of these is to never build SQL by gluing strings together. Use interpolated strings with FromSql, ExecuteSql, and SqlQuery. EF Core turns your values into safe parameters.

// GOOD: value becomes a real SQL parameter
var name = userInput;
var rows = context.Customers.FromSql($"EXEC sp_Find {name}");
 
// RISKY: FromSqlRaw with string building can allow SQL injection
var bad = context.Customers
    .FromSqlRaw("EXEC sp_Find '" + userInput + "'"); // do not do this

If you must use FromSqlRaw, pass parameters separately as SqlParameter objects. Never concatenate user input into the query text.

When should you use each one?

Here is a short guide based on real projects.

• Use a view when you have a complex SELECT that you reuse a lot. It keeps your C# clean and lets the DBA tune the SQL.
• Use a stored procedure when you have heavy logic, batch jobs, or steps that should run close to the data. They are great for "do this whole task" buttons.
• Use a scalar function when you have a small formula you want to use inside many queries, like a discount or a tax amount.
• Use a table-valued function when you want a parameterized, reusable query that returns rows you can still filter.
• Use SqlQuery when you just need a quick list of simple values and do not want to define a class.

One honest note: do not reach for raw SQL too early. Normal LINQ queries are easier to read, easier to test, and safer. Use these tools when LINQ cannot express what you need, or when the database can do the job much faster. If you find your queries are slow, first check simpler fixes like indexes and avoiding the N+1 problem before moving logic into procedures.

A small checklist before you ship

When you add one of these to your project, run through this list:

1. Does the database object (view, procedure, function) actually exist? Migrations will not create views or procedures mapped with ToView or HasDbFunction.
2. Are you using interpolated strings so values become parameters?
3. For procedures that return rows, did you remember AsEnumerable() if you need extra filtering on SQL Server?
4. For keyless views, did you call HasNoKey()?
5. Are you using the right method: FromSql for rows, ExecuteSql for no rows, SqlQuery for scalars?

Quick recap

• A view is a saved SELECT. Map it as a keyless entity with HasNoKey() and ToView("name"). It is read-only.
• A stored procedure that returns rows is called with FromSql using an interpolated string. Add AsEnumerable() if you need more LINQ on SQL Server.
• A stored procedure that returns no rows uses ExecuteSql, which gives back the count of affected rows.
• A scalar function can be mapped with HasDbFunction and used inside LINQ; EF Core translates it to SQL.
• SqlQuery reads simple scalar values like int or string without an entity class.
• Always parameterize. Use interpolated strings, not string concatenation, to stay safe from SQL injection.
• Reach for these tools only when LINQ cannot do the job or when the database is clearly faster.

References and further reading

• SQL Queries - EF Core (Microsoft Learn)
• Keyless Entity Types - EF Core (Microsoft Learn)
• Entity Types - EF Core (Microsoft Learn)
• Calling Views, Stored Procedures and Functions in EF Core (Anton Martyniuk)