CODEWITHSUNDEEP
c#sqlwpflinqentity-framework
Angel
Angel

Reputation: 309

Reduce linq query for filtering

I have a view with 3 textboxes which bind to properties in the ViewModel SupplierName, Contact, Address and one button which bind to SearchCommand property in my ViewModel.

My requirement is to filter Supplier records based on the above properties. I used EntityFramework.

The user can enter any of the above textboxes which lead me to write 9 different queries. For instance if the user inputs data only on the SupplierName textbox then I need to run one query with SupplierName as parameter. If the user enters SupplierName and Contact textboxes then I need to run another query. And so on.

Here is my code:

public IEnumerable<Model.Supplier> GetAllSuppliersBySearch(string nameMatch, string contactMatch, string phoneMatch)
    {

        if(nameMatch!=null)
        {
             var q = from f in Context.Suppliers
                where f.SupplierName==nameMatch 
                select f;
        }
        else if(contactMatch!=null)
        {
             var q = from f in Context.Suppliers
                where  f.ContactName==contactMatch 
                select f;
        }
        else if(phoneMatch!=null)
        {
            var q = from f in Context.Suppliers
                where  f.ContactName==contactMatch 
                select f;
        }

        return q.AsEnumerable();
    }

Instead of writing multiple queries, how to accomplish this with one query or in any optimized way?

Upvotes: 3

Views: 380

Answers (1)

Sergey Berezovskiy
Sergey Berezovskiy

Reputation: 236268

Compose query with lambda syntax:

IQueryable<Supplier> query = Context.Suppliers;

if (!String.IsNullOrEmpty(nameMatch))
   query = query.Where(s => s.SupplierName == nameMatch);

if (!String.IsNullOrEmpty(contactMatch))
   query = query.Where(s => s.ContactName == contactMatch);

// etc

return query.AsEnumerable();

Another option is adding parameter-checking conditions to query

var query = 
   from s in Context.Suppliers
   where (String.IsNullOrEmpty(nameMatch) || s.SupplierName == nameMatch) &&
         (String.IsNullOrEmpty(contactMatch) || s.ContactName == contactMatch)
         // etc
   select s;

Upvotes: 9

Related Questions