How to pass func expression in LINQ where clause?

Question

This is my Custom filter(Func) to pass in where clause

Func<Project,bool> filter = f =>
{
    bool filteredContent = true;
    if (!CreatorId.Equals(0))
        filteredContent = f.CreatedBy.Equals(CreatorId);

    if (filteredContent && !VerticalMarketId.Equals(0))
        filteredContent = f.VerticalMarketsId.Equals(VerticalMarketId);

    if (filteredContent && !ProductCategoryId.Equals(0))
        filteredContent = f.ProductCategoriesId.Equals(ProductCategoryId);

    return filteredContent;

};

This is my code where I get all the projects based on the conditions created in filter expression

 getProjects = await _context.Projects.Where(x => x.IsDeleted == false && filter.Invoke(x))// Here I'm getting the exception
                .Include(PC => PC.ProjectComments.Where(x => x.IsDeleted == false))
                .Include(SP => SP.SharedProjects)
                .AsNoTracking().ToListAsync();

Exception:The LINQ expression (DbSet......) 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 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'.

Can someone tell how can I filter the data using expression in this?

NOTE: I can do ToListAsync() before applying the filter, but it'll get all the records from DB then filter on client side. But I want to filter the data on server side.

Chidester

You will need an Expression<Func<Project, bool>> before EF can translate it – Rimrock

Can you please elaborate? @HansKesting – Chidester

Answer 1

I figured it out by creating a simple Expression as fololows:

private static Expression<Func<Project, bool>> ProjectFilterExpression(
    int creatorId, 
    int verticalMarketId, 
    int productCategoryId)
{
    Expression<Func<Project, bool>> projectFilterExpression = pfe => 
    !pfe.IsDeleted
    //CreatorId Filter
    && (creatorId.Equals(0) || pfe.CreatedBy.Equals(creatorId))
    //Vertical Market Filter
    && (verticalMarketId.Equals(0) || pfe.VerticalMarketsId.Equals(verticalMarketId))
    // Product Category Filter
    && (productCategoryId.Equals(0) || pfe.ProductCategoriesId.Equals(productCategoryId)); 
    return projectFilterExpression;
}

Then I call this static method inside my filter method.

var filter = ProjectFilterExpression(CreatorId, VerticalMarketId, ProductCategoryId);

And finally I applied this filter in my LINQ where clause

getProjects = await _context.Projects.Where(filter).AsNoTracking().ToListAsync();

It's working totally fine.

Chidester

Instead of trying to build a single Expression<>, since your criteria is all &&s, I would instead conditionally call .Where to add each filter. eg; query = _context.Projects.Where(x => !x.IsDeleted); if (cond) query = query.Where(f => [filter]); – Duumvir

@JeremyLakeman Your suggestion will work for sure. But in your suggestion it will fetch the data from DB based on 1st condition. Then it will apply other conditions on the C# object containing the data. My query was.. I want to send an expression based on which data will be filtered in DB only. So I don't have to get all the data from db then filter it in the C#. It'll take more time. – Chidester

No. You should think of an IQueryable as a query builder. Until you enumerate the results, via .ToList() .Sum(...) etc, nothing happens. – Duumvir

Answer 2

IF you were using Linq To Objects that should work but you are doing Linq To SQL and in this case you must think on how you would translate this function into a valid SQL statement. Question yourself: How could I pass this function call in a SQL Statement? Depending what you do on the body of your expression, you cannot translate it to SQL, you must be simpler sometimes.

Candidate solution

1. Add PredicateBuilder class on your project. It will give you easily logical operators to you handle expressions.

   http://www.albahari.com/nutshell/predicatebuilder.aspx

2. Try to define an expression and pass it as argument on Where method of your query method chain. For sample (read the comments):

// define a expression with default condition
Expression<Func<Project, bool>> filter = f => !f.IsDeleted;

// check conditions to add new filtes with `And` logical operator
if (!CreatorId.Equals(0))
    filter = filter.And(f => f.CreatedBy.Equals(CreatorId));
else if (!VerticalMarketId.Equals(0))
    filter =  filter.And(f => f.VerticalMarketsId.Equals(VerticalMarketId));
else if (!ProductCategoryId.Equals(0))
    filter =  filter.And(f => f.ProductCategoriesId.Equals(ProductCategoryId));

// apply the filter on the query and execute it
getProjects = await _context.Projects.Where(filter)
    .Include(PC => PC.ProjectComments.Where(x => !x.IsDeleted))
    .Include(SP => SP.SharedProjects)
    .AsNoTracking()
    .ToListAsync();

Note: I didn't test this code and it probably should be fixed somehow!

Important tips on Linq To SQL:

• Logical operators are ok and tend to be translated fine to sql;
• Where(x => x.Children.Any(j => j.Children.Any())), each Any call generates a subquery on query scope, be careful with it given it can compromise your database performance.
• If you just need to check the existence of an item, use queryable.Any(expression).
• If you need to check and then do something, prefer using queryable.FirstOrDefault(expression) and check if the result is null before using.
• Use paging with .Take(int) and .Skip(int).
• Always concrete your queries by calling .ToList(), .ToArray() or async versions of these methods. Avoid passing queryable in the top layers (query can be executed out of the scope you want).

Imaginal

It's not working. .And method asking for one more parameter, it take two argument Left expression right expression. What is it? – Chidester