Views, functions and CTEs in EF Core
EF Core can read from SQL views, call your SQL functions inside LINQ, and run CTE queries. This lesson maps a view to a keyless class, maps scalar and table-valued functions to C# methods, runs a CTE with SqlQuery, and creates the view in a migration.
- StartWrite the SQL objectCreate the view or function in a migration with migrationBuilder.Sql
- Step 2Map it in OnModelCreatingHasNoKey().ToView(...) for a view, HasDbFunction(...) for a function
- Step 3Write LINQ as usualdb.OrderSummaries.Where(...) or db.RecentOrders(42, 30).Where(...)
- Step 4EF Core translatesSELECT ... FROM vw_OrderSummary or FROM dbo.fn_RecentOrders(@p0, @p1)
- EndResults come back as objectsViews and keyless results are read-only and not tracked
The idea in plain words
Views, functions and CTEs let the database do heavy work close to the data. EF Core does not replace them; it can use them. A view becomes a read-only class you query with LINQ, a SQL function becomes a C# method EF Core translates into SQL, and a CTE runs through a raw SQL query.
This keeps big joins and aggregates in SQL Server, where indexes and indexed views make them fast, while your C# code stays typed and simple.
Map a view to a class
A view usually has no primary key, so the class is configured as keyless with HasNoKey. ToView tells EF Core to read from the view and never to create a table for it. Keyless results are never tracked, which also makes them cheap to read.
public class OrderSummary // shaped like the view's columns
{
public int OrderId { get; set; }
public DateTime OrderedAt { get; set; }
public string CustomerName { get; set; } = "";
public int Lines { get; set; }
public decimal Total { get; set; }
}
public class ShopContext(DbContextOptions<ShopContext> options) : DbContext(options)
{
public DbSet<OrderSummary> OrderSummaries => Set<OrderSummary>();
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<OrderSummary>()
.HasNoKey()
.ToView("vw_OrderSummary");
}
}
// LINQ on top of the view: WHERE, ORDER BY and TOP run in SQL Server
var biggest = await db.OrderSummaries
.Where(o => o.OrderedAt >= new DateTime(2026, 10, 1))
.OrderByDescending(o => o.Total)
.Take(20)
.ToListAsync();Call SQL functions from LINQ
Map a scalar function to a method whose body is never run, and a table-valued function to a method that returns IQueryable through FromExpression. You can keep composing LINQ on the function result. The function must return every column of the entity it maps to (here, full Orders rows).
public class ShopContext(DbContextOptions<ShopContext> options) : DbContext(options)
{
public DbSet<Order> Orders => Set<Order>();
public DbSet<Product> Products => Set<Product>();
// dbo.fn_PriceWithTax: EF Core translates calls into SQL
public decimal PriceWithTax(decimal price, decimal rate) => throw new NotSupportedException();
// dbo.fn_RecentOrders: an inline table-valued function returning Orders rows
public IQueryable<Order> RecentOrders(int customerId, int days)
=> FromExpression(() => RecentOrders(customerId, days));
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.HasDbFunction(typeof(ShopContext).GetMethod(nameof(PriceWithTax))!)
.HasName("fn_PriceWithTax");
modelBuilder.HasDbFunction(typeof(ShopContext).GetMethod(nameof(RecentOrders))!)
.HasName("fn_RecentOrders");
}
}
var prices = await db.Products
.Select(p => new { p.Name, Gross = db.PriceWithTax(p.Price, 18) })
.ToListAsync();
var bigRecent = await db.RecentOrders(42, 30)
.Where(o => o.Total > 500)
.OrderByDescending(o => o.OrderedAt)
.ToListAsync();Run a CTE with SqlQuery
Database.SqlQuery maps raw SQL to any class, even one that is not an entity. Interpolated values become parameters, so this is safe from SQL injection. SQL Server does not allow a WITH clause inside a subquery, so do not add LINQ operators such as Where or OrderBy after a CTE query: put them in the SQL. If you need to keep composing, move the CTE into a view or an inline function and map that instead.
public class MonthlyRevenue
{
public DateTime Month { get; set; }
public decimal Revenue { get; set; }
}
int year = 2026;
List<MonthlyRevenue> months = await db.Database.SqlQuery<MonthlyRevenue>($"""
WITH Monthly AS (
SELECT DATEFROMPARTS(YEAR(OrderedAt), MONTH(OrderedAt), 1) AS Month, Total
FROM dbo.Orders
WHERE OrderedAt >= DATEFROMPARTS({year}, 1, 1)
)
SELECT Month, SUM(Total) AS Revenue
FROM Monthly
GROUP BY Month
ORDER BY Month
""").ToListAsync();Create the view or function in a migration
EF Core does not generate views or functions from your classes. Add an empty migration (Add-Migration AddOrderSummaryView in the Package Manager Console) and write the SQL yourself, with a Down method that removes it.
public partial class AddOrderSummaryView : Migration
{
protected override void Up(MigrationBuilder migrationBuilder)
{
migrationBuilder.Sql("""
CREATE OR ALTER VIEW dbo.vw_OrderSummary AS
SELECT o.OrderId, o.OrderedAt, c.Name AS CustomerName,
COUNT(ol.OrderLineId) AS Lines,
SUM(ol.Quantity * ol.UnitPrice) AS Total
FROM dbo.Orders o
JOIN dbo.Customers c ON c.CustomerId = o.CustomerId
JOIN dbo.OrderLines ol ON ol.OrderId = o.OrderId
GROUP BY o.OrderId, o.OrderedAt, c.Name;
""");
}
protected override void Down(MigrationBuilder migrationBuilder)
=> migrationBuilder.Sql("DROP VIEW IF EXISTS dbo.vw_OrderSummary;");
}Common mistakes
- Forgetting ToView, so the next migration tries to create an OrderSummaries table.
- Trying to save changes to a keyless view entity. Views mapped this way are read-only.
- Adding Where or OrderBy after a CTE in SqlQuery or FromSql, which SQL Server rejects.
- Building SQL with string concatenation instead of interpolation, which opens the door to SQL injection.
- A table-valued function that returns fewer columns than the entity it is mapped to.
Interview question
How do you use a SQL view in EF Core?
Create a class with properties matching the view's columns, configure it in OnModelCreating with HasNoKey() and ToView("ViewName"), and expose it as a DbSet. You can then query it with LINQ and EF Core adds filters, sorting and paging to the view query. Keyless entities are not tracked and cannot be saved. The view itself is created with migrationBuilder.Sql in a migration, because EF Core does not generate views.
Want the full details? Read the official Microsoft documentation for this topic.