Navigation in owned JSON entity

[HppZ]

class A // has table in db
{
    public long Id { get; set; }
    public List<B> Items { get; set; }
}

class B // no table in db
{
    public C Definition { get; set; }
    public string Property1 { get; set; }
}

class C // has table in db
{
    public long Id { get; set; }
    public string Property2 { get; set; }
}

I want to store A.Items as JSON, preserving the properties Property1 of B and Id of C when serializing Items. When querying object A, I want to be able to use the preserved Id property of C in the JSON to retrieve object C and assign it to the Definition field of B.

[roji]

It seems like you're asking for an owned JSON entity (B) to have a navigation out to an entity outside the document (C), which is mapped to its own table. This currently isn't supported.

As a workaround, you can store the Id of C inside B directly as a foreign key (i.e. as a long), and then use LINQ Join to fetch C in the same query.

[HppZ]

1, Will that be supported in the future?
2, Could you please show me some sample code?

[ajcvickers]

@HppZ Sorry for taking a long time to respond. 1. is tracked by #31245. 2. Do you still need some sample code?

[HppZ]

That would be great if you're willing!

[roji]

Sorry for taking so long to reply with a sample. What you're trying to achieve should be doable via the following:

_ = await ctx.A
    .Select(a => new
    {
        A = a,
        Definitions = ctx.C
            .Where(c => a.Items.Select(b => b.DefinitionId).Contains(c.Id))
            .ToList()
    })
    .ToListAsync();

In other words, for each A, query out the rows from C whose Id is in the list of definition IDs from inside A's B.

Full code sample

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

_ = await ctx.A
    .Select(a => new
    {
        A = a,
        Definitions = ctx.C
            .Where(c => a.Items.Select(b => b.DefinitionId).Contains(c.Id))
            .ToList()
    })
    .ToListAsync();

public class BlogContext : DbContext
{
    public DbSet<A> A { get; set; }
    public DbSet<C> C { 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<A>()
            .OwnsMany(a => a.Items, a => a.ToJson());
    }
}

public class A // has table in db
{
    public long Id { get; set; }
    public List<B> Items { get; set; }
}

public class B // no table in db
{
    public string Property1 { get; set; }

    // public C Definition { get; set; }
    public int DefinitionId { get; set; }
}

public class C // has table in db
{
    public int Id { get; set; }
    // public long Id { get; set; }
    public string Property2 { get; set; }
}

The issue tracking allowing navigations out of a complex type is #31245. We can keep this issue for tracking doing the same, if we believe we'll end up doing the same for owned entity types.