Consuming SQL Functions with Entity Framework
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
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
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
To do this, create a new method in the
DbContext, and annotate it with the
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
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
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.
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
I appreciate it if you would support me if have you enjoyed this post and found it useful, thank you in advance.