Ok, that's a really long title for a post, so what the heck do I mean by all that? Well, I've started working with the CompiledQuery class and I've run into a language limitation problem that almost makes any kind of performance gain I might get from CompiledQuery not worthwhile when using projections due to the fact that they cannot be anonymous so you're forced to define a new class yourself to represent the projection.
First off, if you're not familiar with CompiledQuery, it's basically an optimization provided by the ADO.NET Entity Framework that enables it to take your LINQ expression once, generate a cached execution plan (not in the SQL sense, but in the entity sense) for it and return you a Func delegate on the fly that takes parameters to allow you to plug in any dynamic values you might need for the query (e.g. for a where clause).
Secondly, if you're not familiar with the C# "mumble" type problem, jump on over to this excellent post from Ian Griffiths where he details the issues quite well and even talks about the language changes that the C# team was thinking about making to support the concept.
Go ahead... I'll wait. Done yet? K. :) So here's why CompiledQuery suffers from this problem annoying problem. The whole point of CompiledQuery is that you can call Compile once for the LINQ expression and store the resulting Func delegate instance so you can reuse it over and over. So ideally you'd want to store this Func delegate in a static variable or, at bare minimum, a member variable right? Here's how you could create a compiled query that allows you to find all orders for a customer in a specific status. Note that for my examples, I'm assuming we've created an EF model over the AdventureWorksLT DB.
public static Func<AdventureWorksLTEntities, DateTime, IQueryable<SalesOrderHeader>> GetCustomersOrdersByStatus =
CompiledQuery.Compile((AdventureWorksLTEntities entities, int customerId, int status) =>
from order in entities.SalesOrderHeader
where order.CustomerID == customerId
&&
order.Status == status
select order);
I can then call this function and consume it's results like so:
using(AdventureWorksLTEntities entities = new AdventureWorksLTEntities())
{
foreach(var order in MyCompiledQueries.GetCustomersOrdersByStatus(entities, someCustomerId, someStatusValue))
{
/* ... do something with the order ... */
}
}
Now, the above example actually works out just fine because we're selecting the entire SalesOrderHeader entity type so I know the type parameter for my IQueryable<T> is going to be SalesOrderHeader. The problem comes in when you want to do a projection of fields, either from multiple entities or just reducing the number of fields you bring back from a single entity because that results in an anonymous type being generated. For example, assume I want to bring back some data from the customer and just some data from the order for presentation in a list view:
public static Func<AdventureWorksLTEntities, int, int, IQueryable<???>> GetCustomersOrdersByStatus =
CompiledQuery.Compile((AdventureWorksLTEntities entities, int customerId, int status) =>
from order in entities.SalesOrderHeader
join customer in entities.Customer on order.CustomerId = customer.CustomerId
where customer.CustomerID == customerId
&&
order.Status == status
select new
{
CustomerId = customer.CustomerID,
CustomerFirstName = customer.FirstName,
CustomerLastName = customer.LastName,
CustomerCompanyName = customer.CompanyName,
OrderSalesOrderId = order.SalesOrderId,
OrderDate = order.OrderDate,
OrderTotalDue = order.TotalDue
});
So, as you can see, I'm now trying to project an anonymous type that is composed of values from two separate entity types. The problem is not the LINQ query because it works just fine by itself. The problem is that I can't determine the type parameter type for the IQueryable<T> for the result parameter of my Func variable because that type is anonymous. This is where having the ability to use a "mumble" type and say IQueryable<var> would help out tremendously because the compiler does know the anonymous type.
The only solution to this problem today AFAICT is that you actually need to declare a class to represent the projection and use it to type the Func signature and select it instead of an anonymous type in the LINQ query:
public sealed class CustomerOrder
{
public int CustomerId
{
get;
set;
}
/* ... declare other properties here ... */
}
public static Func<AdventureWorksLTEntities, int, int, IQueryable<CustomerOrder>> GetCustomersOrdersByStatus =
CompiledQuery.Compile((AdventureWorksLTEntities entities, int customerId, int status) =>
from order in entities.SalesOrderHeader
join customer in entities.Customer on order.CustomerId = customer.CustomerId
where order.CustomerID == customerId
&&
order.OrderDate == orderDate
select new CustomerOrder
{
CustomerId = customer.CustomerID,
CustomerFirstName = customer.FirstName,
CustomerLastName = customer.LastName,
CustomerCompanyName = customer.CompanyName,
OrderSalesOrderId = order.SalesOrderId,
OrderDate = order.OrderDate,
OrderTotalDue = order.TotalDue
});
And so depending on how large your projection is this forces you to do a hell of a lot of code generation which as of today is very manual. It would be great to have a tool that could generate a strong type based on the anonymous type syntax. Maybe there's one out there today that I don't know about?
Anyway, it's still all stuff the compiler should be smart enough to do for you. The annoying thing is it already does this work, but it's limited to local scope only. Perhaps another solution would be that, instead of using the var keyword to "mumble", they actually gave us a way to give the anonymous type a name inline which would basically name the anonymous type instead of the compiler just generating a random type name.