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?
Yes, EFCore requires explicit inclusion of relational entities.
var accounts = await dbContext.Accounts.include(account => account.Parent)
.include(account => account.Children)
.ToListAsync();
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 :)