Using a LINQ compiled query to determine if a record exists with the Entity Framework

I am writing an application that requires high performance on the server layer. The method I am calling will be invoked a large number of times, so I don’t want the overhead of the LINQ compile every time I invoke it.

Yes, the compile is very expensive. It is especially so when called a large number of times in a small timeframe. So, I decided to write a compiled query. It is really quite scary looking at first, but don’t worry. It does get easier once its dissected.

Let’s start by looking at my compiled query.
//precompiling my query to reduce the overhead. grab one field from db, and only first. Should be fastest.
private static readonly Func<MyDataContext,Guid,bool> _CompiledRecordExists =
(context,myTableID) => ((from record in context.MyTable
where record.myTableId == myTableID
select record.MyTableId
).FirstOrDefault() != null)

So I have  a compiled query called _CompiledRecordExists. This query is compiled on when the class static are all instantiated.  We are creating a query which takes in a data context from the Entity Framework, a Guid for the records primary key, and a return value of a bool.

You will notice the Func template/generic which allows us to do this. The last parameter, you can have up to 6 parameters, is the return value of the function.

My linq query is then created in my lambda function. You can see I use the variables context and myTableID. You can name these whatever you want, the order is what is important. The first one is the first type you used in the Func declaration. The second one is the second, so on and so on. Notice there is no bool. That is because its our return value.

In order to actually use our compiled function in code, we need to simply call it. It is really simple now.

using (var context = factory.CreateContext())
                return _CompiledRecordExists (context, recordID);

Notice that we can call it just like a function. Now you won’t have any of the overhead of the sql query generation. The hit was taken up front.

Try running a tight loop on your linq queries and the compiled queries and let me know how it works for you.

Happy Coding!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.