Entity Framework 6 Lazy Loading and Query Navigation Properties
Trigger warning: I am a former NHibernate user, moving to EF.
Just ran into my first major frustration with Entity Framework 6. In this abstract base class , I have the basic functionality for an account.
The account has a set of transactions. To determine the available account balance, I simply deduct the total expenses and undisclosed charges from the total deposits. This is pure business logic associated with all types of accounts.
To get the totals, I have several properties that filter transactions by type and summarize the amount of transactions. See TotalDepositAmount for an example of a couple of ways I've tried.
I am using Lazy Loading in EF, and what I expected from EF was to query the database to get the total value of the deposit transactions. Instead, EF fetches ALL transactions for the account and loads them into memory and then linq filters them. WTF.
I only found this problem because I was doing some benchmarking on my sample application and pumping 30k transactions (the full amount possible) to the account. Attempting ToString my account is currently shutting down the app ...
After digging around, I found that the only way to query the navigation property is when you select the parent or access the dbcontext inside your object .. so the property will look something like this.
public decimal TotalDepositAmount
{
get
{
return context.Entry(this).Reference("Transactions").Query()
.OfType<Deposit>().Sum(d => d.Amount);
}
}
But of course I don't have access to the context inside my object and it will completely pollute the purity of my entities ...
So the experts at EF. What I am missing. How can I achieve my goal here by having pure domain objects. What work or templates can I use?
source to share
This assumes that your base class transactions have a property which is a list of contributions
context.Transactions.Where(x => x.Deposit.Count > 1).Sum(d => d.Amount);
I tested this using
db.Database.Log = s => System.Diagnostics.Debug.WriteLine($"SQL: {s}");
and placed it before the line above. If you look at your output, you can see that transactions are limited by the where clause in the subquery.
source to share