Linq with WHERE options

Question

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

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 it be best to create dynamic linq? What is the best model, within linq, to ​​have an optional parameter?

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 hope that there will be a more accurate, more acceptable template for this in linq.

Answer 1

Just assign Where clauses with a null check

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

Answer 2

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

Using this code will allow you to generate dynamic queries on the fly and will not allow you to write 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 with this 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 pretty neat, and it will also give you an idea of ​​how powerful expressions can be.

Answer 3

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

Answer 4

In the Search method, you can do something like the following:

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

Answer 5

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

Answer 6

Here you have 1 request with all the 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(); }