Conditional Where clause in LINQ

Question

suppose i am showing data in grid and i have many textboxes for filter the data. textbox for employee id. if employee id textbox is empty then no where clause will be added but if it is not empty then where clause will be added for that. the same way we can filter data if salary textbox has value or employee name textbox has value.

i try to compose a conditional LINQ query but got error. here is mine

var sName="";

var r = from t in TblFamilies
where 1 == 1
if(sName!="")
{
  && t.Name="Keith";
};

select new
{
    t.ID,
    t.ParentID,
    t.Name,
    t.CurDate
};

r.Dump();               

Answer 1

If you want to mix And operator and Or operator together, check PredicateBuilder out here: http://www.albahari.com/nutshell/predicatebuilder.aspx

You can simply write like:

// begin with true if you start with And operator.
var predicate = PredicateBuilder.True<TblFamilie>();
predicate = predicate.And(t => t.CureDate < DateTime.UtcNow.AddDays(-1));
// you can mix with Or operator too.
predicate = predicate.Or(t => t.Name.Contains("blah"));

var results = context.TblFamilie
.Where(predicate)
.Select(new
{
  // your projection here...
});


// begin with false if you start with Or operator.
var predicate2 = PredicateBuilder.False<TblFamilie>();
predicate2 = predicate2.Or(t => t.CureDate < DateTime.UtcNow.AddDays(-1));
// you can mix with And operator too.
predicate2 = predicate2.And(t => t.Name.Contains("blah"));

var results = context.TblFamilie
.Where(predicate)
.Select(new
{
  // your projection here...
});


// even nesting is possible
var inner = PredicateBuilder.False<TblFamilie>();
inner = inner.Or (p => p.Name.Contains("foo"));
inner = inner.Or (p => p.Name.Contains("bar"));

var outer = PredicateBuilder.True<TblFamilie>();
outer = outer.And (p => p.CureDate > DateTime.UtcNow.AddDays(-3));
outer = outer.And (p => p.CureDate < DateTime.UtcNow.AddDays(-1));
outer = outer.And (inner);

var results = context.TblFamilie
.Where(outer)
.Select(new
{
  // your projection here...
});

Updated

Okay, lets assume you have a Family class, and you get 'Families' from some where. You can use PredicateBuilder like this:

// you have 4 families from DB, API or anywhere.
var failies = new List<Family>
{
    new Family { Id = 1, ParentId = 1, Name = "foo", Birthday = new DateTime(1971, 1, 1) },
    new Family { Id = 1, ParentId = 1, Name = "bar", Birthday = new DateTime(1982, 1, 1) },
    new Family { Id = 1, ParentId = 1, Name = "foobar", Birthday = new DateTime(1993, 1, 1) },
    new Family { Id = 1, ParentId = 1, Name = "fake", Birthday = new DateTime(2000, 1, 1) },
};

// make predicate!
// if a family's Birthday is before than 1980 'or' Name contains "ke".
var predicate = PredicateBuilder.True<Family>();
predicate = predicate.And(o => o.Birthday < new DateTime(1980, 1, 1));
predicate = predicate.Or(o => o.Name.Contains("ke"));

// you should make IQueryable in order to use PredicateBuilder.
var result = failies.AsQueryable()
    .Where(predicate)
    .Select(o => new
    {
        o.Id, o.Name, o.Birthday    // only project what you want.
    })
    .ToList();

// now, result should contains "foo" and "fake".
foreach (var family in result)
{
    Debug.WriteLine("Name: " + family.Name);
}

Updated2

You can copy & paste to LinqPad in order to test how it works. Before you run this in the LinqPad,

• Download LinqKit.dll from above link.
• Make sure press 'F4' > Add > Browse > select LinqKit.dll > Add LinqKit namespace in the 'Additional Namespace Imports' tab.
• In the Query panel, choose Language to 'C# Statement(s)'

paste this and run.

// you have 4 strings from DB, API or anywhere.
var strings = new List<string>
{
    "foo",
    "bar",
    "foobar",
    "fake"
};


// make predicate!
// if a string contains "oo" or "ke"
var predicate = PredicateBuilder.True<string>();
predicate = predicate.And(o => o.Contains("oo"));
predicate = predicate.Or(o => o.Contains("ke"));

// you should make IQueryable in order to use PredicateBuilder.
var result = strings.AsQueryable()
    .Where(predicate)
    .ToList();

// now, result should contains "foo", "foobar" and "fake".
foreach (var stringResult in result)
{
    Debug.WriteLine("Name: " + stringResult);
}

Answer 2

Try this:-

First select the data:-

var r = from t in TblFamilie
select new
{
    t.ID,
    t.ParentID,
    t.Name,
    t.CurDate
};

Then you can filter based on condition:-

   if (sName!="")
        r = r.Where(x => x.Name == sName);