How to map and use SQL Server user defined function with EF Core when using value objects?

Question

I want to call a user defined SQL Server function and use as parameter a property that is a value object. The EF Core documentation shows only samples with primitive types. I can't manage to create a working mapping.

The entities of our business domain need to support multi-language text properties. The text is provided by the user. We created a multi-language value object (ML<T>) that internally uses a dictionary. In the database multi-language properties are saved as JSON in a nvarchar(max) column. An EF Core converter handles the conversion to and from string data type.

This is a simplified country entity just to comprehend the test code below:

public class Country : VersionedEntity
{
   public string CountryId { get; set; }
   public ML<CountryName> Name { get; set; }
}

A sample row in the database looks like this:

DEU | [{"language":"de-DE","value":"Deutschland"},{"language":"en-US","value":"Germany"},{"language":"it-IT","value":"Tedesco"}]

Text (i.e. a product description) might not be translated in all supported languages. Therefore the users can define a language preference list. The best matching language will be displayed.

Since paging requires sorting in the database, I created a user defined function GetLanguage in SQL Server that returns the best matching language for each row and allows sorting for each user's language preference.

I declared the function in the DbContext

public string? GetLanguage(string json, string preferredLanguages)
    => throw new NotSupportedException($"{nameof(GetLanguage)} cannot be called client side");

and mapped it

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.HasDbFunction(typeof(AccountsDbContext)
        .GetMethod(nameof(GetLanguage),
        new[] { typeof(string), typeof(string) })!);
}

I tried to call the function in a test LINQ query

string preferredLanguages = "de-DE,en-US,fr-FR";    
List<string?> list = _dbContext.Country.Select(c => _dbContext.GetLanguage(c.Name, preferredLanguages)).ToList();

It does not compile. c.Name creates an error, because it is of type ML<CountryName> and not of type string as the first parameter of the function is defined.

I was expecting that EF Core would not convert the property, because the function runs on the server and just needs to use the table column of the database. It should create SQL like

Select dbo.GetLanguage(name, 'de-DE,en-US,fr-FR');

Is there a way to tell EF Core to just use the table column?

For testing I tried to change the function declaration to

public string? GetLanguage(ML<CountryName> json, string preferredLanguages)
    => throw new NotSupportedException($"{nameof(GetLanguage)} cannot be called client side");

and the mapping to

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.HasDbFunction(typeof(AccountsDbContext)
        .GetMethod(nameof(GetLanguage),
        new[] { typeof(ML<CountryName>), typeof(string) })!);
}

The code compiled, but I get a runtime error.

System.InvalidOperationException : The parameter 'json' for the DbFunction 'App.Accounts.Persistence.AccountsDbContext.GetLanguage(App.SharedKernel.Domain.ML<App.SharedKernel.Domain.CountryName>>,string)' has an invalid type 'ML<CountryName>'. Ensure the parameter type can be mapped by the current provider.

The types are identical, one just is fully qualified and the other not. I don't understand why this error occurs and how I can fix it.

It seems a general issue when a value object with a converter is used. EF Core should create SQL that uses the DB function and the table column, because everything should run on the server. How can I achieve this?

Answer 1

On https://github.com/dotnet/efcore/issues/28393 an answer was provided.

However, we don't currently have good user facing-APIs for this kind of complex case (and in addition, #25980 may temporarily prevent this from working altogether).

However, if you're only going to pass columns to the function, and not e.g. parameters, then it should possible to have the function accept an object-typed parameter, and specify the store type explicitly in the function mapping

So a workaround is to use object as type for the DbFunction definition and declare the database type:

modelBuilder.HasDbFunction(typeof(AccountsDbContext)
    .GetMethod(nameof(GetLanguage), new[] { typeof(object), typeof(string) })!,
    builder =>
    {
        builder.HasParameter("json").HasStoreType("varchar(max)");
        builder.HasParameter("preferredLanguages");
    });

Then call the function with object type conversion:

string preferredLanguages = "de-DE,en-US,fr-FR";    
List<string?> list = _dbContext.Country.Select(c => _dbContext.GetLanguage((object)c.Name, preferredLanguages)).ToList();

It creates clean SQL:

SELECT [dbo].[GetLanguage]([c],[CountryName], @__preferredLanguages_1