Assigning a predicate variable instead of a lambda expression

The following textbook Join query runs fine:

var list = from family in db.Families.Where(f => !f.isDeleted)
  join member in db.Members.Where(m => !m.isDeleted)
  on family.Id equals member.FamilyID
  select new { family = family.LastName, member = member.DisplayName };

list is a IQueryable: {Microsoft.FrameworkCore.Query.Internal.EntityQueryable<<>f__AnonymousType0<string, string>>} and I can call list.ToList().

However, instead of using a lambda, if I use a predicate in the first Where as follows:

Predicate<Family> query = f => !f.isDeleted;
var list = from family in db.Families.Where(new Func<Family, bool>(query))
  join member in db.Members.Where(m => !m.isDeleted)
  on family.Id equals member.FamilyID
  select new { family = family.LastName, member = member.DisplayName };

then list becomes a [IEnumerable]: {System.Linq.Enumerable.<JoinIterator>d__105<Family, Member, short, <>f__AnonymousType0<string, string>>} and the Results View throws a "There is already an open DataReader associated with this Connection which must be closed first.".

I couldn't reproduce it anymore but I am certain sometimes the Results View throws a "Data is null" exception.

I need to make my filter a variable as the exact filter required depends on some other conditions. Why does using a Predicate assignment cause the type of list to be different, and how do I fix the error?

Solution

You are calling two very different overloads of Where. In the first code snippet, you are calling:

Where<TSource>(IQueryable<TSource>, Expression<Func<TSource,Boolean>>)

This overload translates your lambda expression into a SQL query and runs it on the database.

And in the second code snippet, you are calling

Where<TSource>(IEnumerable<TSource>, Func<TSource,Boolean>)

The second overload is the LINQ to Objects Where, which doesn't know about your database at all. It works with all IEnumerable<T>s.

You should create an Expression<Func<Family, bool>> and pass it in:

Expression<Func<Family, bool>> query = f => !f.isDeleted;
var list = from family in db.Families.Where(query)
    ...