Using Include with Intersect/Union/Exclude in Linq

Question

What seemed that it should be a relatively straight-forward task has turned into something of a surprisingly complex issue. To the point that I'm starting to think that my methodology perhaps is simply out of scope with the capabilities of Linq.

What I'm trying to do is piece-together a Linq query and then invoke .Include() in order to pull-in values from a number of child entities. For example, let's say I have these entities:

public class Parent
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Location { get; set; }
    public ISet<Child> Children { get; set; }
}

public class Child
{
    public int Id { get; set; }
    public int ParentId { get; set; }
    public Parent Parent { get; set; }
    public string Name { get; set; }
}

And let's say I want to perform a query to retrieve records from Parent, where Name is some value and Location is some other value, and then include Child records, too. But for whatever reason I don't know the query values for Name and Location at the same time, so I have to take two separate queryables and join them, such:

MyDbContext C = new MyDbContext();
var queryOne = C.Parent.Where(p => p.Name == myName);
var queryTwo = C.Parent.Where(p => p.Location == myLocation);
var finalQuery = queryOne.Intersect(queryTwo);

That works fine, producing results exactly as if I had just done:

var query = C.Parent.Where(p => p.Name == myName && p.Location = myLocation);

And similarly, I can:

var finalQuery = queryOne.Union(queryTwo);

To give me results just as if I had:

var query = C.Parent.Where(p => p.Name == myName || p.Location = myLocation);

What I cannot do, however, once the Intersect() or Union() is applied, however, is then go about mapping the Child using Include(), as in:

finalQuery.Include(p => p.Children);

This code will compile, but produces results as follows:

1. In the case of a Union(), a result set will be produced, but no Child entities will be enumerated.
2. In the case of an Intersect(), a run-time error is generated upon attempt to apply Include(), as follows:

Expression of type 'System.Collections.Generic.IEnumerable`1[Microsoft.EntityFrameworkCore.Query.Internal.AnonymousObject]' cannot be used for parameter of type 'System.Collections.Generic.IEnumerable`1[System.Object]' of method 'System.Collections.Generic.IEnumerable`1[System.Object] Intersect[Object](System.Collections.Generic.IEnumerable`1[System.Object], System.Collections.Generic.IEnumerable`1[System.Object])'

The thing that baffles me is that this code will work exactly as expected:

var query = C.Parent.Where(p => p.Name == myName).Where(p => p.Location == myLocation);
query.Include(p => p.Children);

I.e., with the results as desired, including the Child entities enumerated.

Answer 1

my methodology perhaps is simply out of scope with the capabilities of Linq

The problem is not LINQ, but EF Core query translation, and specifically the lack of Intersect / Union / Concat / Except method SQL translation, tracked by #6812 Query: Translate IQueryable.Concat/Union/Intersect/Except/etc. to server.

Shortly, such queries currently use client evaluation, which with combination of how the EF Core handles Include leads to many unexpected runtime exceptions (like your case #2) or wrong behaviors (like Ignored Includes in your case #1).

So while your approach technically perfectly makes sense, according to the EF Core team leader response

Changing this to producing a single SQL query on the server isn't currently a top priority

so this currently is not even planned for 3.0 release, although there are plans to change (rewrite) the whole query translation pipeline, which might allow implementing that as well.

For now, you have no options. You may try processing the query expression trees yourself, but that's a complicated task and you'll probably find why it is not implemented yet :) If you can convert your queries to the equivalent single query with combined Where condition, then applying Include will be fine.

---

P.S. Note that even now your approach technically "works" w/o Include, prefomance wise the way it is evaluated client side makes it absolutely non equivalent of the corresponding single query.

Answer 2

A long time has gone by, but this .Include problem still exists in EF 6. However, there is a workaround: Append every child request with .Include before intersecting/Unionizing.

MyDbContext C = new MyDbContext();
var queryOne = db.Parents.Where(p => p.Name == parent.Name).Include("Children");
var queryTwo = db.Parents.Where(p => p.Location == parent.Location).Include("Children");
var finalQuery = queryOne.Intersect(queryTwo);

As stated by @Ivan Stoev, Intersection/Union is done with after-fetched data, while .Include is ok at request time.

So, as of now, you have this one option available.