Consuming SQL Functions with Entity Framework

Modified September 02, 2024

Tim Deschryver

timdeschryver.dev

A programming language contains built-in functions that can be used to perform specific tasks easily. Just like any other language, all the different SQL dialicts has built-in functions. Within SQL Server you can make use of the T-SQL functions, such as DATEADD, SOUNDEX, GETDATE, and many more.

Ofcourse when you're Entity Framework you will be using C# to write your queries. Doing so, you'll notice that not all these functions are available in the C# or Entity Framework API.

Some of the build-in SQL 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. The reason that some methods do not have a method with Entity Framework is done to keep the Entity Framework API consistent across different database providers.

To leverage the built-in methods, it's possible to 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.

As an example in this blogpost, let's see how to use the SOUNDEX function in Entity Framework.

Adding the SOUNDEX function to Entity Framework link

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))

In the query, you see that the SoundEx method is used in the WHERE clause. To remove the NULL checks in 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();
    }
}

This results 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')

Implementing your own SQL functions link

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 don'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