Entity Framework Core - Database Functions

Part 1: Scalar-Valued Functions (SVF)

Returns a single value (int, string, decimal, bool, etc.). Used in SELECT or WHERE clauses.

The "Best Practice" Approach: HasDbFunction

This method allows you to use the function directly in LINQ queries. EF Core translates your C# method call into SQL.

1. Create the C# Stub

Define a static method in your DbContext or a dedicated helper class. Throw an exception to ensure it is never evaluated client-side.

public class AppDbContext : DbContext
{
    // ... DbSets

    // 1. Define the Stub
    public int CalculateAge(int personId)
        => throw new NotSupportedException();
}

2. Configure the Mapping

In OnModelCreating, register the function.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    // 2. Map the method to the SQL function
    modelBuilder.HasDbFunction(typeof(AppDbContext).GetMethod(nameof(CalculateAge), new[] { typeof(int) }))
        .HasName("fn_CalculateAge") // Name in SQL Database
        .HasSchema("dbo");          // Schema (optional, defaults to dbo)
}

3. Usage in LINQ

You can now use this method inside any LINQ query. It will be translated to SQL.

var adults = await context.Users
    .Where(u => context.CalculateAge(u.Id) >= 18) // Translates to WHERE dbo.fn_CalculateAge(u.Id) >= 18
    .ToListAsync();

---

Part 2: Table-Valued Functions (TVF)

Returns a set of rows (a table). Used in the FROM clause.

Use Case A: The Modern Composable Approach (Recommended)

Available in EF Core 5+, this allows you to chain LINQ operators (.Where(), .OrderBy(), .Skip()) after the function call, and EF will append them to the SQL query.

1. Define the Result Entity

If the function returns a shape that matches an existing table, reuse that class. If it returns a custom shape (DTO), create a class and mark it as "Keyless".

public class UserReport
{
    public string Name { get; set; }
    public decimal TotalSales { get; set; }
}

2. Create the C# Stub

The method must return IQueryable<T>.

public class AppDbContext : DbContext
{
    public DbSet<UserReport> UserReports { get; set; } // Required for the set

    // Stub Method
    public IQueryable<UserReport> GetHighValueUsers(decimal minSpend)
        => FromExpression(() => GetHighValueUsers(minSpend));
}

3. Configure the Mapping

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    // Configure the Keyless Entity
    modelBuilder.Entity<UserReport>()
        .HasNoKey()
        .ToView(null); // Prevents EF from looking for a table named "UserReport"

    // Map the Function
    modelBuilder.HasDbFunction(typeof(AppDbContext).GetMethod(nameof(GetHighValueUsers), new[] { typeof(decimal) }))
        .HasName("tvf_GetHighValueUsers");
}

4. Usage (Composable)

// The SQL generated will be:
// SELECT * FROM dbo.tvf_GetHighValueUsers(@p0) WHERE TotalSales > 5000
var eliteUsers = await context.GetHighValueUsers(1000)
    .Where(u => u.TotalSales > 5000) // This logic runs in the DB, not memory!
    .ToListAsync();

Use Case B: Raw SQL (Legacy/Simple)

If you don't need composition or the mapping setup is too complex for a one-off call.

var minSpend = 1000;
var results = await context.Set<UserReport>()
    .FromSqlInterpolated($"SELECT * FROM dbo.tvf_GetHighValueUsers({minSpend})")
    .ToListAsync();

---

Part 3: Corner Cases & Nuances

1. Handling Nullable Parameters

If your SQL function accepts NULL, your C# parameter must be nullable.

• SQL: CREATE FUNCTION fn_Test (@val int) ... (where @val can be null)

• C#: public int TestFunc(int? val)

• Mapping: EF Core automatically handles the type mapping.

2. Passing Complex Types / Lists (Array Parameters)

Standard SQL functions do not accept arrays easily.

• PostgreSQL: Native array support. You can pass int[] from C# directly to a Postgres function accepting integer[].

• SQL Server: No native array support in scalar functions.

  • Workaround: Serialize the list to JSON strings in C# and deserialize in the SQL function using OPENJSON.

3. Functions Returning Scalar NULL

If a Scalar Function can return NULL, the C# return type must be nullable.

• Wrong: public int GetScore(...) -> If SQL returns NULL, EF throws a runtime exception.

• Correct: public int? GetScore(...)

4. Client-Side Evaluation Risk

If you accidentally call the stub method outside of a LINQ query, it will throw the NotSupportedException.

• Bad: var result = context.CalculateAge(5); (Throws Exception)

• Good: var result = await context.Database.SqlQuery<int>($"SELECT dbo.fn_CalculateAge(5)").FirstOrDefaultAsync();

5. EF Core 8 "Complex Types" (Value Objects)

In EF Core 8, if your TVF returns a "Complex Type" (a group of properties like Address that isn't its own table), you must map the result entity to include it.

// If your TVF returns columns: City, Street, Zip
modelBuilder.Entity<UserReport>().ComplexProperty(u => u.Address);

---

Part 4: Best Practices Checklist

Quick Implementation Summary

1. Write SQL: Create the function in the DB (via Migration).

2. Write C#: Create the method stub in DbContext.

3. Map: Use modelBuilder.HasDbFunction.

4. Register Type: If TVF, register the return type with modelBuilder.Entity<T>().HasNoKey().

5. Use: Call inside LINQ (.Where, .Select) for SVFs, or as a source (context.MyTvF()) for TVFs.