Expression vs Func with Entity Framework

Sometimes developers don't know whether they should use a Func<> or an Expression<Func<>> with the Entity Framework and LINQ. The distinction was critical in a situation I faced today.

Our application was having performance problems, and Red Gate's excellent ANTS profiling tool pointed to a method that, reduced to its essence, was like what you see below. Context is an Entity Framework context, and MyEntities is one of the entity tables in the context. 

IEnumerable<MyEntity> LoadMyEntities(Expression<Func<MyEntity, bool>> predicate)
{
    return Context.MyEntities.Where(predicate);
}

The idea is that a caller can pass in a predicate in the form of a Lambda. The compiler will turn the Lambda into a LINQ Expression, which can be passed to the method in the 'predicate' parameter.

int id;
// Set the id somehow and then...
var theEntity = LoadMyEntities(e => e.UniqueId == id).Single();

The profiler told us that LoadMyEntities was being called many, many times and it was taking a large fraction of our CPU time. The simple change below solved the problem. Can you guess why?

public IEnumerable<MyEntity> LoadMyEntities(Func<MyEntity, bool> predicate)
{
    return Context.MyEntities.Where(predicate);
}

The parameter is now a Func<> instead of an Expression<Func<>>. The reason this makes a difference is that a predicate that's in the form of an Expression is passed to SQL server, but a predicate that's passed as a Func is not. Normally, you'd want SQL Server to do as much for you as possible, and an Expression would be the right choice, but in this case we'd like to pre-load the entire table in the context -- which is exactly what a Func will do. (This the same point I made in Falling in Love with LINQ, Part 7.) Step by step...

1. The Where extension method has two flavors. One extends IQueryable and takes an Expression parameter. The other extends IEnumerable and takes a Func.
2. Because 'predicate' is now a Func, the Where that extends IEnumerable is used.
3. The Entity Framework's fluent interface for constructing SQL queries is based on IQueryables, not IEnumerables. Therefore, the fluency stops just before the Where. The part of the statement that gets passed to the Entity Framework is just Context.MyEntities.
4. Context.MyEntities therefore returns the entire table to the context.
5. The entire table is now filtered with the predicate, and the value we really want is returned.
6. The next time the method is called, the Entity Framework realizes that the record we want is already in the context. (In my case, we were querying by the primary key, and EF is apparently smart enough to know that if there's a record in the context with that ID, it won't find an additional such record in the database.) Since we don't go out to SQL Server, we save lots of time. Obviously there are occasions when you would not want this, but in our case it was exactly what we wanted. The table was relatively small, and the same context was queried hundreds of times.

In the original version, the predicate was an Expression, so the compiler used the Where that extends IQueryable. The predicate was thus passed to SQL Server, which dutifully returned just one row to the context. The next time we called LoadMyEntities, Entity Framework had to call SQL Server again.

The change from Expression to Func gave us a 6-fold reduction in CPU usage! But again, your situation may call for an Expression. The important thing is to know the difference, and now you do!

Link: http://fascinatedwithsoftware.com/blog/post/2012/01/10/More-on-Expression-vs-Func-with-Entity-Framework.aspx

Read More »

Handler "has a bad module "ManagedPipelineHandler" in its module "

Ran command:

%windir%\Microsoft.NET\Framework64\v4.0.30319\aspnet_regiis.exe -i

If I had been on a 32 bit system, it would have looked like the following:
%windir%\Microsoft.NET\Framework\v4.0.21006\aspnet_regiis.exe -i

Read More »