How to turbocharge your Entity Framework queries – Part 3

• Tweet
• Email

• Tweet
• Email

Note: This assumes you have read and understood the fab articles How to turbocharge your Entity Framework queries – Part 1 and Knowing the difference between IEnumerable<T> and IQueryable<T>. If not, please read them first.

It is important to understand where your code is executed when querying with Entity Framework, as this can make a big difference to the time required to execute your queries.

Once you have implemented AsNoTracking (if appropriate, see Part 1), you can then consider the rest of your query.

I recently came across some code that looked something like this (I’m not posting the real code so as not to embarrass the original author). The repository method pulled out the ferrets, and returned an IEnumerable, which has not yet been executed, so no database hit yet…

public IEnumerable<Ferret> GetFerrets() {
  return _context
  .Include(f => f.Gibbon)
  // other includes
  .Where(f => f.Active);
}

This was used in the business logic as follows…

public List<FerretOverview> GetFerrets(DateTime from, DateTime to) {
  return _ferretRepository.GetFerrets()
  .Where(f => f.Born.Date >= from.Date && f.Born.Date <= to.Date)
  .ToList()
  .Select(f => new FerretOverview {
    // Create the overview
  })
  .ToList();
}

The author had assumed that since the repository returned an IEnumerable, which wasn’t enumerated until after the date filtering had been applied, then the date filtering would be run on the server. It turned out that this was a mistake. As the repository method’s return type was IEnumerable, the query was cast as that before being returned. This meant that the full ferrets data set was being pulled from the database, and only then was the date filtering being applied. As the table contained a lot of rows, this was causing the query to take longer than it should.

The fix was simple, but not. The first thing to do was change the return type of the repository method to be IQueryable, which prevented the query from being executed just yet. Then, when the business logic layer applied the date filtering, this sent that filtering to the database, so significantly less data was returned. So what was the problem? Read on dear reader, and let your eyes glaze over!

Knowing when and where your code gets executed

When I tried to run the query, I got a run time exception in the Where clause in the business logic code saying “The specified type member ‘Date’ is not supported in LINQ to Entities.” It turns out that SQL Server doesn’t actually support most of the methods and properties in the DateTime class, which scuppered the date filtering. At this stage, it looked like the benefits of using IQueryable were slipping out of my reach. However, Microsoft were kind to me, and anticipated this problem.

The System.Data.Entity namespace includes the cunningly named DbFunctions class, which contains methods that do translate directly into SQL. Why they couldn’t just change SQL Server to accept the equivalent .NET methods is beyond me, but who am I to question Microsoft’s design decisions eh?

So, all I needed to do was modify the date filtering to use the DbFunctions class methods instead…

.Where(f => DbFunctions.TruncateTime(f.Born) >= from.Date
         && DbFunctions.TruncateTime(f.Born) <= to.Date)

Voila, my execution time went down from about 2 seconds to under 200 milliseconds!

My regular reader will of course realise that this is all too easy to be the end of the story. Sadly, this was correct. I committed the code and kicked off a build… which failed with the message “System.NotSupportedException: ‘This function can only be invoked from LINQ to Entities”

Being old and dopey, I had forgotten to run the unit tests on the business logic class, and when the build server ran them, three of them failed. It turned out that the reason was that when the test tried to run the code, it was trying to call the DbFunctions methods outside of Linq to Entities, which doesn’t work.

So, I had to move the date filtering code into the repository layer. My repository method now looked like this…

public IQueryable<Ferret> GetFerrets(DateTime from, DateTime to) {
  return _context
  .Include(f => f.Gibbon)
  // other includes
  .Where(f => f.Active && DbFunctions.TruncateTime(f.Born) >= from.Date
                       && DbFunctions.TruncateTime(f.Born) <= to.Date)
}

…and my business logic code like this…

public List<FerretOverview> GetFerrets(DateTime from, DateTime to) {
  return _ferretRepository.GetFerrets(from, to)
  .Select(f => new FerretOverview {
    // Create the overview
  })
  .ToList();
}

The disadvantage of this is that repository code should really only do simple data access, not business logic (such as the date filtering). However, sometimes you have to be pragmatic and go with what works.