How to translate a custom method on a type to SQL to improve performance on a JSON column in EF8?

[michaldivis]

Question

I'm attempting to use the new JSON column functionality for storing translations in a JSON column, as opposed to storing the in a separate table. My first attempt was to store translations in a Dictionary<byte, string> where the byte would be the language ID and a string the value, but that currently doesn't work, as described in #30707.

So I have the following class that is being mapped to a JSON column.

public class Translation
{
    public string CZ { get; set; }
    public string EN { get; set; }

    public string? GetValueOrDefault(Language language) => language switch
    {
        Language.CZ => CZ,
        Language.EN => EN,
        _ => null,
    };

    public static Translation Create(string cz, string en) => new()
    {
        CZ = cz,
        EN = en,
    };
}

When I use the GetValueOrDefault method in a query, the entire JSON column is parsed and the method is called in memory.

This query:

var localizedCountries = dbContext.Countries
   .AsNoTracking()
   .Select(c => new CountryResponse
   {
       Id = c.Id,
       Code = c.Code,
       Name = c.NameTranslation.GetValueOrDefault(Language.CZ)!,
   })
   .ToList();

Produces the following SQL:

 SELECT [c].[Id], [c].[Code], [c].[NameTranslation]
 FROM [flat].[Country] AS [c]

Whereas if I grab the CZ property directly, like so:

var localizedCountries2 = dbContext.Countries
   .AsNoTracking()
   .Select(c => new CountryResponse
   {
       Id = c.Id,
       Code = c.Code,
       Name = c.NameTranslation.CZ,
   })
   .ToList();

That produces:

 SELECT [c].[Id], [c].[Code], JSON_VALUE([c].[NameTranslation], '$.CZ') AS [Name]
 FROM [flat].[Country] AS [c]

Is there a way to translate my GetValueOrDefault method to SQL so it would also grab the property directly in SQL using JSONVALUE?

Also on a side note, I thought that whenever you use a projection to a non-tracked object in a query, the query is automatically a non-tracking once, but I'm getting the following exception when I omit the AsNoTracking() call on those example queries:

System.InvalidOperationException: 'JSON entity or collection can't be projected directly in a tracked query. Either disable tracking by using 'AsNoTracking' method or project the owner entity instead.'

Repro

Here's the full code to repro (requires a connection string to a running SQL server instance):

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;
using Microsoft.Extensions.Logging;

namespace JsonColumnTranslationsDemo;

public static class FlatDemo
{
    public static void Run(string connectionString)
    {
        var options = new DbContextOptionsBuilder<AppDbContext>()
            .UseSqlServer(connectionString)
            .LogTo(Console.WriteLine, LogLevel.Information)
            .Options;

        var dbContext = new AppDbContext(options);

        dbContext.Database.EnsureDeleted();
        dbContext.Database.EnsureCreated();

        var czechia = new Country
        {
            Code = "CZ",
            NameTranslation = Translation.Create("Česko", "Czechia"),
        };

        var japan = new Country
        {
            Code = "JP",
            NameTranslation = Translation.Create("Japonsko", "Japan"),
        };

        dbContext.Add(czechia);
        dbContext.Add(japan);

        dbContext.SaveChanges();

        // NOTE: this grabs the entire NameTranslation JSON column and extracts the CZ value in memory
        var localizedCountries = dbContext.Countries
           // System.InvalidOperationException: 'JSON entity or collection can't be projected directly in a tracked query. Either disable tracking by using 'AsNoTracking' method or project the owner entity instead.'
           .AsNoTracking()
           .Select(c => new CountryResponse
           {
               Id = c.Id,
               Code = c.Code,
               Name = c.NameTranslation.GetValueOrDefault(Language.CZ)!,
           })
           .ToList();

        foreach (var localizedCountry in localizedCountries)
        {
            Console.WriteLine($"Id: {localizedCountry.Id}, Code: {localizedCountry.Code}, Name: {localizedCountry.Name}");
        }
    }

    public class AppDbContext : DbContext
    {
        public DbSet<Country> Countries { get; set; }

        public AppDbContext(DbContextOptions<AppDbContext> options)
             : base(options)
        {
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.ApplyConfigurationsFromAssembly(typeof(AppDbContext).Assembly);
        }
    }

    public class Country
    {
        public int Id { get; set; }
        public string Code { get; set; }
        public Translation NameTranslation { get; set; }
    }

    public class CountryResponse
    {
        public int Id { get; set; }
        public string Code { get; set; }
        public string Name { get; set; }
    }

    public enum Language : byte
    {
        CZ = 1,
        EN = 2,
    }

    public class Translation
    {
        public string CZ { get; set; }
        public string EN { get; set; }

        public string? GetValueOrDefault(Language language) => language switch
        {
            Language.CZ => CZ,
            Language.EN => EN,
            _ => null,
        };

        public static Translation Create(string cz, string en) => new()
        {
            CZ = cz,
            EN = en,
        };
    }

    public class CountryConfiguration : IEntityTypeConfiguration<Country>
    {
        public void Configure(EntityTypeBuilder<Country> builder)
        {
            builder.ToTable(nameof(Country), "flat");

            builder.OwnsOne(c => c.NameTranslation, translationBuilder =>
            {
                translationBuilder.ToJson();
            });
        }
    }
}

Provider and version information

EF Core version: 8.0.0
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 8.0
Operating system: Windows 10 22H2
IDE: Visual Studio 2022 17.8.1

[ajcvickers]

Is there a way to translate my GetValueOrDefault method to SQL so it would also grab the property directly in SQL using JSONVALUE?

The GetValueOrDefault method is opaque to EF Core. There is no way for EF to know which properties of the NameTranslation object will be used, so the entire object must be created.

Also on a side note, I thought that whenever you use a projection to a non-tracked object in a query, the query is automatically a non-tracking once, but I'm getting the following exception when I omit the AsNoTracking() call on those example queries:

This is by-design. The idea is that people might not realize than projected owned entity types are not tracked by default, while projected non-owned entity types are.