Multiple 'Or' statements in EF expression

Question

Given a list of filter parameters in the form of:

public class filterParm
{
    public int age { get; set; }
    public string name { get; set; }
}

Where:

var parms = new List<filterParm>
{
    new filterParm {age = 22, "phil"},
    new filterParm {age = 19, "dave"},
    new filterParm {age = 31, "nick"}
};

How do I write an expression which would result in the following SQL where clause (note the OR operators in bold:

WHERE (age = 22 AND name = "phil")
OR (age = 19 AND name = "dave") OR (age = 31 AND name = "nick")

My current implementation results in each of the statements seperated by the AND operator:

private _dbSet<user> Users { get; set; }
public List<user> CustomFilter(List<filterParm> parms)
{
    IQueryable<TEntity> query = _dbSet;

    if (parms.Count > 0 )
    {
        foreach (var parm in parms)
        {                    
           query = query.Where(u => u.Age == parm.Age && u.Name == parm.Name);
        }
    }

    return query.ToList();
}

The code above results in the following SQL:

WHERE (age = 22 AND name = "phil") AND (age = 19 AND name = "dave") AND (age = 31 AND name = "nick")

What do I need to change in the expression to cause the generated SQL to use 'OR' instead of 'AND' in the indicated locations? I would prefer to avoid using third party libraries where possible.

EDIT: As suggested by @KingKing, a union does return the required results:

private _dbSet<user> Users { get; set; }
public List<user> CustomFilter(List<filterParm> parms)
{
    IQueryable<TEntity> query = _dbSet;

    if (parms.Count > 0)
    {
        query = query.Where(u => u.Age == parms[0].Age && u.Name == parms[0].Name);

        for (int i = 1; i < parms.Count; i++)
        {
            var parm = parms[i];
            query = query.Union(DbSet.Where(u => u.Age == parm.Age && u.Name == parm.Name));
        }                                                   
    }
    return query.ToList();
}

I would still like to know if it is possible to generate the statement using the 'Or' operator as described.

You better have a look at this answer: http://stackoverflow.com/questions/1775050/linq-to-sql-where-or-operator — Giannis Paraskevopoulos, Sep 17 '13 at 09:35
@jyparask It looks like [LinqKit](http://www.albahari.com/nutshell/linqkit.aspx) & the [PredicateBuilder](http://www.albahari.com/nutshell/predicatebuilder.aspx) would work for me. However, I would still be interested in knowing how to implement a solution without using a third party library if possible. — philreed, Sep 17 '13 at 09:58

King King · Accepted Answer · 2013-09-17T10:30:02.683

2

I think using Union will help:

public List<user> CustomFilter(List<filterParm> parms) {
  IQueryable<TEntity> query = _dbSet;
  if (parms.Count > 0 ){
    IQueryable<TEntity> init = _dbSet;
    var firstParm = parms[0];
    query = query.Where(u => u.Age == firstParm.Age && u.Name == firstParm.Name);
    for(int i = 1; i < parms.Count; i++)
    {                    
       var nextParm = parms[i];
       init = init.Where(u => u.Age == nextParm.Age && u.Name == nextParm.Name);
       query = query.Union(init);
    }
  }
  return query.ToList();
}

edited Sep 17 '13 at 10:30

answered Sep 17 '13 at 09:36

King King

61,710
16
105
130

1

The second one can't be converted to EF. It's not possible to use a reference type in a contains :( – Wouter de Kort Sep 17 '13 at 09:52
@WouterdeKort thank you, looks like that `EF` has many limitations :( – King King Sep 17 '13 at 09:54
@KingKing `Concat` has generated SQL with AND operators instead of OR operators, plus it has generated a very complex statement with many bind variables. Either this isn't suitable, or I'm doing it wrong...probably the latter. – philreed Sep 17 '13 at 10:26
@philreed I don't know if the `translated SQL` uses `OR` or `AND` but the result should be the same when using `OR`. – King King Sep 17 '13 at 10:28
@philreed I updated with `Union` instead, it should be better choice. – King King Sep 17 '13 at 10:30
@KingKing `Union` would work because it would remove the requirement of the 'OR' operator but I think your example needs changing a little more for it to work. I'm testing now. – philreed Sep 17 '13 at 10:36
@KingKing I have amended the question to include a variant on the code you provided in your example. Using `union` does return the correct results. If no-one can provide an example which renders SQL using the 'OR' operator in the next couple of days, I will accept your answer. – philreed Sep 17 '13 at 14:51

Multiple 'Or' statements in EF expression

1 Answers1