Using Table-Valued Functions

In this lesson, you will learn how to map SQL Server Table-Valued Functions (TVFs) directly into your Entity Framework Core LINQ queries, allowing you to treat complex database logic like standard database tables.

Why use Table-Valued Functions?

While standard Stored Procedures return result sets that are often difficult to compose, TVFs return a table-like result that EF Core can treat as an IQueryable. This means you can apply additional LINQ operators—like .Where(), .OrderBy(), or .Select()—to the function's output after it returns from the database, giving you the best of both worlds: pre-compiled database logic and C# flexibility.

Mapping the Function

To start, you define a method in your DbContext that acts as a bridge. EF Core uses the [DbFunction] attribute to map your C# method to the SQL function.

public class MyDbContext : DbContext
{
    // 1. Define a method that returns an IQueryable
    public IQueryable<ProductInfo> GetTopProducts(int categoryId)
        => FromExpression(() => GetTopProducts(categoryId));

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // 2. Map the function to the database schema
        modelBuilder.HasDbFunction(typeof(MyDbContext).GetMethod(nameof(GetTopProducts)));
    }
}

Note: Ensure your ProductInfo class is configured as a Keyless entity type in OnModelCreating since the result set is transient.

Querying the Result

Once mapped, you can invoke the function directly inside your LINQ queries. EF Core will translate this into a SQL CROSS APPLY or OUTER APPLY behind the scenes:

var results = context.GetTopProducts(5)
                     .Where(p => p.Price > 100)
                     .ToList();

By doing this, the database executes the logic inside the TVF, and EF Core handles the composition of any additional filtering you add in C#. This is significantly more efficient than pulling all records into memory and filtering them with C# logic.

Key Takeaway: Mapping a Table-Valued Function allows you to treat complex, pre-defined database result sets as composable LINQ sources, blending efficient SQL-side logic with the expressive power of C# queries.