How can I create a conditional where clause using LINQ [duplicate]

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

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.