CODEWITHSUNDEEP
asp.net-coreentity-framework-core
David Spence
David Spence

Reputation: 8079

How can I use a method to add a filter on a property in Entity Framework Core?

I need to conditionally add a filter to particular dates in a query. There are common preconditions and the filter will be the same. Therefore I would like the common code to be in a method which can perform these checks and then have the consumer pass in the property which the filter should be applied to (could be applied to multiple).

Here is a simplified version of my code. 

var query = dbContext.Documents.AsQueryable();

query = FilterDocumentsByDate(query, x => x.CreatedDate);
query = FilterDocumentsByDate(query, x => x.SubmittedDate);

private IQueryable<Document> FilterDocumentsByDate(IQueryable<Document> query, Func<Document, DateTime> propertyToSearch)
{
    query = query.Where(x => propertyToSearch(x).Year > 2000);
    return query;
}

When I look at the query in SQL profiler, I can see that the query is missing the WHERE clause (so all documents are being retrieved and the filter is being done in memory). If I copy/paste the code inline for both dates (instead of calling the method twice) then the WHERE clause for the both dates are included in the query.

Is there no way to add a WHERE condition to an IQueryable by passing a property in a Func which can be properly translated to SQL by Entity Framework?

Upvotes: 2

Views: 508

Answers (1)

Jaliya Udagedara
Jaliya Udagedara

Reputation: 1196

EF is unable to understand your query, so it breaks and executes WHERE clause in memory.

The solution is creating dynamic expressions.

var query = dbContext.Documents.AsQueryable();

query = FilterDocumentsByDate(query, x => x.CreatedDate.Year);
query = FilterDocumentsByDate(query, x => x.SubmittedDate.Year);

private IQueryable<Document> FilterDocumentsByDate(IQueryable<Document> query, Expression<Func<Document, int>> expression)
{
    var parameter = expression.Parameters.FirstOrDefault();
    Expression comparisonExpression = Expression.Equal(expression.Body, Expression.Constant(2000));
    Expression<Func<Document, bool>> exp = Expression.Lambda<Func<Document, bool>>(comparisonExpression, parameter);

    query = query.Where(exp);
    return query;
}

I am sorry, I haven't run this myself, but this should create WHERE statement. Let me know how it goes.

Upvotes: 4

Related Questions