I have undertaken recently to bring a project coded with sql queries and stored procs in Linq and tried to eliminate all stored procs. The code need to be very efficient because the amount of data to be processed is huge. I came across the linq queries and found that we can compiled them in order that only the parameters can change and the query and the reading delegate are reused over and over. This is the best approach in my case because queries are not dynamic and it is very near as efficient as direct sql. What I need is create a query, and compile it using
System.Data.Linq.CompiledQuery.Compile…
But wait, this is not that simple. There is a problem. You can’t use a compiled query with an anonymous type! How would you store the resulting delegate func. You can’t reuse the anonymous type outside of the scope of the method.
Really?
No, we can do something about it. Here is how I resolve the issue:
- Create a query holder. I have create a struct that will hold the func delegate but as an object.
public struct CompiledQuery
{
private object query;
}
- Now, I have created a bunch of methods on that struct that will execute the query. This is the best because it manage automatically the lifetime of the query.
public TResult Execute<TDbContext, TResult>(
TDbContext context,
Expression<Func<TDbContext, TResult>> query) where TDbContext : DataContext
{
if (context == null)
throw new ArgumentNullException("context");
if (query == null)
throw new ArgumentNullException("query");
Func<TDbContext, TResult> func;
if (this.query == null)
{
func = System.Data.Linq.CompiledQuery.Compile<TDbContext, TResult>(query);
this.query = func;
}
else
func = (Func<TDbContext, TResult>)this.query;
return func(context);
}
This works because we are using the compiler type inference to help us finding the type of the delegate and casting is as a valid as func.
After that, I can use the code to query the data directly. Really simple isn’t it?
public class MyClass
{
private CompiledQuery myQuery;
public void MyMethod()
{
using (var context = new DbDataContext())
{
var items = this.myQuery.Execute(context,
c =>
from e in c.Employees
select new
{
e.FirstName,
e.LastName,
e.EmployeeNumber
});
foreach (var item in items)
{
Console.WriteLine("FirstName = {0}, LastName = {1}, Number = {2}", item.FirstName, item.LastName, item.EmployeeNumber);
}
}
}
}
Happy Linq programming! Have a nice week!
Dany
