using if else with LINQ Where

Question

I want to generate dynamic query to check manage the where clause with number of parameters available...if some parameter is null i don't want to include it in the where clause

var test = from p in _db.test
           where if(str1 != null){p.test == str} else i dnt wanna check p.test

I have around 14 parameters for the where clause

need help, thanks

Answer 1

You can do it in steps:

// set up the "main query"
var test = from p in _db.test select _db.test;
// if str1 is not null, add a where-condition
if(str1 != null)
{
    test = test.Where(p => p.test == str);
}

Answer 2

In addition to @Fredrik's answer, you can also use the short-circuit rules when evaluating boolean expressions like so:

var test = from p in _db.test
           where str1 == null || p.test == str1;

Edit If you have lots of strings to test, (str1, str2, etc...) then you can use the following, which will be translated to an SQL IN clause:

var strings = new List<string>();
if (str1 != null) strings.Add(str1);
if (str2 != null) strings.Add(str2);
if (str3 != null) strings.Add(str3);
...

var test = from p in _db.test
           where strings.Contains(p.test);

It's even easier if your strings are already in a collection (which, if you've got 14 of them, I assume they would be...)

Answer 3

Consider param1 and param2 are the parameters. Your query should be as under:

string param1 = "Value1";
string param2 = "Value2";

var q = from bal in context.FxBalanceDetails
        where (string.IsNullOrEmpty(param1) || bal.Column1 == param1)
               && (string.IsNullOrEmpty(param2) || bal.Column2 == param2)
        select bal;

This will ensure that the where clause gets applied for the particular parameter only when it is not null.

Answer 4

Do you check the strings against the same Field of the entity? If so you can write something like:

var strings = new[] { "foo", "bar", "ok", "", null };
var query = dataContext.YourTable.AsQueryable();
query = strings.Where(s => !string.IsNullOrEmpty(s))
       .ToList()
       .Aggregate(query, (q, s) => q.Where(e => e.YourField == s));

EDIT: The previous solution is overcomplicated:

var strings = new[] { "foo", "bar", "ok", "", null }.Where(s => !string.IsNullOrEmpty(s))
       .ToList();
var query = dataContext.YourTable.Where(e => strings.Contains(e.YourField));