How can I create a conditional where clause using LINQ

Question

I have a scenario where I only want use WHERE clause when it is necessary, otherwise I just want to run my LINQ query without that WHERE clause.

For example:

if string name = "";

var res = (from a in db.person 
           select new() { Name = a.FullName, DOB = a.DOB }).ToList();

if string name = "satya";

var res = (from a in db.person
           where a.person.contains(name)
           select new() { Name = a.FullName, DOB = a.DOB }).ToList();

I know for this we have to write separate 2 queries separately, but without writing separate queries, how can we combine them into a single query?

Answer 1

You can do:

var res = (from a in db.person
           where name == "" || a.person.Contains(name)
           select new { Name = a.FullName, DOB = a.DOB }
          ).ToList();

Alternatively, here using the fluent syntax, you can build your query and execute it once you're done:

var query = db.person.AsQueryable();

if(!String.IsNullOrEmpty(name)) {
    query = query.Where(a => a.person.Contains(name));
}

var result = query.Select(s => new { Name = s.FullName, DOB = s.DOB })
                  .ToList();

Answer 2

I think you can use code snippet 2 to get the same result with code snippet 1 even name contains string empty. Why you should make 2 different code. Is it for performance issue?

var res = (from a in db.person
where a.person.contains(name)  // if empty, it will return all list, just makes sure it's not null before
select new(){Name=a.FullName,DOB=a.DOB}).toList();

I try this on my sample code and it's work fine

static void TestContains()
{
    IList<CustomEntity> _a = new List<CustomEntity>();
    IList<CustomEntity> _b = new List<CustomEntity>();

    _a.Add(new CustomEntity { ID = 1, Code = "a", Description = "aaa" });
    _a.Add(new CustomEntity { ID = 2, Code = "c", Description = "c" });

    string name = string.Empty;
    _b = _a.Where(a => a.Description.Contains(name)).ToList();

    foreach (CustomEntity temp in _b)
    {
        Console.WriteLine(temp.Description);
    }
}

This will be the result

aaa
c

Answer 3

Following should work, you can tweak it the way you like to achieve the desired result. Only catering to the condition of empty / null string or the name is contained in the a.person, rest all will lead to null, which we filter in the end

db.person.Select(a => {
    if ( String.IsEmptyOrNull(name) || a.person.contains(name))
        return new {Name=a.FullName,DOB=a.DOB};
    else
        return null;
    }
).Where(x => x != null).ToList()

Created it on a text pad, there might be small syntax issue.