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?