Consuming SQL Functions with Entity Framework

Published January 09, 2023

Tim Deschryver

timdeschryver.dev

SQL Server (or another database) has many built-in functions that can be used in queries. But when you use Entity Framework, these functions can't be used in your Entity Framework queries.

Some of the functions have a .NET counterpart, for example, to add days to a date you use DATEADD in TSQL, and in C# this becomes DateTime.AddDays. But some of them don't, for example SOUNDEX in TSQL (to find similar strings instead of a full-match) does not have a C# version. This is done to keep the Entity Framework API consistent across different database providers.

As a workaround, you can write your own C# version of the missing behavior, or you can make it yourself easy and register these SQL functions within the DBContext. Once a function is registered you can use it in your Entity Framework queries in a database provider-agnostic way.

For example, let's register the SOUNDEX function. To do this, create a new method in the DbContext, and annotate it with the DbFunction attribute.

 MyDbContext.cs content_paste
public class MyDbContext : DbContext
{
    public MyDbContext(DbContextOptions<MyDbContext> options)
        : base(options)
    {
    }
 
    [DbFunction(Name = "SoundEx", IsBuiltIn = true)]
    public static string SoundEx(string input)
    {
        throw new NotImplementedException();
    }
}

Notice that we don't implement the method, we just provide the correct signature.

You can now use the SoundEx method and call the function in your Entity Framework query. In the next example, we use a name query parameter to filter customers based on their name by using the SoundEx method.

 Program.cs content_paste
var builder = WebApplication.CreateBuilder(args);
 
var connectionString = builder.Configuration.GetConnectionString("SqlConnection");
builder.Services.AddDbContext<MyDbContext>(ctx => ctx.UseSqlServer(connectionString));
 
var app = builder.Build();
 
app.MapGet("/customers", ([FromQuery] customerName, MyDbContext ctx) => {
    return ctx.Customers
        .Where(c => MyDbContext.SoundEx(c.Name) == MyDbContext.SoundEx(customerName))
        .ToListAsync();
});
 
app.Run();

When the query is executed this generates the following SQL statement. To give you an idea, if we search for customers with the name "Timothy", this also matches the following names "Timmothy", "Timoteo", or "Timotheo".

 customers.sql content_paste
SELECT [c].[Id], [c].[Name]
FROM [Customers] AS [c]
WHERE SoundEx([c].[Name]) = SoundEx(N'Timothy') OR ((SoundEx([c].[Name]) IS NULL) AND (SoundEx(N'Timothy') IS NULL))

Or, to clean up the SQL statement, we can set the IsNullable property of the DbFunction attribute to false.

 MyDbContext.cs content_paste
public class MyDbContext : DbContext
{
    public MyDbContext(DbContextOptions<MyDbContext> options)
        : base(options)
    {
    }
 
    [DbFunction(Name = "SoundEx", IsBuiltIn = true, IsNullable = false)]
    public static string SoundEx(string input)
    {
        throw new NotImplementedException();
    }
}

Resulting in a cleaner SQL statement.

 customers.sql content_paste
SELECT [c].[Id], [c].[Name]
FROM [Customers] AS [c]
WHERE SoundEx([c].[Name]) = SoundEx(N'Timothy')

Besides the built-in functions, you can also create your own SQL functions and add them to the DbContext in a similar way. For your custom functions, set the IsBuiltIn property to false and you can also define the function's schema.

I wouldn't recommend creating your own SQL functions, but registering existing functions can be useful while porting an existing codebase to a newer version.

 MyDbContext.cs content_paste
public class MyDbContext : DbContext
{
    public MyDbContext(DbContextOptions<MyDbContext> options)
        : base(options)
    {
    }
 
    [DbFunction(Name = "SoundEx", IsBuiltIn = true, IsNullable = false)]
    public static string SoundEx(string input)
    {
        throw new NotImplementedException();
    }
 
    [DbFunction(Name = "MyCustomFunction", Schema = "dbo", IsBuiltIn = false)]
    public static int MyCustomFunction(int input)
    {
        throw new NotImplementedException();
    }
}

Conclusion link

Overall, using DbFunctions can be a useful way to take advantage of specific database features in your Entity Framework queries, but there is one caveat. Because you're using database specific function it becomes harder to port to other databases if you ever want to do this. In our example, the SOUNDEX function does not exist in all databases, or it has a different name. So, when you ever want to switch to another database you should revisit all the added DbFunctions.

Incoming links

• Entity framework features I wish I knew earlier

Feel free to update this blog post on GitHub, thanks in advance!

Support me

I appreciate it if you would support me if have you enjoyed this post and found it useful, thank you in advance.

Share this post on

Twitter LinkedIn