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

Published October 10, 2024

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:

 content_paste
var persons = db.Set<Class>().Where(p => p.CustomType.Value.Contains("3FE8DBB6"));

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.

In practice, this results in the following code:

 Person.cs content_paste
[StronglyTypedId(Template.Guid, "guid-efcore")]
public readonly partial struct PersonId { }
 
public class Person
{
    public PersonId Id { get; }
    public string Name { get; }
 
    public Person(PersonId id, string name)
    {
        Id = id;
        Name = name;
    }
}
 
public class PersonService
{
    public Person GetPersonById(PersonId id)
    {
        // ...
    }
}

 Person.cs content_paste
public class Person
{
   public Guid Id { get; }
   public PersonId Id { get; }
    public string Name { get; }
 
   public Person(Guid id, string name)
   public Person(PersonId id, string name)
    {
        Id = id;
        Name = name;
     }
}
 
public class PersonService
{
   public Person GetPersonById(Guid id)
   public Person GetPersonById(PersonId id)
    {
        // ...
    }
}

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.

 content_paste
PersonId personId = new PersonId(...);
var person = db.Set<Person>().Single(p => p.Id == personId);

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.

 content_paste
var persons = db.Set<Person>().Where(p => p.Id.Value.ToString().Contains("3FE8DBB6"));

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:

 content_paste
System.InvalidOperationException: The LINQ expression 'DbSet<Person>()
 .Where(p => p.Id.Value.ToString().Contains("3FE8DBB6"))' could not be translated.
Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly
by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'.
See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

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"

 SqlServerMemberTranslatorProvider content_paste
/// <summary>
///     This is an internal API that supports the Entity Framework Core infrastructure and not subject to
///     the same compatibility standards as public APIs. It may be changed or removed without notice in
///     any release. 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.
/// </summary>
public class SqlServerMemberTranslatorProvider : RelationalMemberTranslatorProvider
{
    ...
}

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.

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.

 ApplicationDb.cs content_paste
public class ApplicationDb : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer(...);
    }
 
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder
            .HasDbFunction(() => typeof(PersonId).GetMember(nameof(PersonId.Value)))
            .HasTranslation(args => new SqlFunctionExpression(
                functionName: "",
                arguments: args,
                nullable: false,
                argumentsPropagateNullability: [false],
                type: typeof(string),
                typeMapping: null));
    }
}

When we run the query again, we see that the exception is gone, but that it now throws a different exception:

 content_paste
System.ArgumentException: The DbFunction 'Type.GetMember' defined on type 'Type'
must be either a static method or an instance method defined on a DbContext subclass.
Instance methods on other types are not supported.

Luckily, the exception provides us with a clear message on how it can be resolved. We decided to use a static method:

 StronglyTypedIdValue.cs content_paste
public static class StronglyTypedIdValue
{
    public static string InnerValue(this PersonId value)
    {
        throw new NotImplementedException();
    }
}

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:

 content_paste
var persons = db.Set<Person>().Where(p => p.Id.InnerValue().Contains("3FE8DBB6"));

Lastly, this change also impacts the DdFunction. The DdFunction method also needs to be updated to use the InnerValue() method:

 ApplicationDb.cs content_paste
public class ApplicationDb : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer(...);
    }
 
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder
            .HasDbFunction(() => StronglyTypedIdValue.InnerValue(default))
            .HasTranslation(args => new SqlFunctionExpression(
                functionName: "",
                arguments: args,
                nullable: false,
                argumentsPropagateNullability: [false],
                type: typeof(string),
                typeMapping: null));
    }
}

When we run the query again, we see that a new exception is thrown:

 content_paste
System.InvalidOperationException: The parameter 'value' for the DbFunction 'StronglyTypedIdValue.InnerValue(PersonId)' has an invalid type 'PersonId'.
Ensure the parameter type can be mapped by the current provider.

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.

 ApplicationDb.cs content_paste
public class ApplicationDb : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer(...);
    }
 
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder
            .HasDbFunction(() => StronglyTypedIdValue.InnerValue(default))
            .HasTranslation(args => new SqlFunctionExpression(
                functionName: "",
                arguments: args,
                nullable: false,
                argumentsPropagateNullability: [false],
                type: typeof(string),
                typeMapping: null))
            .HasParameter("value")
            .HasStoreType("NVARCHAR(255)");
    }
}

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:

 content_paste
SELECT TOP(2) [p].[Id], [p].[Name]
FROM [Persons] AS [p]
WHERE ([p].[Id]) LIKE N'%3FE8DBB6%'

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.

 ApplicationDb.cs content_paste
public class ApplicationDb : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer(...);
    }
 
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder
            .HasDbFunction(() => StronglyTypedIdValue.InnerValue(default))
            .HasTranslation(args => new SqlFunctionExpression(
                functionName: "",
                arguments: args,
                nullable: false,
                argumentsPropagateNullability: [false],
                type: typeof(string),
                typeMapping: null))
            .HasParameter("value")
            .HasStoreType("NVARCHAR(255)");
    }
}

 StronglyTypedIdValue.cs content_paste
public static class StronglyTypedIdValue
{
    public static string InnerValue(this object value)
    {
        // This is method is not invoked at runtime, instead it's translated to a SQL expression
        throw new NotImplementedException();
    }
}

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 the value argument, which is the value of the strongly-typed id.
• nullable: Configures if the function can return null, in our case this is false because the Value property of the strongly-typed ID is never null.
• argumentsPropagateNullability: Configures if the arguments can be null, in our case this is false because the Value property of the strongly-typed ID is never null.
• type: The return type of the function, in our case this is string.
• typeMapping: to be honest, I'm not entirely sure what this does, but it can be null in our case.

This SqlFunctionExpression is then used to translate the p.Id.InnerValue() in the EF query below.

 content_paste
var persons = db.Set<Person>().Where(p => p.Id.InnerValue().Contains("3FE8DBB6"));

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:

 content_paste
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder
        .HasDbFunction(() => StronglyTypedIdValue.InnerValue(default))
        .HasTranslation(args => new SqlFunctionExpression(
            functionName: "CONVERT",
            arguments: args.Prepend(new SqlFragmentExpression("nvarchar(255)")),
            nullable: false,
            argumentsPropagateNullability: [false, false],
            type: typeof(string),
            typeMapping: null))
        .HasParameter("value")
        .HasStoreType("NVARCHAR(255)");
}

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 case CONVERT.
• arguments: The arguments that are passed to the function, in this case the value argument, but we prepend the nvarchar(255) argument to it. This to create the following valid SQL expression: CONVERT(nvarchar(255), value).
• argumentsPropagateNullability: Because an additional argument is added, the argumentsPropagateNullability is updated to [false, false].

This translates into the following SQL query:

 content_paste
SELECT TOP(2) [p].[Id], [p].[Name]
FROM [Persons] AS [p]
WHERE CONVERT(nvarchar(255), [p].[Id]) LIKE N'%123%'

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!

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