Is it possible to put parts of Linq Queries in functions?-CodePudding

Basically, I have a Database query with a lot of stuff in a WHERE clause, which I want to create in a function. I just cant get it to work. Very simple example:

// _context is the DatabaseContext
var tmp = _context.Person.Where(p => p.Surname.Contains("Ai"));
tmp = tmp.Where(p => SomeFunction(p));
var res = tmp.ToList();

and the function looks like this:

private bool SomeFunction(Person p)
{
    return p.IsFemale;
}

And this throws an exception:

The LINQ expression 'DbSet<Person>
    .Where(p => p.Surname.Contains("Ai"))
    .Where(p => MyController.SomeFunction(p))' could not be translated. Either rewrite 
the query in a form that can be translated, or switch to client evaluation explicitly by 
inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or 
ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

Yes, it works when I call 'tmp.ToList()' first but then it will be filtered in-memory, what I want is to extend the query on the database.

Is this possible? How can it be done, if so?

CodePudding user response：

The EF works with Expression trees to create queries and it can't translate method call to some SQL which will be executed in DB (not in memory).

You can rewrite your method to this:

private IQueryable<Person> WhereFemale(IQueryable<Person> query)
{
    return query.Where(p => p.IsFemale);
}

and then:

var tmp = _context.Person.Where(p => p.Surname.Contains("Ai"));
tmp = WhereFemale(tmp);
var res = tmp.ToList();

Another approach - you can create extension method:

public static IQueryable<Person> WhereFemale(this IQueryable<Person> query)
{
    return query.Where(p => p.IsFemale);
}

and now you can use method chaining:

var res = _context.Person
    .Where(p => p.Surname.Contains("Ai"))
    .WhereFemale(tmp)
    .ToList();

CodePudding user response：

You could build an expression and pass that:

public static Expression<Func<Person, bool>> SomeCondition()
{
    return BuildFilter<Person>(p => p.IsFemale);
}

private static Expression<Func<T, bool>> BuildFilter<T>(Expression<Func<T, bool>> expression)
{
    return expression;
}

The BuildFilter method will transform your lambda into an expression tree. You could then use it like this:

.Where(SomeCondition());

There is a more complicated way to build the expression if you wish to go that way:

public Expression<Func<Person, bool>> SomeCondition()
{
    var parameterExpression = Expression.Parameter(typeof(Person));
    var accessExpression = Expression.Property(parameterExpression, nameof(Person.IsFemale));
    var equalityExpression = Expression.Equal(accessExpression, Expression.Constant(true));
    return Expression.Lambda<Func<Person, bool>>(equalityExpression, parameterExpression);
}

But I think in this case, the first approach is much easier to read and understand.