Entity Framework Core - recursive parent/child linking

entity-framework-core

Question

I have an "account" table that includes a string foreign-key ("parent_guid") to it's "parent" account (if one exists). I would like to create an entity that knows its parent, as well as all of its children.

Here is my entity:

[Table(name:"accounts")]
public class Account
{
    [Key]
    public string Guid { get; set; }
    public string Name { get; set; }

    [Column("guid")]
    public string accountGuid { get; set; }

    [Column(name: "parent_guid")]
    public string parentGuid { get; set; }

    [ForeignKey("parentGuid")]
    public Account Parent { get; set; }

    [InverseProperty("Parent")]
    public ICollection<Account> Children { get; set; }
}

Here's my dbContext:

public DbSet<Split> Splits { get; set; }
public DbSet<Account> Accounts { get; set; }

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

My query is through the 'splits' context as the source table, but I end up returning Accounts. Maybe there's a better way?

When I query for an Account by Guid, I get a result, but 'Parent' and 'Children' are always null, even though 'parentGuid' contains the correct value, and I have confirmed that there should be child records.

Any idea how to make this work, either through annotations or fluent API?

1
1
10/8/2018 11:32:34 PM

Accepted Answer

Yes, EFCore requires explicit inclusion of relational entities.

var accounts = await dbContext.Accounts.include(account => account.Parent)
                                       .include(account => account.Children)
                                       .ToListAsync();

EDIT

As per the edits to the question, this is one way to Eager Load relational entities, but I cannot speak to the efficiency of this query without knowing the relations and indexes.

public IQueryable<Split> 
  FindAllByAccountGuidsPostedBefore(IEnumerable<string> accounts, 
                                                       DateTime endDate) {
  using (reportContext) { 
     return reportContext.Splits.Include(s => s.Account)
                                .ThenInclude(a => a.Parent)
                                .ThenInclude(a => a.Children)
                                .Where(s => accounts.Contains(s.Account.Guid) 
                                 && s.Transaction.postDate < endDate); 
     } 
} 

One way to obtain that information is by looking at the console when this query is run to find the SQL statement(s) this produces, or by asking someone who is more experienced in Relational Databases :)

2
10/9/2018 12:07:08 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow