CODEWITHSUNDEEP
c#entity-frameworklinq
Lalit Vedula
Lalit Vedula

Reputation: 23

Searching in MVC5 using linq

Using this Linq code I get any values corresponding to the search input i.e if I search country = Italy and gender = female I get both employees from Italy and employees who are female but I need it to be more specific. i.e if I search Country = Italy and Gender = female I need to get female employees from Italy. Please suggest me a Linq code for the same

Also, I have five search inputs (First Name, Last Name, Designation, Country, Gender) so just (&&) only doesn't do the work here!

Here's the code:

List<Employee> Elist = userdb.Employees
    .Where(i => i.FirstName == Fn || 
                i.LastName == Ln || 
                i.Designation == desig ||
                i.Country == country || 
                i.Gender == gender)
    .ToList();

Upvotes: 0

Views: 151

Answers (4)

Jon Roberts
Jon Roberts

Reputation: 2282

Try this

List<Employee> Elist = userdb.Employees
.Where(i => (Fn == null || i.FirstName == Fn ) && 
            (Ln == null || i.LastName == Ln ) && 
            (desig == null || i.Designation == desig) &&
            (country == null || i.Country == country) && 
            (gender == null || i.Gender == gender)
.ToList();

Upvotes: 0

andyb952
andyb952

Reputation: 2018

Personally I would use a PredicateBuilder here. A small example, let's say you just have 2 queries:

Expression<Func<Person, bool>> hasFirstName = p1 => p1.FirstName == Fn;

Expression<Func<Person, bool>> hasLastName= p2 => p2.LastName == Ln";

You could build this into a predicate builder like so and keep on expanding using any sort of logic:

var predicate = PredicateBuilder.False<Employee>();  

if (!string.IsNullOrEmpty(Fn))  
{  
    predicate = predicate.And(e => e.FirstName == Fn);
}  

if (!string.IsNullOrEmpty(Ln))  
{  
    predicate = predicate.And(e => e.FirstName == Ln);
}  

var result = userdb.Employees.Where(predicate);

Upvotes: 0

Stephen
Stephen

Reputation: 69

Most likely, you are not wanting to include criteria that is not filled in. You would only want to filter by a value if the value exists (or is not null). Use an IQueryable to build your search and then assign it to Elist.

IQueryable<Employee> Query = userdb.Employees;

if (Fn != null) {
    Query = Query.Where(i => i.FirstName.Equals(Fn));
}

if (Ln != null) {
    Query = Query.Where(i => i.LastName.Equals(Ln));
}

if (desig != null) {
    Query = Query.Where(i => i.Designation.Equals(desig));
}

if (country != null) {
    Query = Query.Where(i => i.Country.Equals(country));
}

if (gender != null) {
    Query = Query.Where(i => i.Gender.Equals(gender));
}

List<Employee> Elist = Query.ToList();

Upvotes: 0

DavidG
DavidG

Reputation: 119186

This is a situation where the nature of IQueryable comes in very useful. You can add Where clauses to your query without actually executing anything against the database. The SQL would only be executed when you materialise the data, for example using ToList(). This is called deferred query execution. So you can write your code like this:

IQueryable<Employee> query = userdb.Employees;

if(!string.IsNullOrEmpty(Fn))
{
    query = query.Where(e => e.FirstName == Fn);
}

if(!string.IsNullOrEmpty(Ln))
{
    query = query.Where(e => e.LastName == Ln);
}

// etc. etc.

List<Employee> Elist = query.ToList();

Upvotes: 3

Related Questions