Filter/Search using Multiple Fields - ASP.NET MVC

Question

I am using ASP.NET MVC with EF 6.

I have a stock page which shows all the information on stock items. Now I want to filter records too.

In picture below I have 3 options. I might filter by each option, one at a time or by combination of two or with all three.

I was thinking of writing linq query for each and every options selected. But this wouldn't be possible if filter option increases.Is there is any better way to this.

Thanks!

This is what I did in my controller.(currently dropdown has two options, excluding : " -- select one -- ")

public ActionResult StockLevel(string option, string batch, string name)
{
    if (option != "0" && batch == "" && name == "")
    {
        if(option == "BelowMin")
        {
            List<Stock> stk = (from s in db.Stocks
                               where s.Qty < s.Item.AlertQty
                               select s).ToList();
            return View(stk);
        }
        else
        {
            List<Stock> stk = (from s in db.Stocks
                               where s.Qty == s.InitialQty
                               select s).ToList();
            return View(stk);
        }
    }
    if (option == "0" && batch != "" && name == "")
    {
        List<Stock> stk = (from s in db.Stocks
                           where s.BatchNo == batch
                           select s).ToList();
        return View(stk);
    }
    if (option == "0" && batch == "" && name != "")
    {
        List<Stock> stk = (from s in db.Stocks
                           where s.Item.Name.StartsWith(""+name+"")
                           select s).ToList();
        return View(stk);
    }
    return View(db.Stocks.ToList());
}

Answer 1

I recommend you separate concerns and use an approach that the code in your controller be like this, simple, beautiful and extensible:

public ActionResult Index(ProductSearchModel searchModel)
{
    var business = new ProductBusinessLogic();
    var model = business.GetProducts(searchModel);
    return View(model);
}

Benefits:

• You can put anything you need in your ProductSearchModel based on your requirements.
• You can write any logic in GetProducts based on requirements. There is no limitation.
• If you add a new field or option to search, your action and controller will remain untouched.
• If the logic of your search changes, your action and controller will remain untouched.
• You can reuse logic of search wherever you need to search on products, in controllers or even in other business logic.
• Having such ProductSearchModel, you can use it as model of ProductSearch partial view and you can apply DataAnnotations to it to enhance the model validation and help UI to render it using Display or other attributes.
• You can add other business logic related to your product in that business logic class.
• Following this way you can have a more organized application.

Sample Implementation:

Suppose you have a Product class:

public class Product
{
    public int Id { get; set; }
    public int Price { get; set; }
    public string Name { get; set; }
}

You can create a ProductSearchModel class and put some fields you want to search based on them:

public class ProductSearchModel
{
    public int? Id { get; set; }
    public int? PriceFrom { get; set; }
    public int? PriceTo { get; set; }
    public string Name { get; set; }
}

Then you can put your search logic in ProductBusinessLogic class this way:

public class ProductBusinessLogic
{
    private YourDbContext Context;
    public ProductBusinessLogic()
    {
        Context = new YourDbContext();
    }

    public IQueryable<Product> GetProducts(ProductSearchModel searchModel)
    {
        var result = Context.Products.AsQueryable();
        if (searchModel != null)
        {
            if (searchModel.Id.HasValue)
                result = result.Where(x => x.Id == searchModel.Id);
            if (!string.IsNullOrEmpty(searchModel.Name))
                result = result.Where(x => x.Name.Contains(searchModel.Name));
            if (searchModel.PriceFrom.HasValue)
                result = result.Where(x => x.Price >= searchModel.PriceFrom);
            if (searchModel.PriceTo.HasValue)
                result = result.Where(x => x.Price <= searchModel.PriceTo);
        }
        return result;     
    }
}

Then in your ProductController you can use this way:

public ActionResult Index(ProductSearchModel searchModel)
{
    var business = new ProductBusinessLogic();
    var model = business.GetProducts(searchModel);
    return View(model);
}

Important Note:

In a real world implementation, please consider implementing a suitable Dispose pattern for your business class to dispose db context when needed. For more information take a look at Implementing a Dispose method or Dispose Pattern.

Answer 2

Conditional filtering

.ToList(), .First(), .Count() and a few other methods execute the final LINQ query. But before it is executed you can apply filters just like that:

var stocks = context.Stocks.AsQueryable();
if (batchNumber != null) stocks = stocks.Where(s => s.Number = batchNumber);
if (name != null)        stocks = stocks.Where(s => s.Name.StartsWith(name));
var result = stocks.ToList(); // execute query

WhereIf LINQ Extension

Simple WhereIf can significantly simplify code:

var result = db.Stocks
    .WhereIf(batchNumber != null, s => s.Number == batchNumber)
    .WhereIf(name != null,        s => s.Name.StartsWith(name))       
    .ToList();

WhereIf implementation. It's a simple extension method for IQueryable:

public static class CollectionExtensions
{
    public static IQueryable<TSource> WhereIf<TSource>(
        this IQueryable<TSource> source,
        bool condition,
        Expression<Func<TSource, bool>> predicate)
    {
        if (condition)
            return source.Where(predicate);
        else
            return source;
    }
}

Non-WhereIf LINQ way (Recommended)

WhereIf provides more declarative way, if you don't want to use extensions you can just filter like that:

var result = context.Stocks
    .Where(batchNumber == null || stock.Number == batchNumber)
    .Where(name == null || s => s.Name.StartsWith(name))
    .ToList();

It gives an exact same effect as WhereIf and it will work faster as runtime will need to build just one ExpressionTree instead of building multiple trees and merging them.

Answer 3

I've written some extensions to make this easier. https://www.nuget.org/packages/LinqConditionalExtensions/

It's not reinventing the wheel. Some of the extensions have already been recommended. You could rewrite your logic as follows.

var results = db.Stocks
                .If(option != "0", stocks => stocks
                    .IfChain(option == "BelowMin", optionStocks => optionStocks
                        .Where(stock => stock.Qty < stock.Item.AlertQty))
                    .Else(optionStocks => optionStocks
                        .Where(stock => stock.Qty == stock.InitialQty)))
                .WhereIf(!string.IsNullOrWhiteSpace(batch), stock => stock.BatchNo == batch)
                .WhereIf(!string.IsNullOrWhiteSpace(name), stock => stock.Item.Name.StartsWith("" + name + ""))
                .ToList();

return results;

Basically, the initial If() method will apply the passed if-chain if the condition is true. The IfChain() is your nested if-else statement. IfChain() allows you to chain multiple IfElse() and end with an Else().

The WhereIf() will just conditionally apply your where clause if the condition is true.

If you are interested in the library, https://github.com/xKloc/LinqConditionalExtensions has a readme.

Answer 4

public ActionResult Index(string searchid)
{ 
var personTables = db.PersonTables.Where(o => o.Name.StartsWith(searchid) )||  o.CombanyTable.ComName.StartsWith(searchid) ).Include(k => k.CombanyTable);
return View(personTables.ToList());
}