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?

That `.toList()` should be `.ToList()`. But then again, unless you actually need a list maybe it shouldn't be there at all; enumerating directly cuts out creating a list just to then enumerate it. — Jon Hanna, Jun 12 '15 at 03:33

Nic · Accepted Answer · 2015-06-12T06:40:05.240

22

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

edited Jun 12 '15 at 06:40

answered Jun 12 '15 at 03:16

Nic

12,220
20
77
105

Thank you for the comment, But i am getting Time Out Exception when I am using ToList(), since it is having some crores records. While it is fetching records I am getting exception, But I did not get that exception if I write Query directly using If else separate queries. – Satya Jun 12 '15 at 06:11
Timeout is happening due to timeout in connection which is maintaining live connection to the database, as you are trying to fetch the IQueryable type, option would be to increase connection timeout, which is by default 60 sec or bring data to memory and process it, which is what you are doing in your if loops. Anyway processing this data per call is crazy – Mrinal Kamboj Jun 12 '15 at 08:47

score 1 · Answer 2 · edited Dec 23 '18 at 10:22

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.

score 0 · Answer 3 · edited Apr 28 '19 at 10:29

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

I try this in my sample code and it's work fine. You mean "" as empty, not `null` right — Heinz Siahaan, Jun 12 '15 at 06:11

How can I create a conditional where clause using LINQ

3 Answers3