Object equality on object nested in JSON column

[PrgOrcl]

I have the following code below that queries a table with the following structure:

Author {
    id int ,
    name varchar,
    Contact: {
               phone no,
               Address: {
                    city,
                        Country
            }
        }
    }

Here, "Contact" is a json column. We have mapped it to the Contact class in the OnModelCreation method.

using System;
using Microsoft.EntityFrameworkCore;
using System.Linq;
using Microsoft.Extensions.Logging;
using System.Collections.Generic;
using System.Reflection.Emit;
using System.Numerics;
using static System.Net.Mime.MediaTypeNames;
using static System.Runtime.InteropServices.JavaScript.JSType;

namespace SqlServerEFCore
{
  class Program
  {
    static void Main(string[] args)
    {
      try
      {
        using (var db = new MyContext())
        {
          db.Database.EnsureDeleted();
          db.Database.EnsureCreated();

          var data1 = new Author
          {

            Name = "aaa",
            Contact = new ContactDetails
            {
              Address = new Address
              {
                City = "acity",
                Country = "UK",

              },
              Phone = "01632 12345"
            }
          };

          var data2 = new Author
          {

            Name = "bbb",
            Contact = new ContactDetails
            {
              Address = new Address
              {
                City = "bcity",
                Country="Canada",
              },
              Phone = "01632 12346"
            }
          };

          db.Author.Add(data1);
          db.Author.Add(data2);
          db.SaveChanges();

          var address = new Address
          {
            City = "acity",
            Country = "UK",
          };

        // below is the query i want to run
          var query = db.Author.Where(author => author.Contact.Address == address).ToList();               

        }
      }
      catch (Exception e)
      {
        throw;
        Console.WriteLine(e.Message);
      }
    }
  }

  class MyContext : DbContext
  {
    public DbSet<Author> Author { get; set; }
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
      optionsBuilder.UseSqlServer("Data Source = (localdb)\\ProjectModels; Initial Catalog = MySql;")
        .LogTo(Console.WriteLine);
    }

    //public static readonly LoggerFactory _myLoggerFactory = new LoggerFactory(new[] { new Microsoft.Extensions.Logging.Debug.DebugLoggerProvider() });
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
      modelBuilder.Entity<Author>().OwnsOne(
          author => author.Contact, ownedNavigationBuilder =>
          {
            ownedNavigationBuilder.ToJson();
            ownedNavigationBuilder.OwnsOne(contactDetails => contactDetails.Address);
          });
    }
  }

  public class ContactDetails
  {
    public Address Address { get; set; } = null!;
    public string? Phone { get; set; }
  }

  public class Address
  {

    public string City { get; set; }              

    public string? Country { get; set; }           

  }

  public class Author
  {
    public int Id { get; set; }
    public string Name { get; set; }
    public ContactDetails Contact { get; set; }
  }
}

In the linq in the given code, I'm trying to find the rows where the Address field in the Contact column is equal to the "address" I have defined locally. However, on running it I get the following exception :-

Unhandled exception. System.InvalidOperationException: No backing field could be found for property 'Address.ContactDetailsAuthorId' and the property does not have a getter.
   at Microsoft.EntityFrameworkCore.Metadata.IPropertyBase.GetMemberInfo(Boolean forMaterialization, Boolean forSet)
   at Microsoft.EntityFrameworkCore.Metadata.Internal.ClrPropertyGetterFactory.GetMemberInfo(IPropertyBase propertyBase)
   at Microsoft.EntityFrameworkCore.Metadata.Internal.ClrAccessorFactory`1.CreateBase(IPropertyBase propertyBase)
   at Microsoft.EntityFrameworkCore.Metadata.Internal.ClrPropertyGetterFactory.Create(IPropertyBase property)
   at Microsoft.EntityFrameworkCore.Metadata.RuntimePropertyBase.<>c.<Microsoft.EntityFrameworkCore.Metadata.IPropertyBase.GetGetter>b__43_0(RuntimePropertyBase property)
   at Microsoft.EntityFrameworkCore.Internal.NonCapturingLazyInitializer.EnsureInitialized[TParam,TValue](TValue& target, TParam param, Func`2 valueFactory)
   at Microsoft.EntityFrameworkCore.Metadata.RuntimePropertyBase.Microsoft.EntityFrameworkCore.Metadata.IPropertyBase.GetGetter()
   at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.ParameterValueExtractor[T](QueryContext context, String baseParameterName, List`1 complexPropertyChain, IProperty property)
   at lambda_method89(Closure, QueryContext)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteCore[TResult](Expression query, Boolean async, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.GetEnumerator()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at SqlServerEFCore.Program.Main(String[] args) in C:\Users\PRASEGHO\source\repos\SqlServerEfcore\SqlServerEfcore\Program.cs:line 67

My question is - Is such a linq supported in efcore? If yes, then how do we write such a query? And is there any additional code or setup required for this?

EF Core version: 8 and 9 Database provider: (Microsoft.EntityFrameworkCore.SqlServer) Target framework: (NET 8.0) Operating system: Windows IDE: (Visual Studio 2022 17.4)

[cincuranet]

Self contained repro:

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Diagnostics;

using var db = new MyContext();
db.Database.OpenConnection();
db.Database.EnsureCreated();

var data1 = new Author
{
    Name = "aaa",
    Contact = new ContactDetails
    {
        Address = new Address
        {
            City = "acity",
            Country = "UK",

        },
        Phone = "01632 12345"
    }
};
var data2 = new Author
{
    Name = "bbb",
    Contact = new ContactDetails
    {
        Address = new Address
        {
            City = "bcity",
            Country = "Canada",
        },
        Phone = "01632 12346"
    }
};
db.Author.Add(data1);
db.Author.Add(data2);
db.SaveChanges();

Console.WriteLine(db.Model.ToDebugString(Microsoft.EntityFrameworkCore.Infrastructure.MetadataDebugStringOptions.LongDefault));
var address = new Address
{
    City = "acity",
    Country = "UK",
};
var query = db.Author.Where(author => author.Contact.Address == address).ToList();

class MyContext : DbContext
{
    public DbSet<Author> Author { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlite();
        optionsBuilder.LogTo(Console.WriteLine, events: [CoreEventId.QueryExecutionPlanned]);
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Author>().OwnsOne(
            author => author.Contact, ownedNavigationBuilder =>
            {
                ownedNavigationBuilder.ToJson();
                ownedNavigationBuilder.OwnsOne(contactDetails => contactDetails.Address);
            });
    }
}
public class ContactDetails
{
    public Address Address { get; set; } = null!;
    public string? Phone { get; set; }
}
public class Address
{
    public string City { get; set; }
    public string? Country { get; set; }

}
public class Author
{
    public int Id { get; set; }
    public string Name { get; set; }
    public ContactDetails Contact { get; set; }
}

[ajcvickers]

Note for team: looks like it could be a dupe of ~#35147~ #29442. The issue is attempting to get the value of a shadow property from an object that isn't mapped. (Note that complex types don't have this issue.)

[roji]

Yeah, I think the best answer here is to use complex types (when those are available)... As a workaround, it's possible to explicitly construct all the property comparisons in the LINQ query (the same SQL would be produced by EF in any case).