Expressions versus Delegates in LINQ to SQL Performance

Recently, I was working on a class that stored a delegate and later used that delegate to retreive a LINQ to SQL object from either the database or an EntitySet, depending on the situation. Unfortunately, the performance for the first case was terrible — I believe it was returning the entire table and then applying the delegate to each row in memory.

Here’s the relevant code (original version):

public abstract Func<D, bool> GetDbKeyQueryFunction(E entity);
//...
D dbEntity;
if ( isRoot ) {
	dbEntity = context.GetTable<D>().SingleOrDefault(GetDbKeyQueryFunction(entity));
} else {
	dbEntity = DbParentSet.Invoke(dbParent).SingleOrDefault(GetDbKeyQueryFunction(entity));
}

Here’s a typical implementation of GetDbKeyQueryFunction:

public override Func<SomeTable, bool> GetDbKeyQueryFunction(SomeEntity entity) {
	return dbEntity => dbEntity.Id == entity.Id;
}

Now before I created this abstract class, I had specific code that used a (seemingly) identical lambda expression to retrieve a row from the database quite quickly:

int id = 11;
dataContext.SomeTable.SingleOrDefault(dbEntity => dbEntity.Id == id));

When I hovered over SingleOrDefault in the IDE, I noticed that there were two overloads, one that took Expression<Func> and another that took Func. On the surface, these don’t seem much different. In fact, the reason I ended up using Func in my abstract class was because Expression is abstract and I couldn’t just put new Expression() around my lambda expession.

In practice, the difference is huge, as I mentioned above regarding the performance. It appears that LINQ to SQL can optimize expressions but cannot optimize delegates. The tricky part is all I had to do was change:

public abstract Func<D, bool> GetDbKeyQueryFunction(E entity);

to:

public abstract Expression<Func<D, bool>> GetDbKeyQueryFunction(E entity);

Surprisingly (to me, anyway), you don’t have to do anything in the implementing code other than modify the method signature to match. The body of the method remains unchanged, but like magic, the compiler is now creating an Expression instead of a Delegate for us.

Performance for the first case (querying the database) was back up to par. However, there was another minor hurdle to overcome.

public abstract Func<D, bool> GetDbKeyQueryFunction(E entity);
//...
D dbEntity;
if ( isRoot ) {
	dbEntity = context.GetTable<D>().SingleOrDefault(GetDbKeyQueryFunction(entity));
} else {
	dbEntity = DbParentSet.Invoke(dbParent).SingleOrDefault(GetDbKeyQueryFunction(entity));
}

In the code above, DbParentSet.Invoke(dbParent) is returning an EntitySet instance. Interestingly enough, you cannot directly apply an Expression to an EntitySet, as I found documented here. However, you can compile an Expression, and in my scenario, the type of the value returned by Compile just happened to be Func (it all seems so obvious now, hehe). So here’s the final version of the code that works well for both scenarioes:

public abstract Expression<Func<D, bool>> GetDbKeyQueryFunction(E entity);
//...
D dbEntity;
if ( isRoot ) {
	dbEntity = context.GetTable().SingleOrDefault(GetDbKeyQueryFunction(entity));
} else {
	dbEntity = DbParentSet.Invoke(dbParent).SingleOrDefault(GetDbKeyQueryFunction(entity).Compile());
}

So remember, always favor Expressions over Delegates when using LINQ. If you have to have a Delegate, you can always use Compile to turn your Expression into a Delegate as needed. But you can’t turn a Delegate into an Expression (that I’m aware of).