How to store Linq where condition in property

Question

I'd like to know the most maintainable way (if exists) to store an entity condition in a parameter so to reuse it in every linq where conditions.

Suppose to have a Product entity:

public class Product
{
    public bool IsDiscontinued { get; set; }

    public int UnitsInStock { get; set; }
}

I'd like to add to the Product class a property IsOnSale that contains the logic to determine whether the product is on sale or not. In this simple example the logic could be:

IsOnSale = IsDiscontinued == false && UnitsInStock > 0

Then I should be able to write a linq query of this type:

from p in context.Products
where p.IsOnSale == true
select p

The purpose of the solution should be that, if in the future the logic to determine whether the product is on sale or not changes (e.g. adding a property IsBackOrderAllowed), I don't have to edit the linq queries everywhere but simply have to change the IsOnSale property.

A similar question has been posted here but seems to address a more specific problem.

Doctor Jones · Accepted Answer · 2011-03-16T11:37:50.757

6

You could make a method that returns an IQueryable filtered by your conditions:

public IQueryable<Product> WhereOnSale(this IQueryable<Product> source)
{
    return source.Where(p => p.IsDiscontinued == false && p.UnitsInStock > 0);
}

and you would use it like this:

from p in context.Products.WhereOnSale()
select p

If you want to do it with an expression like in Yakimych's answer then this would work:

Expression<Func<Product, bool>> isOnSale = 
(p => p.IsDiscontinued == false && p.UnitsInStock > 0);

and you could use it like so:

context.Products.Where(isOnSale)

It is important that it is declared as an expression, otherwise Entity Framework won't be able to translate it into SQL because the lambda will be compiled into IL instead of an Expression Tree.

edited Mar 16 '11 at 11:37

answered Mar 16 '11 at 10:39

Doctor Jones

21,196
13
77
99

I agree, I prefer using methods as oppose to Expressions. You can achieve more with Expressions but they tend to obscure the meaning of the code. – Doctor Jones Mar 16 '11 at 11:33
1

The Expressions solution seems more versatile (when composing more complex queries). But I'll make some more test with extension methods solution, maybe I'll understand why it's better... – EdoT Mar 16 '11 at 11:45
Yes they are, you should probably take a look at [PredicateBuilder](http://www.albahari.com/nutshell/predicatebuilder.aspx) it is incredibly useful for manipulating Expressions. – Doctor Jones Mar 16 '11 at 11:48
I wouldn't say it's better, both solutions have their own virtues. The method solution is simpler and easier to maintain, Expressions are more flexible but also more complicated. – Doctor Jones Mar 16 '11 at 11:50

Yakimych · Answer 2 · 2011-03-16T11:01:23.290

2

You can do something like this:

Func<Product, bool> isOnSaleFunc = 
                          (p => p.IsDiscontinued == false && p.UnitsInStock > 0);

Then in your query you do:

context.Products.Where(isOnSaleFunc)

Update

As a result of the comment-discussion with @DoctaJonez - the filtering with such an approach will be performed on the client-side (which is or course inefficient), thus Expression<Func<Product, bool>> should be used instead of Func<Product,bool>.

edited Mar 16 '11 at 11:01

answered Mar 16 '11 at 10:36

Yakimych

17,612
7
52
69

2

This would fail at query time because it cannot translate the Func to a SQL expression, if you change the declaration from Func to Expression> it will work. – Doctor Jones Mar 16 '11 at 10:41
@DoctaJonez - No, it works perfectly well the way it is, just tested it. Apparently EF is smart enough not to try to translate the `isOnSaleFunc`, but to analyze beforehand and then start building the SQL expression. – Yakimych Mar 16 '11 at 10:49
It won't do it at query time though, there's no way that it could. Run it LINQPad and check the resulting SQL. It won't include your where clause if you declare it as a Func, because EF cannot parse a Func, it has to be an Expression to work. – Doctor Jones Mar 16 '11 at 10:51
@DoctaJonez - Well, I just ran it from VisualStudio and the query returns real results based on the `Where` condition. – Yakimych Mar 16 '11 at 10:52
For further information please see [Why would you use Expression> rather than Func?](http://stackoverflow.com/questions/793571/why-would-you-use-expressionfunct-rather-than-funct) – Doctor Jones Mar 16 '11 at 10:53
Yes you will get the results, but the filtering will be performed on the client side rather than the server side. This could be a massive performance hit if the source table is large. – Doctor Jones Mar 16 '11 at 10:54
@DoctaJonez - Ok, that's a valid point, and you should have probably mentioned it initially, since it doesn't actually `fail`. What happens is more dangerous, since one might get a performance bottleneck without realizing why. Thanks for your clarification! – Yakimych Mar 16 '11 at 10:59
No problem, it used to fail when using EF 1.0, I didn't realise that they changed the behavior in the new version (which is more dangerous IMO). – Doctor Jones Mar 16 '11 at 11:01

Ladislav Mrnka · Answer 3 · 2011-03-16T11:03:19.490

First problem here is that linq to entities cannot work with properties which are not part of the model (= it can't work with custom properties).

You must define expression. If you define only Func it will be executed as Linq to objects:

public class Product
{
    ...

    public static Expression<Func<Product, bool>> IsOnSale
    {
        get
        {
            return p => (p.IsDiscontinued == false && p.UnitsInStock > 0);
        }
    }
}

Now you must call the query this way:

var query = context.Products.Where(Product.IsOnSale);

Another approach is using model defined function.

smartcaveman · Answer 4 · 2011-03-16T10:45:36.807

I think you are looking for the Specification Pattern.

An article on using it with EF that includes a base implementation is available at http://huyrua.wordpress.com/2010/07/13/entity-framework-4-poco-repository-and-specification-pattern/ .

An example of how to implement this would be to make your query

from p in context.Products
where ProductSpecifications.IsOnSale.Predicate
select p

and to use the following helper and specification definition.

public static class ProductSpecifications{
     public static readonly Specification<Product> IsOnSale = new Specification<Product>(x => !x.IsDiscontinued && x.UnitsInStock > 0);  
}

public class Specification<TEntity> : ISpecification<TEntity>
{
    public Specification(Expression<Func<TEntity, bool>> predicate)
    {
        _predicate = predicate;
    }

    public bool IsSatisfiedBy(TEntity entity)
    {
        return _predicate.Compile().Invoke(entity);
    }

    private Expression<Func<TEntity, bool>> _predicate;

    public Expression<Func<TEntity,bool>> Predicate{ 
         get{ return _predicate; }
    }
}

You can do a lot more with this pattern too, so I suggest looking into it!

Seems very promising, I'll surely take a deeper look. Maybe it's a lot complex for simple "specifications". — EdoT, Mar 16 '11 at 12:00
It's not that complex, but you can just do `Expression> IsOnSale = product => !product.IsDiscontinued && product.UnitsInStock > 0` — smartcaveman, Mar 16 '11 at 12:06

How to store Linq where condition in property

4 Answers4