Linq: adding conditions to the where clause conditionally

entity-framework linq linq-to-sql

Question

I have a similar question.

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

Depending on whether those requirements were supplied to the method conducting this query, I want to add other conditions like age and height. User Division will be a part of all terms. If an age was given, I would want to include it in the question. Likewise, if height was mentioned, I'd want to include that as well.

If SQL queries were to be utilized, I would have added them to the main strSQL query using string builder. However, with Linq, the only use of an IF condition that comes to mind is to write the same query three times, with an extra condition in each IF block. Exists a more effective approach to this?

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

Accepted Answer

Unless you call,ToList() you may add your final mapping to the DTO type.Where create the outcomes in the end by adding clauses as you go:

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();

Even yet, there will only be one database call as a consequence, which is almost equivalent to writing the query in one pass.

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

Popular Answer

I often utilize method chaining, yet I still have this issue. Here is the 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 aids in preventing chain breakdowns. The same alsoConditionalOrderBy and ConditionalOrderByDescending very useful.



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