How can I compose an Entity Framework request from smaller, renewable requests?

Question

I have several (rather redundant) queries in my application that look something like this:

var last30Days = DateTime.Today.AddDays(-30); from b in Building let issueSeverity = (from u in Users where u.Building == b from i in u.Issues where i.Date > last30Days select i.Severity).Max() select new { Building = b, IssueSeverity = issueSeverity } 

and

 var last30Days = DateTime.Today.AddDays(-30); from c in Countries let issueSeverity = (from u in Users where u.Building.Country == c from i in u.Issues where i.Date > last30Days select i.Severity).Max() select new { Country = c, IssueSeverity = issueSeverity } 

This, of course, is a simplified example. However, the bottom line is that I need to fix the date and filter the subquery on it. I also need to filter the subquery differently based on the parent.

I tried (essentially) to create the following function:

 public IQueryable<int?> FindSeverity(Expression<Func<User, bool>> predicate) { var last30Days = DateTime.Today.AddDays(-30); return from u in Users.Where(predicate) from i in u.Issues where i.Date > last30Days select i.Severity; } 

Using it as follows:

 from c in Countries let issueSeverity = FindSeverity(u => u.Building.Country == c).Max() select new { Country = c, IssueSeverity = issueSeverity } 

This compiles, but does not work at runtime. Entity structures complain about an unknown FindSeverity function.

I tried several different Expression gymnastics methods, but to no avail.

What do I need to do to create multiple Entity Framework queries?

Answer 1

I worked a bit with your problem, but without a final satisfactory result. I just listed a few points that I could find and understand.

1)

I am rewriting your last piece of code (in a simplified form without projecting onto an anonymous type) ...

 var query = from c in Countries select FindSeverity(u => u.Building.Country == c).Max(); 

... and then in the syntax of the extension method:

 var query = Countries .Select(c => FindSeverity(u => u.Building.Country == c).Max()); 

Now we see that FindSeverity(u => u.Building.Country == c).Max() is the body of Expression<Func<Country, T>> ( T is int in this case). (I'm not sure that the "body" is the correct termicus technicus, but you know what I mean: the part to the right of the arrow lambda =>). When the entire query is translated into the expression tree, this body is transformed as a call to the FindSeverity function FindSeverity . (This can be seen in the debugger when you look at the Expression query property: FindSeverity is directly a node in the expression tree, not the body of this method.) This is not executed at execution, since LINQ The entities of this method do not know. In the body of such a lambda expression, you can use only known functions, for example, canonical functions from the static class System.Data.Objects.EntityFunctions .

2)

A possible general way to create reusable query parts is to create your own IQueryable<T> extension methods, for example:

 public static class MyExtensions { public static IQueryable<int?> FindSeverity(this IQueryable<User> query, Expression<Func<User, bool>> predicate) { var last30Days = DateTime.Today.AddDays(-30); return from u in query.Where(predicate) from i in u.Issues where i.Date > last30Days select i.Severity; } } 

Then you can write queries, for example:

 var max1 = Users.FindSeverity(u => u.Building.ID == 1).Max(); var max2 = Users.FindSeverity(u => u.Building.Country == "Wonderland").Max(); 

As you can see, you are forced to write your queries in the syntax of the extension method. I see no way to use such custom query extension methods in the query syntax.

The above example is just a general template for creating reusable query fragments, but it does not help for specific queries in your question. At least I don’t know how to reformulate your FindSeverity method FindSeverity that it fits into this template.

3)

I believe your original queries cannot work in LINQ for Entities. A query like this ...

 from b in Building let issueSeverity = (from u in Users where u.Building == b from i in u.Issues where i.Date > last30Days select i.Severity).Max() select new { Building = b, IssueSeverity = issueSeverity } 

... falls into the category "Reference to a non-scalar variable" within a query that is not supported in LINQ to Entities. (It works in LINQ to Objects.) The non-scalar variable in the query above Users . If the Building table is not empty, an exception is expected: "It is not possible to create a constant value of type EntityType. Only primitive types (such as Int32, String, and Guid) are supported in this context."

It looks like you have a one-to-many relationship between User and Building in the database, but this association is not fully modeled in your entities: User has the Building navigation property, but Building does not have the Users set. In this case, I would expect Join in the request, for example:

 from b in Building join u in Users on u.Building.ID equals b.ID let issueSeverity = (i in u.Issues where i.Date > last30Days select i.Severity).Max() select new { Building = b, IssueSeverity = issueSeverity } 

This would not throw the mentioned exception to refer to a non-scalar variable. But maybe I misunderstood your model.