I use the **PredicateBuilder **class to filter the data returned by the database.

Below an example where the data (orders) are filtered by date (dispatch date).

  public IList<OrderProjection> GetList(DateFilter dispatchDateFilter)
        {
            var predicate = PredicateBuilder.Null<OrderEntity>();

            if (dispatchDateFilter != null)
            {
                switch (dispatchDateFilter.Operator)
                {
                    case DateFilter.OperatorEnumeration.Inferior:
                        predicate = predicate.And(order => order.DispatchDate < dispatchDateFilter.Date);
                        break;

                    case DateFilter.OperatorEnumeration.Equal:
                        predicate = predicate.And(order => order.DispatchDate == dispatchDateFilter.Date);
                        break;

                    case DateFilter.OperatorEnumeration.Superior:
                        predicate = predicate.And(order => order.DispatchDate > dispatchDateFilter.Date);
                        break;
                }
            }

And now, I would like to filter the orders by dispatch date but also by creation date. Of course, I can write the same code (switch/case) as above for the creation date but...

My question : is there a way to create a generic method to apply the date filter on another column, something like this :

  public IList<OrderProjection> GetList(DateFilter dispatchDateFilter, DateFilter creationDateFilter)
        {
                var predicate = PredicateBuilder.Null<OrderEntity>();
                predicate = FilterDate<OrderEntity>(predicate, OrderFields.DispatchDate.Name, dispatchDateFilter);
                predicate = FilterDate<OrderEntity>(predicate, OrderFields.CreationDate.Name, creationDateFilter);

Add a new Predicate (Func) to the PredicateBuilder (ex. AndField), which accepts an extra parameter.

Implement such method, and inside check the extra parameter and accordingly choose which field you'd use in the query.

Then in the code GetList() method, which use the new predicates AndField().

e.g.:

predicate = predicate.AndField(order => MyNewMethod(order, extraParam));

Sorry but I don't understand how I would have to implement the MyNewMethod() function in a generic way.

Can you give me an example, please

bool MyNewMethod(order, string extraParam)
{
 if(extraParam == "xyz")
{
    return (order.DispatchDate == DateTime.Now)
}
else if(extraParam == "abc")
{
    return (order.OtherDate== DateTime.Now)
}
}

Not so generic, but serves the purpose.

OK but I would have to do the switch for all needed properties but also for each comparison operator (<, =, >). So it would be a lot of pain, no ?

Unless you create a method (MyNewMethod) for each operator.

When I try your solution, I have the following error : "The binary expression '(value(Bejot.Erp.Common.Server.Dao.OrderDao).FilterDate([531]) = True)' can't be converted to a predicate expression."

where **FilterDate **is defined as

private bool FilterDate(OrderProjection order)
{
      return (order.DispatchDate == DateTime.Now);
}

and use here

if (dispatchDateFilter != null)
{
      predicate = predicate.And(order => FilterDate(order));
}

OK in fact, I have been able to write the method as you explain me :

 public static Expression<Func<OrderProjection, bool>> GetFilterDateCondition(string propertyName, ComparisonOperator comparisonOperator, DateTime date)
        {
            switch (comparisonOperator)
            {
                case Bejot.Common.Shared.Enumeration.ComparisonOperator.LessThan:
                    if (propertyName == OrderFields.DispatchDate.Name)
                    {
                        return o => (o.DispatchDate < date);
                    }
                    else
                    {
                        return o => (o.OrderDate < date);
                    }

                case Bejot.Common.Shared.Enumeration.ComparisonOperator.EqualTo:
                    if (propertyName == OrderFields.DispatchDate.Name)
                    {
                        return o => (o.DispatchDate == date);
                    }
                    else
                    {
                        return o => (o.OrderDate == date);
                    }

                case Bejot.Common.Shared.Enumeration.ComparisonOperator.MoreThan:
                    if (propertyName == OrderFields.DispatchDate.Name)
                    {
                        return o => (o.DispatchDate > date);
                    }
                    else
                    {
                        return o => (o.OrderDate > date);
                    }

                default:
                    throw new NotImplementedException();
            }
        }

but

1) it's really not a generic method 2) it's the same that writing 1 method for each date's property I want to filter with.

=> Is there a solution to use reflection (I know it's not really good but...) to retrieve the desired property and compare it to the passed parameter? using reflection perhaps?

First of all, the real problem is Linq itself, as it's not designed to do dynamic predicate building. So your problem is really a linq problem which occurs in EF, Linq to SQL, our linq provider and any other linq provider as well.

The real problem is the bold expression in the following line: predicate = predicate.And(order => order.DispatchDate < dispatchDateFilter.Date);

As 'And' expects an Expression<Func<T, bool>>, this lambda isn't compiled to IL but to calls to extension methods which build an expression, in this case a LambdaExpression. The lambda expression has 1 parameter named 'order', of type OrderEntity. The body of the lambda expression is a BinaryExpression with 2 sides: order.DispatchDate and dispatchDateFilter.Date. Both sides are memberexpression instances which refer to a given object: the left side to the member 'DispatchDate' on the parameter of the lambda expression, and the right side to the member 'Date' on the method member dispatchDateFilter.

So, to do this in a generic way, you have to build this LambdaExpression instance in-memory and pass it to the Add or Or method of the predicate builder.

Below I've written an example: a test which constructs a dynamic filter for 'Customer' and the method which builds the actual expression:

[Test]
public void FetchWithWhereLambdaBuildInMethod()
{
    using(DataAccessAdapter adapter = new DataAccessAdapter())
    {
        LinqMetaData metaData = new LinqMetaData(adapter);
        System.Linq.Expressions.Expression<Func<CustomerEntity, bool>> whereFilter = BuildWhereFilter(new LambdaCreationParameter() { Country = "Germany" });
        var q = metaData.Customer.Where(whereFilter);
        foreach(var v in q)
        {
            Assert.AreEqual(v.Country, "Germany");
        }
    }
}


private System.Linq.Expressions.Expression<Func<CustomerEntity, bool>> BuildWhereFilter(LambdaCreationParameter filterValue)
{
    var parameter = System.Linq.Expressions.Expression.Parameter(typeof(CustomerEntity), "p");
    var entityMember = typeof(CustomerEntity).GetProperty("Country");
    var leftSide = System.Linq.Expressions.Expression.MakeMemberAccess(parameter, entityMember);
    var filterValueMember = filterValue.GetType().GetProperty("Country");
    var rightSide = System.Linq.Expressions.Expression.MakeMemberAccess(System.Linq.Expressions.Expression.Constant(filterValue), filterValueMember);
    var lambdaBody = System.Linq.Expressions.Expression.Equal(leftSide, rightSide);
    return (System.Linq.Expressions.Expression<Func<CustomerEntity, bool>>)System.Linq.Expressions.Expression.Lambda(lambdaBody, parameter);
}

As you can see, the names of the members are in strings, so you can pass anything to it. The Expression.Equal() method creates a leftside == rightside expression, you of course want to use the LessThan method...

hope this helps a bit.

We created the QuerySpec API to overcome things like this as our queryspec as well as our own low-level query API allows dynamic filter building without the low-level expression hoopla.

1) Yes of course

First of all, the real problem is Linq itself, as it's not designed to do dynamic predicate building. So your problem is really a linq problem which occurs in EF, Linq to SQL, our linq provider and any other linq provider as well.

2) Not really friendly but thanks for the demonstration

[Test]
public void FetchWithWhereLambdaBuildInMethod()
{
    using(DataAccessAdapter adapter = new DataAccessAdapter())
    {
        LinqMetaData metaData = new LinqMetaData(adapter);
        System.Linq.Expressions.Expression<Func<CustomerEntity, bool>> whereFilter = BuildWhereFilter(new LambdaCreationParameter() { Country = "Germany" });
        var q = metaData.Customer.Where(whereFilter);
        foreach(var v in q)
        {
            Assert.AreEqual(v.Country, "Germany");
        }
    }
}


private System.Linq.Expressions.Expression<Func<CustomerEntity, bool>> BuildWhereFilter(LambdaCreationParameter filterValue)
{
    var parameter = System.Linq.Expressions.Expression.Parameter(typeof(CustomerEntity), "p");
    var entityMember = typeof(CustomerEntity).GetProperty("Country");
    var leftSide = System.Linq.Expressions.Expression.MakeMemberAccess(parameter, entityMember);
    var filterValueMember = filterValue.GetType().GetProperty("Country");
    var rightSide = System.Linq.Expressions.Expression.MakeMemberAccess(System.Linq.Expressions.Expression.Constant(filterValue), filterValueMember);
    var lambdaBody = System.Linq.Expressions.Expression.Equal(leftSide, rightSide);
    return (System.Linq.Expressions.Expression<Func<CustomerEntity, bool>>)System.Linq.Expressions.Expression.Lambda(lambdaBody, parameter);
}

3) What do you think about DynamicQueryable? (see http://muxtonmumbles.blogspot.fr/2010/01/generics-and-dynamic-linq.html) I tried to use it and have been able to write something like below. Now, I need to be able to use it with PredicateBuilder.

List<FilterCriteria> criteriaList = new List<FilterCriteria>();
criteriaList.Add(new FilterCriteria { FieldName = OrderFields.DispatchDate.Name, ComparisonOperator = dispatchDateFilter.Operator, Value = dispatchDateFilter.Date });

DynamicLinqFilter filter = new DynamicLinqFilter { Criteria = criteriaList };
query = query.Where(filter.GetDynamicLinqString(), filter.GetDynamicLinqParameters());

sybjeb wrote:

1) Yes of course

First of all, the real problem is Linq itself, as it's not designed to do dynamic predicate building. So your problem is really a linq problem which occurs in EF, Linq to SQL, our linq provider and any other linq provider as well.

2) Not really friendly but thanks for the demonstration

[Test]
public void FetchWithWhereLambdaBuildInMethod()
{
    using(DataAccessAdapter adapter = new DataAccessAdapter())
    {
        LinqMetaData metaData = new LinqMetaData(adapter);
        System.Linq.Expressions.Expression<Func<CustomerEntity, bool>> whereFilter = BuildWhereFilter(new LambdaCreationParameter() { Country = "Germany" });
        var q = metaData.Customer.Where(whereFilter);
        foreach(var v in q)
        {
            Assert.AreEqual(v.Country, "Germany");
        }
    }
}


private System.Linq.Expressions.Expression<Func<CustomerEntity, bool>> BuildWhereFilter(LambdaCreationParameter filterValue)
{
    var parameter = System.Linq.Expressions.Expression.Parameter(typeof(CustomerEntity), "p");
    var entityMember = typeof(CustomerEntity).GetProperty("Country");
    var leftSide = System.Linq.Expressions.Expression.MakeMemberAccess(parameter, entityMember);
    var filterValueMember = filterValue.GetType().GetProperty("Country");
    var rightSide = System.Linq.Expressions.Expression.MakeMemberAccess(System.Linq.Expressions.Expression.Constant(filterValue), filterValueMember);
    var lambdaBody = System.Linq.Expressions.Expression.Equal(leftSide, rightSide);
    return (System.Linq.Expressions.Expression<Func<CustomerEntity, bool>>)System.Linq.Expressions.Expression.Lambda(lambdaBody, parameter);
}

Yeah, well, that's Microsoft's fault, sorry. You asked, so I gave you an example. I spend time on this so you can solve your problem. But now I have the feeling you weren't interested in the answer?

3) What do you think about DynamicQueryable? (see http://muxtonmumbles.blogspot.fr/2010/01/generics-and-dynamic-linq.html) I tried to use it and have been able to write something like below. Now, I need to be able to use it with PredicateBuilder.

List<FilterCriteria> criteriaList = new List<FilterCriteria>();
criteriaList.Add(new FilterCriteria { FieldName = OrderFields.DispatchDate.Name, ComparisonOperator = dispatchDateFilter.Operator, Value = dispatchDateFilter.Date });

DynamicLinqFilter filter = new DynamicLinqFilter { Criteria = criteriaList };
query = query.Where(filter.GetDynamicLinqString(), filter.GetDynamicLinqParameters());

Never used it, so I can't comment on it. 'GetDynamicLinqString()' suggests it works with strings, which of course won't work in normal linq queries.

In all honesty, why not simply write the query in e.g. QuerySpec, it's way easier.

QuerySpec is certainly the solution but I don't want to have to use Linq in some cases, QuerySpec in other cases and finally the normal LLBLGEN framework else. It could be a bit hard to understand to other people who could arrive on the project.

If really needed, I will do it but if I can find another way, I prefer.

In fact, I understand that Linq is not really THE solution to write query with LLBLGEN and it would have been better to use QuerySpec or low level framework because in these 2 modes, I would have been able to write all queries.

But, the Linq way to write query was so interesting (as it look like SQL) that I decided to use it. Another thing was that with Linq it was easy to use projection. With LLBLGEN normal runtime, perhaps I'm wrong but it was not possible as I had to create a TypedListView in LLBLGEN designer to be able to do a projection.

Frans,

That was a great description of building a dynamic predicate in linq using expressions. I've been using this functionality in linq for some time, but this succinct description of building a predicate was helpful.

And QuerySpec is working well for us in creation of dynamic queries.

Jeremy