How can I add Where expressions to a LINQ query if I'm not sure which properties I'll want to match?

Question

I have a situation where I might want to search by order number or by name. I know I can add a Where expression to my LINQ query, but I only want to add it for the property that I'm searching for! I won't know until the method is called which parameter will be provided, so how can I add the proper condition?

public JsonResult Search(int orderNo=0, string firstName="", string lastName="")
{
    if (orderNo >0){
        //add Condition
    }

    if (firstName.Length > 0){
        //add Condition
    }

    if (lastName.Length > 0){
        //add Condition
    }

    //get Result

    var result = Repository.Orders.Where(???).OrderByDescending(e=> e.orderNo);

    //return
}

Tried [expression trees](http://msdn.microsoft.com/en-us/library/bb882637.aspx) ? — Dejan Janjušević, Apr 19 '12 at 21:33

David Ruttka · Answer 1 · 2012-04-19T21:45:10.497

The following assumes Repository.Orders is returning an IQueryable, but the idea is just to dynamically add the expressions you need. The execution of the query is deferred, so you can build it systematically before actually asking for the results.

// You haven't executed the query yet, you can still build up what you need
var query = Repository.Orders;

if(orderNo >0){
    // You STILL haven't actually executed the query.
    query.Where(x => x.orderNo == orderNo);
}
if(firstName.Length > 0){
    query.Where(x => x.firstname == firstName);
}
if(lastName.Length > 0){
    query.Where(x => x.lastName == lastName);
}

// Even with this, you STILL aren't actually executing the query.
query.OrderByDescending(x => x.orderNo);

// You'll be executing and enumerating the results here, but that's OK because you've fully defined what you want.
return Json(query.ToArray(), JsonRequestBehavior.AllowGet);

score 3 · Accepted Answer · answered Apr 19 '12 at 21:37

Assuming your repository returns IQueryable you can compose your query however you want to:

var query = Repository.Orders;
if(orderNo > 0)
{
    query = query.Where( x => x.OrderId == orderNo);
}

if(firstName.Length > 0)
{
   query = query.Where( x => x.FirstName == firstName);
}
//...

Erik Funkenbusch · Answer 3 · 2012-04-19T21:43:47.613

var result = Repository.Orders.Where(x => 
    (firstName == null || firstName.Trim() == "" || x.FirstName.Contains(firstName)) 
    && (lastName == null || lastName.Trim() == "") || x.LastName.Contains(lastName)) 
    && (orderNo == 0 || x.OrderNo == orderNo)).OrderByDescending(x => x.OrderNo)

@Amit_g's example will work as well, and is arguably easier to read. I like to do it all in one query. It's a matter of preference.

score 1 · Answer 4 · answered Apr 19 '12 at 21:38

If you have to AND these conditions and assuming your repository returns IQueryable (if not change it or add another overload or something)

var query = Repository.Orders

// Build where clause

if (orderNo >0){
    query = query.Where(o => o.OrderNo == orderNo);
}

if (firstName.Length > 0){
    query = query.Where(o => o.FirstName == firstName);
}

if (lastName.Length > 0){
    query = query.Where(o => o.LastName == lastName);
}

// Build OrderBy clause

query = query.OrderByDescending(o => o.orderNo);

// Execute Query

results = query.ToList();

How can I add Where expressions to a LINQ query if I'm not sure which properties I'll want to match?

4 Answers4