Lazy Loading and Virtual Proxy Querying in Entity Framework 4

I’ve been kicking the tyres on Entity Framework 4, and have become a bit ambivalent about a few of it’s features – lazy loading and the virtual proxy implementation.

I’m using EF 4 code first in a service tier with DTO’s between the tiers. A portion of the domain looks as follows:

image

As you can see, the model is pretty simple. Modelling double-entry booking keeping, each transaction consists of at least two entries, each of which are in a different account. You can imagine that an Account will over time contain many thousands of entries.

I want to implement a service query to return the entries in an account between two dates. My initial implement was something like:

public List GetAccountEntriesForDates(int accountID, DateTime dtFrom, DateTime dtTo){
  ILedgerAccountRepository ledgerRepository = ServiceLocator.Current.GetInstance();
            
  LedgerAccount account = ledgerRepository.Get(accountID);

  return account.Entries.Where(la => 
    la.Transaction.TransactionDate > dtFrom
    && lla.Transaction.TransactionDate < dtTo).ToList();
}

Which seems reasonable enough. But when I ran and profiled it I was unpleasantly surprised to see that for each and every LedgerEntry in the account, a query was being run against the database to get the Transaction for the LedgerEntry so that the Linq critieria could be evaluated:

image

This would be crippling for a for a large account.

The way to avoid this would be eager load the Transaction for each LedgerEntry, but unlike NHibernate, Entity Framework does not provide that level of granularity with regard to loading strategies. With Entity Framework, you either lazy load everything or you eager load everything, and eager loading, I can attest, is the shorter path to damnation.

I then re-worked the method so that instead of using the property of the Account, and thereby invoking the virtual lazy loading proxy, I used the relevant ObjectContext instead:

public List GetAccountEntriesForDates(int accountID, DateTime dtFrom, DateTime dtTo){
  LedgerContext context = ServiceLocator.Current.GetInstance();

  return context.LedgerEntries.Where(la =>; la.LedgerAccountID == accountID 
                && la.Transaction.TransactionDate > dtFrom
                && la.Transaction.TransactionDate < DateTime.Now).ToList();
}

Only one query is made to database with the expected inner join between LedgerEntry and Transaction.

image

What i can draw from this experience is that the virtual proxies for the lazy loaded collections defeat the Linq Provider efficiencies of IQueryable<TEntity> , transforming the queries to IEnumerable<TEntity> instead, which is really, really disappointing.

So the takeaway for me is to not perform Queries against the virtual collections of entities unless you want your application to have serious problems. Instead query the object context directly as that will invoke the SQL Linq provider for sensible direct queries against the database.

It might be a good time to spruik the benefits of Repositories, which I actually use to wrap these sorts of queries up…but I’ll do that next time.


kick it on DotNetKicks.com

About these ads
This entry was posted in Entity Framework and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s