CODEWITHSUNDEEP
c#linqentity-framework-corewhere-clauseiqueryable
Okasha Momin
Okasha Momin

Reputation: 231

How to pass func expression in LINQ where clause?

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.

Upvotes: 5

Views: 5820

Answers (2)

Okasha Momin
Okasha Momin

Reputation: 231

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.

Upvotes: 4

Felipe Oriani
Felipe Oriani

Reputation: 38608

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).

Upvotes: 4

Related Questions