.net core - Table relationships in EFCore are not included in query

Question

I am trying to run this query: var test = (await _db.TableA.Include(c=> c.TableB).Include(c=>c.TableC).ToListAsync(); The models are as such:

[Serializable]
[Table("TableA")]
public class TableA
{
    [Key]
    public Guid TableASpecialIdentifier { get; set; }

    public ICollection<TableB> TableB { get; set; }
    public TableC TableC{ get; set; }
}

[Serializable]
[Table("TableB")]
public class TableB
{
    [Key]
    public Guid TableBSpecialIdentifier { get; set; }
    public Guid TableASpecialIdentifier { get; set; }

    [ForeignKey("TableASpecialIdentifier")]
    public TableA TableA { get; set; }
}

[Serializable]
[Table("TableC")]
public partial class TableC
{
    [Key]
    public Guid TableCSpecialIdentifier { get; set; }
    public Guid? TableASpecialIdentifier { get; set; }

    [ForeignKey("TableASpecialIdentifier")]
    public TableA TableA { get; set; }
}

When I run it as noted above, nothing comes back. If I run var test = (await _db.TableB.Include(c=> c.TableA).ToListAsync(); I get results, same with var test = (await _db.TableC.Include(c=> c.TableA).ToListAsync(); producing results as well. But trying to include anything into TableC doesnt work. I have also tried removing the Data Annotations and use the Fluent API like:

            modelBuilder.Entity<TableB>()
                .HasKey(c => c.TableBSpecialIdentifier);

            modelBuilder.Entity<TableA>()
                .HasKey(f => f.TableASpecialIdentifier);

            modelBuilder.Entity<TableC>()
                .HasKey(c => c.TableCSpecialIdentifier);

            modelBuilder.Entity<TableB>()
                .HasOne(c => c.TableA)
                .WithMany(g => g.TableB)
                .HasForeignKey(s => s.TableASpecialIdentifier);

            modelBuilder.Entity<TableA>()
                .HasOne(i => i.TableC)
                .WithOne(c => c.TableA)
                .HasForeignKey<TableC>(f => f.TableASpecialIdentifier);

This has the same end result as before. So I tried writing it in LINQ with similar results:

var test = (from A in _db.TableA
                        join C in _db.TableC on C.TableASpecialIdentifier equals A.TableASpecialIdentifier
                        join B in _db.TableB on A.TableASpecialIdentifier equals B.TableASpecialIdentifier
                        select C).ToListAsync();

I'm not receiving any errors. This is what I'm trying to accomplish written in SQL:

Select c.* from TableA a 
   join TableB b on a.TableASpecialIdentifier = b.TableASpecialIdentifier 
   join TableC c on a.TableASpecialIdentifier = c.TableASpecialIdentifier 

I'm using .net core and EFcore. Any ideas on what I'm doing wrong? Sorry in advance for the crummy table names!

question from:https://stackoverflow.com/questions/65927722/table-relationships-in-efcore-are-not-included-in-query

Answer 1

If you want to run your query you have to put TableC key as a foreign key of TableA:

public class TableA
{
    [Key]
    public Guid TableASpecialIdentifier { get; set; }

    public Guid TableCSpecialIdentifier { get; set; 

    public ICollection<TableB> TableB { get; set; }
   
   [ForeignKey("TableCSpecialIdentifier")]
    public TableC TableC{ get; set; }
}


public partial class TableC
{
    [Key]
    public Guid TableCSpecialIdentifier { get; set; }
   
     [InverseProperty(nameof(TableA.TableC))]
     public virtual ICollection<TableA> TableAs { get; set; }
}

dbcontext:

modelBuilder.Entity<TableA>(entity =>
{
                     entity.HasOne(d => d.TableC)
                    .WithMany(p => p.TableA)
                    .HasForeignKey(d => d.TableCSpecialIdentifier)
                    .OnDelete(DeleteBehavior.ClientSetNull);
 }