EF Core 7 Json column projections - LINQ expression could not be translated

[JackPSmith]

Hey Guys,

I have an issue with mapping an EF Core 7 Json column into an class. I am presented with the following exception.

System.InvalidOperationException: The LINQ expression 'JsonQueryExpression(p.Addresses, $.AddressList)' 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'.

The code below is the projection that is mapping the queried AddressesData class to the Addresses class. (Which the Addresses is an EF Core 7 Json column, see DBContext at the bottom)

public static class AddressesDataExpressions
{
    public static class Projections
    {
        private static Expression<Func<AddressesData, Addresses>> Projection()
        {
            return a => new()
            {
                AllAddresses = a.AddressList.AsQueryable().AsEnumerable().Select(ad => new Address
                {
                    City = ad.City,
                    CountryCode = ad.CountryCode,
                    FirstLine = ad.FirstLine,
                    PostCode = ad.PostCode,
                    SecondLine = ad.SecondLine
                }).ToList(),
                PrimaryAddressIndex = a.Primary
            };
        }
        private static Func<AddressesData, Addresses>? _project;
        [Expandable(nameof(Projection))]
        public static Addresses Project(AddressesData data)
        {
            _project ??= Projection().Compile();

            return _project(data);
        }
    }
}

Below is the method that contains the EF Query

    public async Task<CustomerSettings?> GetSettingsAsync(int customerId, CancellationToken cancellationToken = default)
    {
        var customerSettings = await _appDbContext.Customers
            .Where(c => c.ResourceId == customerId)
            .Select(c => new CustomerSettings
            {
                Addresses = ADE.Projections.Project(c.Addresses),
                Privacy = CPE.Projections.Project(c.Privacy),
                SocialMedia = new()
                {
                    Facebook = c.SocialMedia.Facebook,
                    Instragam = c.SocialMedia.Instragam,
                    Twitter = c.SocialMedia.Twitter
                }
            })
            .FirstOrDefaultAsync(cancellationToken);

        return customerSettings;
    }

However, as you can see in the above code, I'm also using a projection for Privacy which I've converted back to an original Relational Database format with a table instead of Json column to test this issue, and this works without any issues.

I was just wondering if there's currently no support for EF Core 7 Json columns?

Below is the AddressesData database model class & the Addresses it is being mapped into.

public class AddressesData
{
    public int? Primary { get; set; }
    public ICollection<AddressData> AddressList { get; set; } = new List<AddressData>();
}

public class Addresses
{
    public Addresses()
    {
        AllAddresses = new List<Address>();
    }
    public Addresses(IEnumerable<Address> addresses)
    {
        AllAddresses = new List<Address>();
        AllAddresses.AddRange(addresses);
    }

    public int? PrimaryAddressIndex { get; set; }
    public List<Address> AllAddresses { get; set; }
}

And here's the EF Db context config too

public class AppDbContext : DbContext
{
    public AppDbContext(DbContextOptions<AppDbContext> options) : base(options)
    {

    }

    public DbSet<ResourceData> Resources { get; set; }
    public DbSet<DepartmentData> Departments { get; set; } = null!;
    public DbSet<PersonData> People { get; set; } = null!;
    public DbSet<StaffMemberData> StaffMembers { get; set; } = null!;
    public DbSet<CustomerData> Customers { get; set; } = null!;
    public DbSet<CustomerPrivacyData> CustomerPrivacyData { get; set; } = null!;

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<DepartmentData>().OwnsOne(p => p.Address, options => options.ToJson());

        // Have to use TPH if we're using a base class with JSON columns, TPC is not currently supported
        modelBuilder.Entity<PersonData>().OwnsOne(p => p.SocialMedia, options => options.ToJson());
        modelBuilder.Entity<PersonData>().OwnsOne(p => p.Addresses, builder =>
        {
            builder.ToJson();
            builder.OwnsMany(a => a.AddressList);
        });

        modelBuilder.Entity<StaffMemberData>().OwnsMany(p => p.Certifications, options => options.ToJson());
        modelBuilder.Entity<StaffMemberData>().OwnsMany(p => p.Titles, options => options.ToJson());

        //modelBuilder.Entity<CustomerData>().OwnsOne(p => p.Privacy, options => options.ToJson());
    }
}

Thanks in advance!

[TheConstructor]

Just a first impression: .AsQueryable().AsEnumerable() looks suspicious. Generally EF Core is only able to translate Expression-stuff, as used by the IQueryable-methods; IEnumerable on the other hand uses compiled Funcs, that aren't easily translated. Can you remove .AsQueryable().AsEnumerable() and see, if it changes anything? If the compiler objects, try removing only .AsEnumerable() or explicitly casting the lambda inside the Select to Expression<Func<..., Address>> (replacing ... by the type of items in AddressList).

[JackPSmith]

I still receive the same error after removing .AsQueryable().AsEnumberable() and also removing just .AsEnumerable()

However, for your suggestion of explicitly casting the lambda expression, could you elaborate a little more as I'm afraid I'm not following you. In my case it would be Expression<Func<AddressData,Address>>