Enumerable Intersect and Any Expressions do not compile to SQL

Question

I'm trying to create an extension method for IQueryable which gets a function returning a IEnumerable<Int32>> as an input parameter, which should be checked against another constant list of numbers. If at least one identical entry is contained in both lists it should return true, something like listA.Intersect(listB).Any(). This expression also has to compile well to SQL (latest EF core) (Intersect() and Any() themselves should do). So here is what I came up with (yet):

public static IQueryable<T> AuthorizedRecords<T>(this IQueryable<T> query, Expression<Func<T, IEnumerable<Int32>>> employeeIds)
{
    var ids = (new List<int> { 1, 2, 3 }).AsEnumerable(); // <- replaced later
    
    var methodAny = typeof(Enumerable)
        .GetMethods()
        .Where(m => m.Name == "Any" && m.GetParameters().Length == 1)
        .First()
        .MakeGenericMethod(typeof(int));

    var methodIntersect = typeof(Enumerable)
         .GetMethods()
         .Where(m => m.Name == "Intersect" && m.GetParameters().Length == 2)
         .First()
         .MakeGenericMethod(typeof(int));

    var lambdaParam = employeeIds.Parameters.Single();
    var lambda = Expression.Lambda(
        Expression.Call(
            methodAny,
            Expression.Call(
                methodIntersect,
                Expression.Constant(ids), 
                employeeIds.Body
            )
        ),
        lambdaParam
     );
     
     var predicate = (Expression<Func<T, bool>>)lambda;
     return query.Where(predicate);
}       

This code compiles but I get a runtime error that The LINQ expression <...very long expression...> cannot be translated to sql. What's wrong with the expression?

---

UPDATE

The expression seems to be fine, instead the Intersect() method fails for some reason. I reduced the code to this minimal Linq query reproducing the error:

dbContext.Meetings
  .Where(e => e.AccessList
    .Select(x => x.Id)
    .Intersect((new List<int>() { 90, 91, 92 })
    .Any()
  );

Now I get a System.ArgumentNullException: Value cannot be null. bei System.Linq.Expressions.Expression.Lambda(Expression body, String name, Boolean tailCall, IEnumerable`1 parameters)

What could be the problem with the Intersect query?

Answer 1

Long story short: I did not find any solution to get Intersect() to work. Instead I changed my Linq expression to get rid of Intersect. I will post the solution for my use case, maybe it is useful for someone later.

The idea is to use

Where(ListA.Any(x => ListB.Contains(x))
// instead of 
// Where(ListA.Intersect(ListB).Any()

Here it is:

public static IQueryable<T> AuthorizedRecords<T>(this IQueryable<T> query, Expression<Func<T, IQueryable<int>>> employeeIds)
{
  var ids = new List<int> { 1, 2, 3 }); // <- changed later

  var methodAny = typeof(Queryable)
        .GetMethods()
        .First(m => m.Name == "Any" && m.GetParameters().Length == 2)
        .MakeGenericMethod(typeof(int));

  Expression<Func<int, bool>> contains = x => ids.Contains(x);
  
  var lambda = Expression.Lambda<Func<T, bool>>(
    Expression.Call(
      methodAny,
      employeeIds.Body,
      contains
    ), 
    employeeIds.Parameters
  );
  
  return query.Where(lambda);
}