CODEWITHSUNDEEP
c#linqentity-framework
rao
rao

Reputation: 223

Entity Framework Linq query for filtering records

I am populating a grid. I have two filters for 'MerchantName' and 'MerchantId' columns on screen. If filters are empty and search button is pressed then all record should be displayed otherwise if any of the filter has value the record should be filtered on that. I am using entity framework for pulling data.

merchantsList = dbContext.MERCHANT.Where();

This is my Entity how can I do it in where clause. MerchantName is string type and merchant Id is Int.

Upvotes: 2

Views: 3432

Answers (3)

Rikard
Rikard

Reputation: 3869

Replace INPUT_MERCHANTNAME and INPUT_ID with request values.

var merchantName = INPUT_MERCHANTNAME;
int? id = INPUT_ID;

merchantsList = dbContext.MERCHANT.Where(x =>
  (id.HasValue ? x.Id == id.Value : true) ||
  (!(merchantName == null || merchantName  == "") ? x.MerchantName.Equals(merchantName) : true));

This will give you all records if both id and merchantName is null/empty and if they are specified it will filter.

Upvotes: 2

Søren Lorentzen
Søren Lorentzen

Reputation: 866

Seperate your filters.
It will make it much more readable and it will allow you to easily add more filters at a later time.

var merchantName = INPUT_MERCHANTNAME;
int? id = INPUT_ID;

var list = dbContext.MERCHANT.AsQueryable();

if (!string.IsNullOrWhitespace(merchantName)) {
    list = list.Where(m => m.MerchantName == merchantName);
}

if (id.HasValue) {
    list = list.Where(m => m.Id == id.Value);
}

return list.ToList();

EDIT: This will AND the filters together.
If you only want to use one filter at a time, try this:

var merchantName = INPUT_MERCHANTNAME;
int? id = INPUT_ID;

var list = dbContext.MERCHANT.AsQueryable();

//Lets say ID is more important than name.
if (id.HasValue) {
    list = list.Where(m => m.Id == id.Value);
}else {
    if (!string.IsNullOrWhitespace(merchantName)) {
        list = list.Where(m => m.MerchantName == merchantName);
    }
}

return list.ToList();

If you want to OR both filters:

var merchantName = INPUT_MERCHANTNAME;
int? id = INPUT_ID;

var list = dbContext.MERCHANT.AsQueryable();

list = list.Where(m => (merchantName != null && m.MerchantName == merchantName) || (id != null && m.Id == id.Value));


return list.ToList();

Upvotes: 1

Muhammad Saqlain
Muhammad Saqlain

Reputation: 2212

if(textBox.Text == "")
{
  merchantsList = dbContext.MERCHANT.ToList();
}
else
{
  merchantsList = (context.CITY.Where(q => q.MerchantName.Contains(textBox.Text))).ToList();

}

I am using this technique for search.

Upvotes: 0

Related Questions