How to Extract Where Part of an Include-CodePudding

I'm struggling to understand how expressions work in C# LINQ queries.

I have an entity Assignment which has Persisted and SessionId properties I always use to filter using this expression:

public static Expression<Func<Assignment, bool>> HasCorrectPersisted(int[]? sessions)
{
    return e => e.Persisted && sessions!= null && !sessions.Contains(e.SessionId ?? 0) || !e.Persisted && (sessions== null  ||  e.SessionId != null && sessions.Contains(e.SessionId.Value));
}

(The expression doesn't really matter, only that it's complex.)

So anytime I do something with the Assignments, I'll use dbContext.Assignments.Where(HasCorrectPersisted(sessions)). So far so good.

Now a list of these entities belongs to a entity Parent, and I'm struggling to assign the same filter to these.

I tried:

dbContext.Parents.Include(p => p.Assignments.Where(HasCorrectPersisted(sessions)));

Which leads to this compiler error:

Cannot resolve method 'Where(Expression<Func<Assignment,bool>>)', candidates are: 
IEnumerable<Assignment> Where<Assignment>(this IEnumerable<Assignment>, Func<Assignment,bool>) (in class Enumerable) 
IEnumerable<Assignment> Where<Assignment>(this IEnumerable<Assignment>, Func<Assignment,int,bool>) (in class Enumerable)

This is pretty clear, no Expression should be used, so I replaced it with (note the Compile()):

dbContext.Parents.Include(p => p.Assignments.Where(HasCorrectPersisted(sessions).Compile()));

Which leads to this exception, which kinda makes it sound like Expression was the correct parameter type after all:

ArgumentException : Expression of type 'Func`2[Assignment,Boolean]' cannot be used for parameter of type 'Expression`1[Func`2[Assignment,Boolean]]' of method 'IQueryable`1[Assignment] Where[AssignmentViewEntity](IQueryable`1[Assignment], Expression`1[Func`2[Assignment,Boolean]])' (Parameter 'arg1')

So I started casting and forcing the expression:

dbContext.Parents.Include(p => ((IQueryable<Assignment>)p.Assignments)..Where(HasCorrectPersisted(sessions)));

Which leads to:

The expression 'Convert(d.Assignments, IQueryable`1).Where(e => (False OrElse (Not(e.Persisted) AndAlso True)))' is invalid inside an 'Include' operation, since it does not represent a property access: 't => t.MyProperty'. To target navigations declared on derived types, use casting ('t => ((Derived)t).MyProperty') or the 'as' operator ('t => (t as Derived).MyProperty'). Collection navigation access can be filtered by composing Where, OrderBy(Descending), ThenBy(Descending), Skip or Take operations. For more information on including related data, see http://go.microsoft.com/fwlink/?LinkID=746393.

So this means... that the expression is just to complex for the include?

If I copy the logic from the method HasCorrectPersisted() into the Where() it works, so it's not the complexity per se.

Is there any way to use such a complex expression in the include without copying it?

CodePudding user response：

This was a bit of a curious one, but I was able to get the query to accept the Expression<Func<>>, you need to use AsQueryable with the child collection rather than the casting:

.Include(p => (p.Assignments.AsQueryable().Where(HasCorrectPersisted(...)))

There does seem to be a few issues with your expression. Firstly you appear to be ORing several conditions, but without parenthesis the order of operations would likely be returning results you don't expect:

At a guess I'd suspect it should be something like:

public static Expression<Func<Assignment, bool>> HasCorrectPersisted(int[]? sessions)
{
    return e => (e.Persisted && sessions!= null && !sessions.Contains(e.SessionId ?? 0)) 
        || (!e.Persisted && (sessions == null  ||  e.SessionId != null && sessions.Contains(e.SessionId.Value)));
}

From that I'd probably simplify it so that the simplified expression is passed based on whether the array contained values:

public static Expression<Func<Assignment, bool>> HasCorrectPersisted(int[]? sessions)
{
    if (sessions != null)
        return e => (e.Persisted && !sessions.Contains(e.SessionId ?? 0)) || sessions.Contains(e.SessionId);
    else 
        return e => !e.Persisted;
}

The iffy bit there is the ...Contains(e.SessionId ?? 0) and whether that will translate down to SQL reliably. You may need to handle that more explicitly, but this should hopefully be nudging the solution closer to working.. :)