Linq: adding conditions to the where clause conditionally

Question

I have a query like this

(from u in DataContext.Users
       where u.Division == strUserDiv 
       && u.Age > 18
       && u.Height > strHeightinFeet  
       select new DTO_UserMaster
       {
         Prop1 = u.Name,
       }).ToList();

I want to add the various conditions like age, height based on whether those conditions were provided to the method running this query. All conditions will include user Division. If age was supplied I want to add that to the query. Similarly, if height was provided I want to add that as well.

If this were to be done using sql queries I would have used string builder to have them appended to the main strSQL query. But here in Linq I can only think of using an IF condition where I will write the same query thrice, with each IF block having an additional condition. Is there a better way to do this?

Answer 1

If you do not call ToList() and your final mapping to the DTO type, you can add Where clauses as you go, and build the results at the end:

var query = from u in DataContext.Users
   where u.Division == strUserDiv 
   && u.Age > 18
   && u.Height > strHeightinFeet
   select u;

if (useAge)
   query = query.Where(u => u.Age > age);

if (useHeight)
   query = query.Where(u => u.Height > strHeightinFeet);

// Build the results at the end
var results = query.Select(u => new DTO_UserMaster
   {
     Prop1 = u.Name,
   }).ToList();

This will still only result in a single call to the database, which will be effectively just as efficient as writing the query in one pass.

Answer 2

I usually use method chaining but have the same problem. And here is extension I use

public static IQueryable<T> ConditionalWhere<T>(
        this IQueryable<T> source, 
        Func<bool> condition,
        Expression<Func<T, bool>> predicate)
    {
        if (condition())
        {
            return source.Where(predicate);
        }

        return source;
    }

It helps to avoid chain breaks. Also the same ConditionalOrderBy and ConditionalOrderByDescending are helpful.

Answer 3

one option.

bool? age = null

(from u in DataContext.Users
           where u.Division == strUserDiv 
           && (age == null || (age != null && u.Age > age.Value))
           && u.Height > strHeightinFeet  
           select new DTO_UserMaster
           {
             Prop1 = u.Name,
           }).ToList();

or you could switch to the method syntax for linq and use if conditions to attach expressions to the where clause.

Answer 4

Simply I am using it in my where clause as

    public IList<ent_para> getList(ent_para para){
     db.table1.Where(w=>(para.abc!=""?w.para==para.abc:true==true) && (para.xyz!=""?w.xyz==para.xyz:true==true)).ToList();
}

Answer 5

Based on certain condtion add the where condition ...

from u in DataContext.Users
where u.Division == strUserDiv 
&& u.Age != null ? u.Age > 18 : 1== 1
&& u.Height != null ? u.Height > 18 : 1== 1
&& u.Height != null ? u.Height > 18 : 1== 1
 select new DTO_UserMaster
       {
         Prop1 = u.Name,
       }).ToList();

Answer 6

Here is my code to do a similar thing. This is a method on my WCF SOAP Web Service api.

    public FruitListResponse GetFruits(string color, bool? ripe)
    {
        try
        {
            FruitContext db = new FruitContext();
            var query = db.Fruits.Select(f => f);
            if (color != null)
            {
                query = query.Where(f => f.Color == color);
            }
            if (ripe != null)
            {
                query = query.Where(f => f.Ripe == ripe);
            }
            return new FruitListResponse
            {
                Result = query.Select(f => new Fruit { Id = f.FruitId, Name = f.Name }).ToList()
            };
        }
        catch (Exception e)
        {
            return new FruitListResponse { ErrorMessage = e.Message };
        }
    }

The base query is Select(f => f) which means basically EVERYTHING, and the Where clauses are optionally attached to it. The final Select is optional. I use to convert the database rows objects into result "Fruit" objects.

Answer 7

Assuming following parameter,

Int? Age = 18;

Simply using && and || conditional operators we can have another version.

(from u in DataContext.Users
where u.Division == strUserDiv 
    && (Age == null || u.Age > Age)
    && (Param1 == null || u.param1 == Param1)
    && u.Height > strHeightinFeet
select new DTO_UserMaster
{
    Prop1 = u.Name,
}).ToList();

Like Param1 you can add any number of parameters for search condition.

Answer 8

I just came across this looking for something else, but thought I'd throw in lambda version.

First, I'd create a class like this to pass parameters into a data layer:

   public class SearchParameters() {
       public int? Age {get; set;}
       public string Division {get;set;}
       etc
    }

Then, in my data layer, something like this:

public IQueryable<User> SearchUsers(SearchParameters params) 
{
    var query = Context.Users;
    if (params.Age.HasValue)
    {
         query = query.Where(u => u.Age == params.Age.Value);
    }
    if (!string.IsNullOrEmpty(params.Division)
    {
        query = query.Where(u => u.Division == params.Division);
    }
    etc
    return query;
}

Where you materialize the query is up to you. Might have a layer between the app and data which converts db-specific representations to db-agnostic (maybe you query multiple data sources). That layer might get multiple types of queryables from these sources and map them to a common POCO representation, for example.

Answer 9

Just to add to the above accepted answer here, if you are doing a dynamic search on a join, consider to return a new object with both tables (t1,t2) in the initial linq query so you can access them individually to do the conditional search.

var query = from t1 in _context.Table1
            join t2 in _context.Table2 on t1.Table1Id equals t2.Table1IdId
            select new { t1, t2 };

        if (!string.IsNullOrEmpty(searchProperty1))
        {
            query = query.Where(collection => collection.t1.TableColumn == searchProperty1);
        }
        if (!string.IsNullOrEmpty(searchProperty2))
        {
            query = query.Where(collection => collection.t2.TableColumn == searchProperty2);
        }
        ....etc.

I got the answer i was looking for here in regards to joining two tables and querying specific columns on either of the tables