How can I compose an Entity Framework query from smaller, resusable queries?

Question

I have a few (fairly redundant) queries in my app that are 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 is a simplified example, of course. However, the gist is that I need to capture a date and filter a subquery on it. I also need to filter the subquery differently based on the parent object.

I tried (essentially) creating 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 frameworks complains about the FindSeverity function being unknown.

I've tried a few different methods of Expression gymnastics, but to no avail.

What do I need to do to compose reusable Entity Framework queries?

Answer 1

I've played around a bit with your problem but without a final satisfying result. I will only list the few points I could find and understand.

1)

I rewrite your last code snippet (in simplified form without the projection to an anonymous type)...

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

...and then in extension method syntax:

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

Now we see better that FindSeverity(u => u.Building.Country == c).Max() is the body of an Expression<Func<Country, T>> (T is int in this case). (I'm not sure if "body" is the correct terminus technicus, but you know what I mean: the part right from the Lambda arrow =>). When the whole query is translated into an expression tree this body is translated as a method call to the function FindSeverity. (You can see this in the debugger when you watch the Expression property of query: FindSeverity is directly a node in the expression tree, and not the body of this method.) This fails on execution because LINQ to Entities doesn't know this method. In the body of such a lambda expression you can only use known functions, for instance the canonical functions from the static System.Data.Objects.EntityFunctions class.

2)

A possible general way to build reusable parts of a query is to write custom extension methods of IQueryable<T>, 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 like:

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 extension method syntax. I don't see a way to use such custom query extension methods in query syntax.

The example above is only a general pattern to create reusable query fragments but it doesn't really help for the specific queries in your question. At least I don't know how to reformulate your FindSeverity method so that it fits into this pattern.

3)

I believe that your original queries cannot work in LINQ to 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 under the category "Referencing a non-scalar variable" inside of a query which is not supported in LINQ to Entities. (In LINQ to Objects it works.) The non-scalar variable in the query above is Users. If the Building table is not empty an exception is expected: "Unable to create a constant value of type EntityType. Only primitive types ('such as Int32, String, and Guid') are supported in this context."

It looks that you have a one-to-many relationship between User and Building in the database but this association isn't completely modelled in your Entities: User has a navigation property Building but Building doesn't have a collection of Users. In this case I would expect a Join in the query, something like:

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 wouldn't create the mentioned exception of referencing a non-scalar variable. But perhaps I misunderstood your model.