Executing Table-Valued Functions

In this lesson, you will learn how to integrate SQL Server Table-Valued Functions (TVFs) into your Entity Framework Core query pipeline to leverage database-side logic seamlessly.

The Power of Table-Valued Functions

While stored procedures are great for executing commands, Table-Valued Functions (TVFs) are unique because they return a table result set that acts just like a database table or view. EF Core allows you to map these functions to your model, enabling you to use them in LINQ queries. This means you can filter, sort, and project the results of a complex SQL function using standard C# syntax.

Mapping the Function

To integrate a TVF, you first map it in your DbContext using the HasDbFunction method within the OnModelCreating override. You define the function name and map it to a specific method in your context.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.HasDbFunction(typeof(MyDbContext)
        .GetMethod(nameof(GetHighValueOrders), new[] { typeof(decimal) }))
        .HasName("fn_GetHighValueOrders");
}

Querying the Function

Once mapped, you can invoke the function directly within your LINQ queries. Because the TVF returns an IQueryable, EF Core translates your C# calls into a SQL SELECT statement against the function, allowing you to compose further operations (like Where or OrderBy) on top of the results.

public IQueryable<Order> GetHighValueOrders(decimal threshold)
    => FromExpression(() => GetHighValueOrders(threshold));

// Usage in business logic:
var results = context.GetHighValueOrders(500.00m)
                     .Where(o => o.OrderDate > DateTime.Now.AddDays(-7))
                     .ToList();

By using FromExpression, you bridge the gap between your C# code and the database logic. The engine treats the TVF as a source table, ensuring that the final SQL sent to the server is highly efficient and optimized by the database engine itself. This technique is particularly powerful for complex calculations, such as those involving hierarchical data or intricate business rules that are too cumbersome to write in pure LINQ.

Key Takeaway: Mapping TVFs allows you to treat database-side functions as first-class IQueryable sources, enabling you to combine powerful SQL logic with the flexibility of LINQ.