Entity Framework Core - recursive parent/child linking

Question

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?

James

View more on Stack Overflow

Avin Kavish · Accepted Answer · 10/9/2018 12:07:08 AM

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 :)

Entity Framework Core - recursive parent/child linking

Question

Fastest Entity Framework Extensions

Accepted Answer

EDIT

Performance

Expression Evaluator

Related