Tim DeschryverTim

Consuming SQL Functions with Entity Framework

The .NET logo
@tim_deschryver

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.

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.

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

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

Resulting in a cleaner SQL statement.

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.

Conclusion

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.

Support me

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

Buy Me a Coffee at ko-fi.com PayPal logo
Support the blog Share on Twitter Discuss on Twitter Edit on GitHub