CODEWITHSUNDEEP
c#asp.net-mvclinqasp.net-mvc-4linq-to-entities
Estagra
Estagra

Reputation: 43

Multiple CheckBox Selection Result using Linq without hardcoding

Illustration of database (actual column and table name is different)

EmployeeeID      IsSicked   IsRetired    IsHoliday  
1                false      false        true
2                true       false        true
3                true       true         true
4                false      false        false

What problem am I having?
I have a checkbox for IsSicked, IsRetired and IsHoliday in my application, which allow user to check multiple selection and submit.
The system will return list of employee from database based on the selection of user. For example, when user tick IsSicked and IsRetired, system will return list of user who is sicked and is retired from database.

Based on below code, I need to write 2^3 = 8 possibilities to get the result. As the column expand to n columns(exluding employeeID column), I will need to write 2^n if else statement to get the query. If there are 10 column (10 checkboxes selection), then i need to write 2^10 = 1024 if else statement

It seems to me that this supposed to be quite a common problem faced by developers, unfortunately i can't find a good solution to this online

Expectation of solution
Generic combination of C# code and/or LINQ query that no need hard code the logic as below if(...) else if (...)

var employee = db.Employee.AsQueryable():
if(model.IsSicked == true)
{
    employee.Where(z => z.IsSicked == true)
}
else if(model.IsRetired == true)
{
    employee.Where(z => z.IsRetired == true)
}
...
...

else if(model.IsSicked == true && model.IsRetired == true)
{
    employee.Where(z => z.IsSick == true || z.IsRetired == true)
}
else if (model.IsSicked == true && model.IsRetired == true && model.IsHoliday == true)
{
    employee.Where(z => z.IsSick == true || z.IsRetired == true || z.IsHoliday == true)
}

Upvotes: 2

Views: 1442

Answers (3)

dav_i
dav_i

Reputation: 28097

Why not use a enum with [Flags]:

[Flags]
public enum EmployeeStatus
{
    None = 0,
    Sick = 1,
    Retired = 2,
    Holiday = 4
}

In your database you can store this as an integer.

Your model becomes similar to

public class Employee
{
    public int Id { get; set; }
    public EmployeeStatus Status { get; set }
}

then LINQ queries become similar to:

employee.Where(z => z.Status == EmployeeStatus.Sick)

Because of using [Flags] you can do the following for multiple states, e.g. sick and retired:

employee.Where(z => z.Status == (EmpoyeeStatus.Sick | EmployeeStatus.Retired))

Please note that | is the boolean OR which translates in the following:

  EmpoyeeStatus.Sick | EmployeeStatus.Retired
= 1 | 2
= b01 | b10
= b11
= 3

so effectively works as "and" in our logic.

Upvotes: 3

DavidG
DavidG

Reputation: 118937

You can build up your Where predicate like this:

var employee = db.Employee.AsQueryable():

if(model.IsSicked == true)
    employee = employee.Where(z => z.IsSicked == true)

if(model.IsRetired == true)
    employee = employee.Where(z => z.IsRetired == true)

if (model.IsHoliday == true)
    employee = employee.Where(z => z.IsHoliday == true)

Alternatively, you could do this:

employee.Where(z => (z.IsSicked || !model.IsSicked) &&
                    (z.IsRetired || !model.IsRetired) &&
                    (z.IsHoliday || !model.IsHoliday))

Upvotes: 0

Mike
Mike

Reputation: 4051

You can use library called LINQKit and use its PredicateBuilder to create predicate with dynamic ORs.

var employee = db.Employee.AsQueryable():
var predicate = PredicateBuilder.False<Employee>();
if (model.IsSicked == true)
    predicate = predicate.Or(p => p.IsSick == model.IsSick);
if (model.IsRetired == true)
    predicate = predicate.Or(p => p.IsRetired == model.IsRetired);
if (model.IsHoliday == true)
    predicate = predicate.Or(p => p.IsHoliday == model.IsHoliday);
var result = employee.AsExpandable().Where(c => predicate.Invoke(c)).ToList()

Upvotes: 0

Related Questions