Consistent LINQ statement with multiple search criteria

Question

I have a form with several search criteria that the user can use to search for employee data, for example. FirstName, LastName, HireDate, Department, etc.

I use LINQ and wonder what method I can use to query the Employes collection based on any of the search criteria, that is, the user does not need to enter everything, but they need to enter at least one of the search parameters.

So far, checking my LINQ statement with two search parameters, it seems like I should check if the search parameter is entered or not. If so, then this can become rather cumbersome for many search options.

// only FirstName is entered if (!string.IsNullOrEmpty(FirstName) && string.IsNullOrEmpty(LastName)) { var employees = DB.Employees .Where(emp => emp.FirstName.Contains(fName)); } // only LastName is entered else if (string.IsNullOrEmpty(FirstName) && !string.IsNullOrEmpty(LastName)) { var employees = DB.Employees .Where(emp => emp.LastName.Contains(lName)); } // both parameters are entered else if (!string.IsNullOrEmpty(FirstName) && !string.IsNullOrEmpty(LastName)) { var employees = DB.Employees .Where(emp => emp.FirstName.Contains(fName)) .Where(emp => emp.LastName.Contains(lName)); } 

FYI, I initially thought that I could just add Where () statements to my LINQ statement with the appropriate search parameters, but noticed that not all records were returned, which should and therefore the above logic of if-then statements.

Answer 1

Something like that:

 IQueryable<Employee> employees = DB.Employees; if (!string.IsNullOrEmpty(FirstName)) { employees = employees .Where(emp => emp.FirstName.Contains(fName)); } if (!string.IsNullOrEmpty(LastName)) { employees = employees .Where(emp => emp.Last.Contains(lName)); } 

Answer 2

You can write it like this:

 var employees = DB.Employees.AsQueryable(); if (!string.IsNullOrEmpty(fName) employees = employees.Where(emp => emp.FirstName.Contains(fName)); if (!string.IsNullOrEmpty(lName) employees = employees.Where(emp => emp.LastName.Contains(lName)); 

Answer 3

I faced a similar problem when the user could select 0, 1 or several values ​​for about 10 fields available for search, and to create this query at runtime.

I ended up using LINQKit: http://www.albahari.com/nutshell/linqkit.aspx

In particular, I used the predicate builder, which is described here: http://www.albahari.com/nutshell/predicatebuilder.aspx

In the above example, you included multiple query in if statements. An alternative is to create a query as you go.

If you were to declare var employee = DB.Employees outside these if statements (assuming this is always relevant), then you can simply use your statements in your if statements, if applicable.

LINQ gives you delayed execution, so you don’t need to have the whole expression in one block (even if it’s most natural to do this, and in many cases you will).

Things get a little more complicated if you want to mix in OR with AND, but where the previous predicate builder comes in.

Unfortunately, I have no examples for sharing, but these links should help you get started well.

Answer 4

  var resultData = (from data in db.Abc where !string.IsNullOrEmpty(firstName) ? data.FirstName == firstName : true && data.UserType == userTypeValue && !string.IsNullOrEmpty(lastName) ? data.LastName == lastName : true && !string.IsNullOrEmpty(gender) ? data.Gender == gender : true && !string.IsNullOrEmpty(phone) ? data.CellPhone == phone : true && !string.IsNullOrEmpty(fax) ? data.Fax == fax : true && !string.IsNullOrEmpty(emailAddress) ? data.Email == emailAddress : true && !string.IsNullOrEmpty(address1) ? data.Address == address1 : true select new { UserName = data.UserName, FirstName = data.FirstName, Address = data.Address, CellPhone = data.CellPhone, Fax = data.Fax, Email = data.Email }).ToList();