CODEWITHSUNDEEP
linqentity-frameworklinq-to-sql
user20358
user20358

Reputation: 14766

Linq: adding conditions to the where clause conditionally

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?

Upvotes: 148

Views: 149758

Answers (9)

Riaan Saayman
Riaan Saayman

Reputation: 106

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

Upvotes: 3

Scott Peterson
Scott Peterson

Reputation: 73

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.

Upvotes: 1

Sushant Yelpale
Sushant Yelpale

Reputation: 889

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.

Upvotes: 0

Melu
Melu

Reputation: 1905

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

Upvotes: 8

Minhaj
Minhaj

Reputation: 188

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

Upvotes: 10

Yuriy Granovskiy
Yuriy Granovskiy

Reputation: 679

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.

Upvotes: 35

John Henckel
John Henckel

Reputation: 11417

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.

Upvotes: 5

Reed Copsey
Reed Copsey

Reputation: 564891

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.

Upvotes: 255

Matthew Vines
Matthew Vines

Reputation: 27581

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.

Upvotes: 25

Related Questions