Create a custom SQL expression with Entity Framework, or how to make EF interpret your Value Types
The problem link
In this blog post, I want to offer a solution to a problem we encountered while querying data with Entity Framework using "lookup" methods on Value Type properties. In short, we want to execute the following query:
Spoiler alert... this won't work out of the box. To address this issue, we need to help Entity Framework how it can interpret this C# code and explain how it can be translated to a SQL Expression. write (and configure) a DbFunction that translates the some C# code to a SQL expression.
Strongly-type id value types link
Before I discuss the solution, I want to take a quick detour to give you some more context and explain how we got there.
At my current project, we're currently busy migrating our project to use strongly-typed IDs.
Instead of using primitive types like long
or Guid
, we use a custom type that wraps the primitive type.
The benefit of doing this is that we can't accidentally mix up the IDs of different entities.
We're using the StronglyTypedId NuGet Package to create these strongly-typed IDs easily.
The StronglyTypedId package uses source generators to generate the code behind the strongly-typed IDs, helping you to battle primitive obsession in your codebase.
See the blog post series Using strongly-typed entity IDs to avoid primitive obsession by Andrew Lock, who's also the author of StronglyTypedId, for more information about strongly-typed IDs and the StronglyTypedId library.
In practice, this results in the following code:
This approach prevents us from accidentally passing the wrong ID, an ID from a different entity, which can lead to bugs that are hard to track down.
The encountered issue link
The move to strongly-typed IDs is working well for us, but we've recently run into an issue when querying our database. Writing simple queries that query data by its ID using the equality operator are working fine.
However, it becomes more a different story when we need to query data using different lookup techniques, which require more complex expressions. For example, let's say we want to query all persons containing a specific part of the ID.
In theory, this should work, as Entity Framework Core should be able to translate the ToString()
and Contains()
methods to SQL.
At least, that's what we thought... In practice, when we run this code, we get the following exception:
We were surprised by this exception, as accessing .Value
just works for nullable types.
For example, if the Id
property was a nullable Guid
type instead of the strongly-typed ID PersonId
, the same query would just execute correctly.
To work around this issue we wanted to know how Entity Framework works, as this could help us to solve the encountered issue.
How EF translates C# to SQL link
Entity Framework is open-source, so we can look at how it handles the translation of expressions.
After some digging, we were led to the SqlServerMemberTranslatorProvider
class, in which you can notice that Entity Framework works with a collection of translators to translate C# members to SQL expressions, specifically for Sql Server. An example of such a translator is the SqlServerStringMemberTranslator
, which translates the C# string.Length
member to LEN
in SQL.
We see that the SqlServerMemberTranslatorProvider
class derives from RelationalMemberTranslatorProvider
. This base class contains a collection of translators that are shared between all relational databases. One of the translators is the NullableMemberTranslator
that translates expressions for nullable types. The translator checks if the member is a nullable type, and if so, it provides a translation for the .Value
and .HasValue
properties.
This was exactly what we needed to know, as we could use this information to create our translator for the Value
property of the strongly-typed ID.
But after some more research, we hit a wall and found out that we can't just extend the translator's collection and add our translator to it.
Then what about creating our own TranslatorProvider
?
It turns out that this is possible (GitHub issue), but on the other hand it is not recommended.
This warning is clearly mentioned in the summary description of SqlServerMemberTranslatorProvider
, "You should only use it directly in your code with extreme caution and knowing that doing so can result in application failures when updating to a new Entity Framework Core release"
You can also check out how methods are translated in SqlServerMethodCallTranslatorProvider, e.g. DateTime.AddDays
, string.Contains
, and more.
HasDbFunction
to the rescue
link
Instead of extending the built-in translators, Entity Framework provides an extensible way to allow us to map C# code to SQL expressions.
It turns out we can use the HasDbFunction
method for this task.
In another blog post Consuming SQL Functions with Entity Framework I've already explained how you can use SQL functions that aren't natively supported by Entity Framework, e.g. the SOUNDEX
function. This blog post takes it a step further by creating your own SQL expression.
We can take our knowledge about the built-in translators and apply it to our own custom translator, using the HasDbFunction
method.
To create a DbFunction
, add the function within the OnModelCreating
method of your DbContext
class.
Similar to the nullable translator, we hook into the Value
member of the PersonId
type.
Using HasTranslation
we create a SqlFunctionExpression
that represents the SQL expression, in the case of the strongly-typed ID, this means that we map the Value
member to a SqlFunctionExpression
that represents the value of the Id.
In this case, this can be an empty implementation, as we just want to tell Entity Framework that it should use the Value
member of the PersonId
type.
When we run the query again, we see that the exception is gone, but that it now throws a different exception:
Luckily, the exception provides us with a clear message on how it can be resolved. We decided to use a static method:
The static method received the strongly-typed ID as an argument, and has the return type of string
.
We don't need an implementation for this method, as we only want to use it to translate our code.
Because the InnerValue()
returns a string, the query also becomes simpler.
Instead of calling ToString()
on the Value
property, we can now call the InnerValue()
method directly.
The query now looks like this:
Lastly, this change also impacts the DdFunction
.
The DdFunction
method also needs to be updated to use the InnerValue()
method:
When we run the query again, we see that a new exception is thrown:
This exception is thrown because the PersonId
type cannot be mapped to a SQL type.
To fix this issue, we provide the correct SQL type via the HasStoreType
(and HasParameter
) method.
Now, finally, when we run the query, we see that the query is successfully translated to SQL, and that the query returns the expected results. The SQL query that is executed looks as follows:
Going over the solution in detail link
We went over the solution very quickly, so let's explain it in more detail.
We need to give the compiler a little hand of how it can translate a C# expression into a valid SQL expression. In our case this means that it should be able to translate a member access of a value type (a strongly-typed ID).
To do this, we create a DbFunction
to map the Value
property of the strongly-typed ID to a SqlFunctionExpression
.
Because Entity Framework only supports static methods or methods within the DbContext
class, we also introduced a static method that receives the strongly-typed ID as an argument and returns a string. This method is not required to have an implementation, as it's not invoked at runtime, but is used to translate the C# expression to a SQL expression.
To make the previous solution more generic, we can replace the StronglyTypedId PersonId
with the object
type.
HasDbFunction
receives an expression that represents the method that should be translated.
Within the HasTranslation
method, the C# function is translated to a SqlFunctionExpression
.
The translation receives an args
as an argument, which represents the arguments that are passed to the StronglyTypedIdValue.InnerValue()
extension method.
In our case, this is the strongly-typed ID PersonId
.
If more arguments are passed to the method, they will be added to the args
argument as well.
The SqlFunctionExpression
is built up using the following arguments:
functionName
: The name of the SQL function, in our case an empty string because we don't require a function.arguments
: The arguments that are passed to the function, in our case just thevalue
argument, which is the value of the strongly-typed id.nullable
: Configures if the function can returnnull
, in our case this isfalse
because theValue
property of the strongly-typed ID is nevernull
.argumentsPropagateNullability
: Configures if the arguments can benull
, in our case this isfalse
because theValue
property of the strongly-typed ID is nevernull
.type
: The return type of the function, in our case this isstring
.typeMapping
: to be honest, I'm not entirely sure what this does, but it can benull
in our case.
This SqlFunctionExpression
is then used to translate the p.Id.InnerValue()
in the EF query below.
Of course, this works because the inner type of our strongly-typed ID PersonId
is a Guid
, which can be converted to a string
.
When the inner type is a number, you need to convert the number to a string first.
An example of this is shown below:
In the example above, we use the CONVERT
function to convert the number to a string.
For this, the following changes are made:
functionName
: The name of the SQL function, in our caseCONVERT
.arguments
: The arguments that are passed to the function, in this case thevalue
argument, but we prepend thenvarchar(255)
argument to it. This to create the following valid SQL expression:CONVERT(nvarchar(255), value)
.argumentsPropagateNullability
: Because an additional argument is added, theargumentsPropagateNullability
is updated to[false, false]
.
This translates into the following SQL query:
Conclusion link
In this blog post, we've seen how we configure Entity Framework to translate a C# expression, which isn't by the compiler, to a SQL expression.
To come up with this solution, we've looked at how Entity Framework translates expressions using its built-in translators, but it turned out that this is meant for internal use only and is not recommended to extend.
The better solution is to create a DbFunction
that maps a C# expression to a SQL expression.
While configuring the DbContext
, we created a DbFunction
within the OnModelCreating
method.
Our function maps the Value
property of the strongly-typed ID into a valid SQL expression using a SqlFunctionExpression
.
To create a valid hook that Entity Framework can use, we created a static method that receives the strongly-typed ID as an argument.
Resources link
Most of the information to come up with this solution was found in the Entity Framework GitHub issues.
- GitHub issue: EF Translators
- GitHub Issue: EF configure proper type mapping
- Learn DBFunction documentation
- Blog: Going down the rabbit hole of EF Core and converting strings to dates
Feel free to update this blog post on GitHub, thanks in advance!
Join My Newsletter (WIP)
Join my weekly newsletter to receive my latest blog posts and bits, directly in your inbox.
Support me
I appreciate it if you would support me if have you enjoyed this post and found it useful, thank you in advance.