dynamic linq expression to query, getting issue

Question

I an tryin to join 4 tables within a query as per requirement. where as I wanted to add the conditions in where clause dynamically so, i could able to do this for 2 table query as of now. but this 4 table join is bit the complex join here. To extend the functionality i am using following code to add dynamic where clause :

public static class Extensions
    {
        public static IQueryable<T> AddEqualityCondition<T, V>(this IQueryable<T> queryable,
          string propertyName, V propertyValue)
        {
            ParameterExpression pe = Expression.Parameter(typeof(T), "p");

   IQueryable<T> x = queryable.Where<T>(Expression.Lambda<Func<T, bool>>(Expression.Equal(Expression.Property(pe, typeof(T).GetProperty(propertyName)), Expression.Constant(propertyValue, typeof(V)), false, typeof(T).GetMethod("op_Equality")), new ParameterExpression[] { pe }));
            return (x);
        }
    }

// My code to add where conditions:

Query is:
 var agrs = (from agr in _dbContext.Agreements
                                 join amdv in _dbContext.AgreementMetaDataValues on agr.AgreementID equals amdv.AgreementID 
                                 join emd in _dbContext.EntityMetadatas on amdv.AttributeId equals emd.AttributeId
                                 join et in _dbContext.Entities on agr.EntityID equals et.EntityId
                                 select new  agr, amdv,emd });

//Add dynamically where conditions:
 agrs = agrs.AddEqualityCondition("?????", "A83C82C5-F9D6-4833-A234-EBB5D971280C");

This is working for 2 table join not for more than that. because within complex query it is generating the Annonymouse object. so so what should i need to pass in place of "??????" marks...? typically need to pass the property name as"agr.AgreementId" but here it is throwing the expression as "Value Canot be Null : propertyName" in extension class. Need more guidance for this ...

wouldn't it be easier simply to use: `agrs = agrs.Where(x => x.agr.AgreementId == "A83C82C5-F9D6-4833-A234-EBB5D971280C");` ? — Marc Gravell, Nov 07 '11 at 07:03
I believe Marc's comment is the answer since the query will be build and executed later when you start using it's results (e.g. iterating through them). Until that moment you can add any amount of where clauses as you want. — Insomniac, Nov 07 '11 at 07:16

score 5 · Accepted Answer · answered Nov 07 '11 at 07:07

5

I think you might want to consider something like (as an additional overload):

public static IQueryable<T> AddEqualityCondition<T, V>(
    this IQueryable<T> queryable,
    Expression<Func<T, V>> selector, V propertyValue)
{
    var lambda = Expression.Lambda<Func<T,bool>>(
       Expression.Equal(
           selector.Body,
           Expression.Constant(propertyValue, typeof(V)),
           false, typeof(T).GetMethod("op_Equality")),
        selector.Parameters);
    return queryable.Where(lambda);           
}

and using:

agrs = agrs.AddEqualityCondition(x => x.agr.AgreementId, 
             "A83C82C5-F9D6-4833-A234-EBB5D971280C");

however! it is much easier to use just:

agrs = agrs.Where(x => x.agr.AgreementId ==
             "A83C82C5-F9D6-4833-A234-EBB5D971280C");

answered Nov 07 '11 at 07:07

Marc Gravell

927,783
236
2,422
2,784

This is perfect ! thanks dude. and how if i want to get the Distinct records based on x.agr.AgreementId ? – Red Swan Nov 07 '11 at 07:43
@Red you can't specifically get "distinct by [some member]", because ultimately SQL doesn't really support that. If this is **LINQ-to-Objects**, it is possible to **write** a `DistinctBy` easily enough - but not if this is LINQ-to-SQL or EF. If you don't expect many dups, you could just fetch and apply distinct-by locally. – Marc Gravell Nov 07 '11 at 07:47
1

@Red a basic LINQ-to-Objects implementation might look like: ` public static IEnumerable DistinctBy(this IEnumerable source, Func selector) { var seen = new HashSet(); foreach (var item in source) if (seen.Add(selector(item))) yield return item; } ` – Marc Gravell Nov 07 '11 at 07:50
you know Marc, the real scenario is i am having the Dictionary for the Where conditions. and i wanted to add where conditions from this dictionary. so far so good your solution is working for me. it is perfect approach by you. but I am looking for the , to assign the expression in in this way agrs = agrs.Where("agr.AgreementId","A83C82C5-F9D6-4833-A234-EBB5D971280C"); because it is limitation still fro me to make this generic. can guide me ...? – Red Swan Nov 07 '11 at 08:44
@Red just split the string on `.` and apply reflection in turn on each part, composing the expression. A bit painful, but very similar to my OrderBy example here: http://stackoverflow.com/questions/41244/dynamic-linq-orderby/233505#233505 – Marc Gravell Nov 07 '11 at 09:01
Exillent! But how to match with my scenario here? Where should i need to pass dictinary as parameter? and build the Where condition accordingly ? – Red Swan Nov 07 '11 at 09:18
@Red I don't understand your exact scenario – Marc Gravell Nov 07 '11 at 09:28
hmmm... scenario is , instead of sending the expression as parameter, i need to send key as property name and value as property value to check in where clause. so i may have more than one. hence i am keeping it as dictionary as parameter to my invoking method, where for each pair i need to build the where clause ans add it to linq query dynamically. – Red Swan Nov 07 '11 at 09:37

score 1 · Answer 2 · answered Nov 07 '11 at 07:05

1

It's better you go for the predicate builder i.e Dynamically Composing Expression Predicates which allows you to build query dynamically easily.

answered Nov 07 '11 at 07:05

Pranay Rana

164,177
33
228
256

dynamic linq expression to query, getting issue

2 Answers2