Create a custom SQL expression with Entity Framework, or how to make EF interpret your Value Types

profile
Tim Deschryver
timdeschryver.dev

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.

Tip

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"

Note

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.

Note

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:

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:

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.

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.

Buy Me a Coffee at ko-fi.com PayPal logo

Share this post