This article explains how SQL Server functions can be used within .NET Core applications using Entity Framework Core (EF Core). It covers system functions, user-defined functions, and EF Core helper functions with simple and clear examples.
1. System Functions in .NET Core
SQL Server provides built in functions for working with text, numbers, and dates. EF Core translates many common C# methods into these SQL functions automatically.
a) String Functions
Examples of C# → SQL translation:
• u.Name.Length → LEN(Name)
• u.Name.ToUpper() → UPPER(Name)
• u.Name.ToLower() → LOWER(Name)
• u.Name.Substring(0,3) → SUBSTRING(Name,1,3)
using (var context = new AppDbContext())
{
var users = context.Users
.Where(u => u.Name.Length > 5) // Translates to LEN(Name) > 5
.ToList();
}
Examples
b) Numeric Functions
var products = context.Products
.Select(p => new
{
p.Name,
RoundedPrice = Math.Round(p.Price, 2) // ROUND(Price, 2)
})
.ToList();
Examples
ABS() → Math.Abs()
ROUND() → Math.Round()
POWER() → Math.Pow()
• Math.Abs(x) → ABS(x)
• Math.Round(x,2) → ROUND(x,2)
• Math.Pow(x,3) → POWER(x,3)
c) Date & Time Functions
var recentOrders = context.Orders
.Where(o => o.OrderDate >= DateTime.Now.AddDays(-7)) // DATEADD(DAY, -7, GETDATE())
.ToList();
Examples
GETDATE() → DateTime.Now
DATEADD() → AddDays(), AddMonths()
DATEDIFF() → EF.Functions.DateDiffDay(start, end)
d) Aggregate Functions
var totalSales = context.Orders.Sum(o => o.TotalAmount); // SUM(TotalAmount)
var avgRating = context.Products.Average(p => p.Rating); // AVG(Rating)
Examples
SUM(), AVG(), MIN(), MAX(), COUNT()
2. User-Defined Functions (UDFs)
In SQL Server, you can create UDFs, then map them in EF Core to call them inside LINQ.
a) SQL Scalar Function Example
CREATE FUNCTION dbo.fn_GetFullName(@First NVARCHAR(50), @Last NVARCHAR(50))
RETURNS NVARCHAR(101)
AS
BEGIN
RETURN (@First + ' ' + @Last)
END
b) Mapping the Function in EF Core
public class AppDbContext : DbContext
{
public DbSet<User> Users { get; set; }
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.HasDbFunction(() => GetFullName(default, default));
}
var names = context.Users .Select(u => AppDbContext.GetFullName(u.FirstName, u.LastName)) .ToList();
public static string GetFullName(string first, string last) => throw new NotImplementedException();
}
c) Using the Function in LINQ
var names = context.Users
.Select(u => AppDbContext.GetFullName(u.FirstName, u.LastName))
.ToList();
EF Core translates this into:
SELECT dbo.fn_GetFullName([u].[FirstName], [u].[LastName])
FROM [Users] AS [u]
3. Use of Functions in .NET Core
String cleanup before search
Date filtering for reports
Mathematical calculations directly in the database
Encapsulating business logic in UDFs for reuse
Improving performance by reducing data sent from SQL Server
4. EF Core Helpers
EF Core also exposes EF.Functions for direct access to SQL features:
var orders = context.Orders
.Where(o => EF.Functions.Like(o.CustomerName, "%Smith%")) // LIKE operator
.ToList();
Common ones
EF.Functions.Like() → SQL LIKE
EF.Functions.FreeText() → Full-text search
EF.Functions.DateDiffDay(), DateDiffMonth(), etc.
Use Cases
• Cleaning text before searching
• Filtering data by dates (e.g., last 30 days)
• Doing calculations such as tax, totals, or weights inside SQL
• Improving performance by reducing the amount of data processed in C#
Summary
EF Core automatically translates many C# operations into SQL functions. You can also use custom SQL functions and EF helper functions to simplify logic, improve performance, and keep queries clean.