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());
}

Yeah just wrote query for each of those three options. But I didn't write for multiple options as I thought this isn't the good approach. — Avishekh Bharati
– Avishekh Bharati, Commented Oct 15, 2015 at 16:40
@Avi-B I recommend using a SearchModel in such cases, Let me know if you are interested in this approach and I'll post an answer for you. — Reza Aghaei
– Reza Aghaei, Commented Oct 15, 2015 at 16:53
You could use a predicate but your best bet in this situation is to use an expression tree to dynamically create the search query as the form elements are filled in. — Stephen Brickner
– Stephen Brickner, Commented Oct 15, 2015 at 17:38
Use !string.IsNullOrWhiteSpace(batch) rather than batch == "" — user3794166
– user3794166, Commented Oct 15, 2015 at 17:43

Reza Aghaei · Accepted Answer · 2019-09-30 15:13:38Z

92

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.

edited Sep 30, 2019 at 15:13

answered Oct 15, 2015 at 17:12

Reza Aghaei

126k18 gold badges239 silver badges447 bronze badges

Sign up to request clarification or add additional context in comments.

35 Comments

KayGee Over a year ago

I am using your idea and it works fine when i search, but when i try to sort my data the search criteria is lost. my action looks like this

public ActionResult Index(string sortOrder, SearchTransacModel searchModel, SearchTransacModel currentFilter, int? page)         {}

I am passing the search model to the view like this in order to keep my search parameters ` ViewBag.currentFilter = searchModel;`

Reza Aghaei Over a year ago

The result of GetProducts method is IQueryable<Product> and you can simply apply sorting and paging to the result. It's enough to pass suitable sort column name, sort order and page number to the action and use them.

Reza Aghaei Over a year ago

@KayGee It's better to ask a new question inspired by this post and let the community help you. Also if you notify me, surely I also will take a look at your question :)

Reza Aghaei Over a year ago

ModelBinder looks to parameter of action and since it sees the action has a parameter of type ProductSearchModel, creates an instance of it. Then looks into querystring or form parameters, check if there is a value posted for Id property (case-insensitive) then initializes the Id property of the object, the same for Name property and the other properties. For more information, search about Model binding in ASP.NET MVC and read a few articles like this one.

MD TAREQ HASSAN Over a year ago

An elegant solution: codereview.stackexchange.com/a/5506/167333

|

chindirala sampath kumar · Accepted Answer · 2020-02-25 14:16:31Z

18

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.

edited Feb 25, 2020 at 14:16

chindirala sampath kumar

6518 silver badges18 bronze badges

answered Oct 15, 2015 at 16:39

Andrei

44.9k39 gold badges163 silver badges228 bronze badges

5 Comments

Avishekh Bharati Over a year ago

Well, I am really new to asp.net mvc and learning by doing. I will take a look int expressions and see if that works for me. Thanks.

jefissu Over a year ago

Nice approach using WhereIf

jefissu Over a year ago

Using it in an BaseController i needed to add .AsQueryable at the return source.Where(predicate).AsQueryable();

johannespartin Over a year ago

The Non-WhereIf LINQ way, which you do recommend isn't that great! The problem is, that the null check is generated in the SQL query, if you use it onto a database.

chindirala sampath kumar Over a year ago

The predicate should be Expression<Func<TSource, bool>> rather than Func<TSource, bool> in the extension method.

Todd Skelton · Accepted Answer · 2019-01-10 18:45:45Z

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.

ana · Accepted Answer · 2018-04-04 11:10:04Z

0

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());
}

edited Apr 4, 2018 at 11:10

answered Apr 4, 2018 at 11:00

ana

11 bronze badge

1 Comment

sg7 Over a year ago

Add the description of the solution.

Collectives™ on Stack Overflow

Filter/Search using Multiple Fields - ASP.NET MVC

4 Answers 4

35 Comments

Conditional filtering

WhereIf LINQ Extension

Non-WhereIf LINQ way (Recommended)

5 Comments

Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

35 Comments

Conditional filtering

WhereIf LINQ Extension

Non-WhereIf LINQ way (Recommended)

5 Comments

Comments

1 Comment

Linked

Related