Linq with optional WHERE options

Question

I have a .Net function that accepts 3 parameters, all optional. Something like this:

public List<MyObject> Search(string colour, string size, string name)
{
     var result = (from c in MyTable where .... select c).ToList();     
}

My question is, what is the best way to do the where part. Would the best be to create dynamic linq? What's the best pattern, within linq, to have optional where parameters?

So, in SQL, something like this:

SELECT *
FROM MyTable
WHERE (@colour <> '' AND colour = @colour)
  AND (@size <> '' AND size = @size)
  AND (@name <> '' AND name = @name)

But I am hoping there a neater, more acceptable pattern for doing this within linq.

You can use `Expression`s or some 3rd part Dynamic Linq libraries. — Farhad Jabiyev, Oct 04 '15 at 07:25
That SQL suffers from bad execution plans, like all catch-all queries — Panagiotis Kanavos, Dec 16 '20 at 16:02

Fabio · Accepted Answer · 2020-12-16T20:04:34.570

8

Chain Where clauses with checking for null

var result = context.MyTable
    .Where(t => color == null || color == t.Color)
    .Where(t => size == null || size == t.Size)
    .Where(t => name == null || name == t.Name)
    .ToList();

Alternative approach would be to add conditions only when you need them

var query = context.MyTable;

if (color != null) query = query.Where(t => t.Color == color);
if (size != null) query = query.Where(t => t.Size == size);
if (name != null) query = query.Where(t => t.Name == name);

var result = query.ToList();

edited Dec 16 '20 at 20:04

answered Dec 07 '17 at 07:55

Fabio

31,528
4
33
72

Catch-all queries like this result in bad execution plans. If a condition isn't needed there's no need to add it to the chain – Panagiotis Kanavos Dec 16 '20 at 16:01

score 6 · Answer 2 · answered Oct 04 '15 at 07:56

In such cases, I would advise you to use the PredicateBuilder to generate your queries. You can copy the code from here or you could install the LinqKit Nuget Package.

Using this code will allow you to generate dynamic queries on the fly and will prevent you from writing tons of if/else statements.

Statements like...

p => p.Price > 100 &&
 p.Price < 1000 &&
 (p.Description.Contains ("foo") || p.Description.Contains ("far"))

will be generated by this kind of code:

var inner = PredicateBuilder.False<Product>();
inner = inner.Or (p => p.Description.Contains ("foo"));
inner = inner.Or (p => p.Description.Contains ("far"));

var outer = PredicateBuilder.True<Product>();
outer = outer.And (p => p.Price > 100);
outer = outer.And (p => p.Price < 1000);
outer = outer.And (inner);

I think this is fairly neat and it will also give you an understanding on how powerful expressions can be.

Sam Jolan · Answer 3 · 2020-09-16T08:21:05.403

2

var results = olstOfObjects.Where(x => 
    (x.size == size || x.size == "") &&
    (x.color == color || x.color == "") &&
    (x.name == name || x.name == "")).ToList();;

edited Sep 16 '20 at 08:21

answered Oct 04 '15 at 07:43

Sam Jolan

67
5

score 0 · Answer 4 · answered Oct 04 '15 at 07:36

You can do something like the following in your Search method:

var query = from c in MyTable select c;
if (!String.IsNullOrEmpty(colour))
  query = from c in query where c.colour == colour select c;
if (!String.IsNullOrEmpty(size))
  query = from c in query where c.size == size select c;
if (!String.IsNullOrEmpty(name))
  query = from c in query where c.name == name select c;
return query.ToList();

score 0 · Answer 5 · answered Oct 04 '15 at 07:37

How about:

public List<MyObject> Search(string colour, string size, string name)
{
    IEnumerable<MyObject> result = MyTable;

    if(colour != null)
        result = result.Where(o => o.Colour == colour);

    if(size != null)
        result = result.Where(o => o.Size == size);

    ...

    return result.ToList();
}

score 0 · Answer 6 · answered Dec 07 '17 at 07:46

Here you have 1 query with all conditions:

public List<object> Search(string colour, string size, string name)
{
    var query = from c in MyTable
        where
            (string.IsNullOrEmpty(colour) || c.colour == colour) &&
            (string.IsNullOrEmpty(size) || c.size == size) &&
            (string.IsNullOrEmpty(name) || c.name == name)
        select c;

    return query.ToList();
}

Linq with optional WHERE options

6 Answers6

Linked