# 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.

```csharp
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.

```csharp
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.

```csharp
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".

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

2\. Create the C# Stub

The method must return IQueryable<T>.

```csharp
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**

```csharp
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)**

```csharp
// 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.

```csharp
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(...)` -&gt; 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.

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

---

### **Part 4: Best Practices Checklist**

| **Category** | **Best Practice** | **Why?** |
| --- | --- | --- |
| **Mapping** | Always use `HasDbFunction`. | Allows LINQ composition. `FromSql` is opaque to the LINQ translator. |
| **Migrations** | **Create Empty Migration.** | EF Core does **not** generate the SQL `CREATE FUNCTION` script for you. You must create an empty migration (`dotnet ef migrations add AddFunctions`) and write `migrationBuilder.Sql("CREATE FUNCTION...")` in the `Up` method. |
| **Naming** | Match C# parameters names to SQL parameters. | EF Core binds by name by default. If your C# param is `id` and SQL is `@customerId`, the binding may fail or require explicit configuration. |
| **Performance** | Avoid Scalar Functions in `Select`. | `Select(x => context.MyFunc(x))` often forces row-by-row execution (N+1) in older SQL versions. TVFs used in `Join` or `From` are generally faster. |
| **Result Types** | Use `HasNoKey()`. | Return types for TVFs are usually views/DTOs, not tracked entities. Using `.HasNoKey()` prevents EF from trying to track changes or save data back. |

### **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.