Linq: adding conditions to the where clause conditionally

entity-framework linq linq-to-sql

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?

1
96
9/16/2019 1:25:08 AM

Accepted Answer

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.

172
6/4/2012 4:31:47 PM

Popular Answer

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.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow