Create an Expression Tree that generates a parametric query for Entity Framework

Question

I'm trying to create a generic class to be used to compose queries for Entity Framework (5).

I got it to work, the only problem is that the value is injected as a constant of the query instead of as a parameter. This reduces the possibilities for EF to cache the query and reuse it later on.

This is what I got so far.

public class MinDateFilter<T> : IFilter<T> where T : class
{
    private readonly Expression<Func<T, bool>> _predicate;

    public MinDateCandidateFilter(Expression<Func<T, DateTime>> propertySelector, DateTime from)
    {
        from = from.Date.AddDays(-1);
        from = new DateTime(from.Year, from.Month, from.Day, 23, 59, 59, 999);

        Expression value = Expression.Constant(from, typeof(DateTime));
        //ParameterExpression variable = Expression.Variable(typeof(DateTime), "value");

        MemberExpression memberExpression = (MemberExpression)propertySelector.Body;
        ParameterExpression parameter = Expression.Parameter(typeof(T), "item");
        Expression exp = Expression.MakeMemberAccess(parameter, memberExpression.Member);

        Expression operation = Expression.GreaterThan(exp, value);
        //Expression operation = Expression.GreaterThan(exp, variable);
        _predicate = Expression.Lambda<Func<T, bool>>(operation, parameter);
    }

    public IQueryable<T> Filter(IQueryable<T> items)
    {
        return items.Where(_predicate);
    }
}

this class can be used in two ways:

by sub-classing it:

public class MinCreationDateCandidateFilter : MinDateFilter<Candidate>
{
    public MinCreationDateCandidateFilter(DateTime @from) : base(c => c.CreationDate, @from) {}
}

or simply by instantiating it:

var filter = new MinDateFilter<Entities.Transition>(t => t.Date, from.Value);

This is what I managed to achieve so far:

SELECT 
[Extent1].[Id] AS [Id]
-- Other fields
FROM [dbo].[Candidates] AS [Extent1]
WHERE [Extent1].[CreationDate] > convert(datetime2, '1982-12-09 23:59:59.9990000', 121)

instead of

SELECT 
[Extent1].[Id] AS [Id]
-- Other fields
FROM [dbo].[Candidates] AS [Extent1]
WHERE [Extent1].[CreationDate] > @p__linq__0

If I uncomment the two commented lines and I comment the two above, I get an error saying that the parameter "value" isn't bound.

I hope I gave all the useful details :)

Could you explain why isn't the current result good enough for your? — svick
– svick, Commented Jul 10, 2013 at 11:45
Because both EF and SQL can't use the first query to properly cache their output. Of course EF will cache the compiled query but that cached item won't be usable unless the input is exactly the same. Same happens to SQL Server that will cache the query plan specific for this value. — Kralizek
– Kralizek, Commented Jul 10, 2013 at 12:06
I'm having the same problem. It seems you can't pass Expression.Constant(myValue). Using Expression Tree Visualizer you may find that the value is passed as MemberExpression when inspecting an actual working query, not ConstantExpression, but I still couldn't get to take a external variable and pass it with Expression.Property or alike. — Pedro Fialho
– Pedro Fialho, Commented Feb 3, 2014 at 14:12

Pedro Fialho · Accepted Answer · 2024-07-01 14:12:02Z

When a parameter is passed as a ConstantExpression, like this:

Expression.Constant(myString)

... it will produce a fixed, constant symbol on the resulting query:

SELECT 
[Extent1].[Id] AS [Id], 
[Extent1].[Bar] AS [Bar], 
FROM [dbo].[Foo] AS [Extent1]
WHERE [Extent1].[Bar] = "Some text"

If we use a tool like Expression Tree Visualizer to analyze an expression like (f => f.Bar == myString)), we'll see that the parameter is actually a MemberExpression. So, in order to have a parameter instead of a string constant in the resulting query we have to pass something like a property of an object, or the more convenient anonymous type:

Expression.Property(
    Expression.Constant(new { Value = myString }),
    "Value"
)

This way we're passing a property of the just-created object and the expression tree gets a MemberExpression that is reusable, resulting in a CommandText optimized for caching:

SELECT 
[Extent1].[Id] AS [Id], 
[Extent1].[Bar] AS [Bar], 
FROM [dbo].[Foo] AS [Extent1]
WHERE [Extent1].[Bar] = @p__linq__0

Collectives™ on Stack Overflow

Create an Expression Tree that generates a parametric query for Entity Framework

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related