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

[KillerBoogie]

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?

[ajcvickers]

/cc @divega
/cc @roji

[KillerBoogie]

My colleague found a first workaround using a cast to object and string to satisfy the compiler:

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

EF Core produced the following SQL code:

SELECT [dbo].[GetLanguage](CAST([c],[CountryName] AS nvarchar(max), @__preferredLanguages_1

The cast is unnecassary, because CountryName is of type nvarchar(max), but it produces the desired result. Is there any option that avoids the cast?

[roji]

To summarize, you're trying to pass a value-converted property to a user-defined function; in order to satisfy the compiler, the .NET method must accept the pre-converted type, but EF then doesn't know what to do with it, since the parameter doesn't have the proper type mapping configured (with the value converter).

Ideally, this would be doable by specifying the type mapping on the function parameter:

modelBuilder.HasDbFunction(typeof(BlogContext).GetMethod(nameof(Foo))!)
    .HasParameter("p")
    .Metadata.TypeMapping = // Set up type mapping with the value converter

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:

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

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

Repro with workaround

await using var ctx = new BlogContext();
await ctx.Database.EnsureDeletedAsync();
await ctx.Database.EnsureCreatedAsync();

_ = ctx.Blogs.Where(b => ctx.Func(b.Name) == "foo").ToList();

public class BlogContext : DbContext
{
    public DbSet<Blog> Blogs { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseSqlServer(@"Server=localhost;Database=test;User=SA;Password=Abcd5678;Connect Timeout=60;ConnectRetryCount=0;Encrypt=false")
            .LogTo(Console.WriteLine, LogLevel.Information)
            .EnableSensitiveDataLogging();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Blog>().Property(b => b.Name)
            .HasConversion(n => n.Wrapped, s => new(s));

        modelBuilder.HasDbFunction(typeof(BlogContext)
            .GetMethod(nameof(Func), new[] { typeof(StringWrapper) })!)
            .HasParameter("wrapper").HasStoreType("varchar(max)");
    }

    public string Func(StringWrapper wrapper)
        => throw new NotSupportedException();
}

public class Blog
{
    public int Id { get; set; }
    public StringWrapper Name { get; set; }
}

public class StringWrapper
{
    public StringWrapper(string wrapped)
        => Wrapped = wrapped;

    public string Wrapped { get; set; }
}

/cc @smitpatel

[KillerBoogie]

Thanks for your answer. I will try out the options.

The parameter has a proper type mapping with a value converter. General reading and writing to the property work. As you can see in the error message above, when I use the multi-language type the type with full namespace is compared to the same one without the namespace. Still EF Core thinks they don't match.

[KillerBoogie]

The sample code above does not compile

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

'DbFunctionParameterBuilder' does not contain a definition for 'HasParameter' and no accessible extension method 'HasParameter' accepting a first argument of type 'DbFunctionParameterBuilder' could be found (are you missing a using directive or an assembly reference?).

It seems that the code should be like this:

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

I tested it and it works! Thanks a lot!

[roji]

Yep, make sense (wrote that blindly).

Leaving open to possibly track extended parameter configuration APIs for type mappings.

[KillerBoogie]

I just discovered another issue. Our multi-langage type ML is generic. With the above workaround, I would need to define the function for each ML. I tried to use object instead of ML and it still works.

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

[roji]

Yep, object should work fine, or you can have your Generic ML extend a non-generic one (or implement an interface), and accept that. That would constrain your method to only be used with ML.

[ajcvickers]

May be covered by #28393 #4978 . May want to add something to #4319.

/cc @roji

[ajcvickers]

Duplicate of #4978