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

Prescript

Have you tried any code already? – Dammar

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. – Prescript

@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. – Claypoole

@Reza Well, I really have no idea about SearchModel. If you could post answer that would be really great. – Prescript

@RezaAghaei great :) – Prescript

Let us continue this discussion in chat. – Prescript

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. – Leid

Use !string.IsNullOrWhiteSpace(batch) rather than batch == "" – Broughton

@Hani great!! I didn't realize that.. :) – Prescript

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.

Claypoole

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;` – Doenitz

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. – Claypoole

When rendering your paging links and your column headers which are responsible for sorting, you should add suitable query string to links to act correctly. In my projects, it's the responsibility of my Grid helper/component. – Claypoole

But when I click sort: @Html.ActionLink("Reference No", "Index", new { sortOrder = ViewBag.RefNoSortParm, currentFilter = ViewBag.CurrentFilter }) currentFilter is null in the controller. I have checked and ViewBag.currentFilter does have the data in the view but for some reason it is not going back to the Controller – Doenitz

@Doenitz Because it probably generates a link like /?sortorder=acs&curretFilter=SomeNameSpace.SearchTransacModel. You cannot mix search model values and sortorder this way. – Claypoole

@Doenitz 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 :) – Claypoole

thanx I'll do that :) – Doenitz

@RezaAghaei i have a quick question on this. On the controller, all the search fields(on the view) are mapped to SearchModel. Suppose i need to return search results thats not coming from one table(in the above example its coming from one mode Products) but multiple tables/models, is it better for me to create a view in the db and then add that to the entity model and then use that as a view model ? – Brendabrendan

I guess i can create a new ViewModel thats combination of multiple models and return that? – Brendabrendan

@Brendabrendan You don't need to create a View in db when you use Entity Framework. Just use EF and shape the result of query to your custom ViewModel. It's an example and I kept it simple. You can return a custom view model, also you can include paging and sorting like this. – Claypoole

Yes, thats what i am leaning on. I do have another quick question. I have a top screen where i have all the search fields (mapped to searchmodel) and on the bottom i want to have a search results which is coming from a different view model. I cant define search model and view model in my main view. I am confused on how to structure this. – Brendabrendan

@Brendabrendan You can create a single ViewModel including a property for SearchModel and a property of List<DataModel> as the search result. The approach which I used myself was having using a partial view for search panel and a partial view for search result and using ajax. So I didn't need to mix those models in a single view model, but you can mix them with no problem. – Claypoole

@RezaAghaei on your second approach, how would you pass the search fields (searchmodel) to the partial view in DataModel? – Brendabrendan

@Brendabrendan I used the ViewBag. For next times when you click on submit button of search partial view, I send an ajax request and the result (Index partial view) will be returned and replaced previous results. My search partial view is at top of page and I didn't need to render it again. But I use the search model which I need it in Index Partial view. For example take a look at this post. – Claypoole

@Brendabrendan Using ViewBag is somehow ugly, but it's simple to use. You can use ViewBag, also you can go with first option which is creating a Custom ViewModel containing search model and search results. In our solution because of some reasons we tried to use data models as much as possible instead of view models but there is no need to avoid view models, indeed it's recommended to use view models. – Claypoole

Thanks @RezaAghaei for answering my questions and giving good feedback. I will try the viewmodel method first and see how it goes. – Brendabrendan

@Brendabrendan By the way, if you find this post or the linked post useful, it would be great if you vote for question and answers to make them more popular and useful for future readers. It's not compulsory at all :) – Claypoole

The Logic works perfectly fine untill some one refreshes the page and the moment you refresh it, all the data vanishes away. – Brophy

@AbdulHannan persisting the search/sort state is your job. For example you can simply pass searchModel to View using ViewData, then you can use it in Html.ActionLink or Html.BeginForm methods to include it in QueryString for links or forms. This way, when you navigate, refresh or re-post the form you will see the search model will be passed to your action. For example take a look at this post. – Claypoole

Hi, in the view binding/typing should I have the SearchModel as the strong type or the ProdctViewModel?. More importantly, when the results from the query are sent back they're a list of product and how do you iterate them to insert them in a table if you dont have a strongly typed ProductViewModel inside the view. can you please add the view code here so we know how to populate the view – Blackmarket

Hi @transformer In the above example, the model used for the view is Product and the ProductSearchModel is passed to view (to be added to sort columns, page numbers and show in search fields to persist the search model between requests) using ViewData. – Claypoole

@Reza Aghaei - this is nice but how is your controller taking in the object searchModel how are you constructing it? In addition how does your controller pass this object in as when the page loads you simply are on the ui level no object created yet??? – Intermix

@Intermix This is ASP.NET MVC. When a request like /product/index?name=x&pricefrom=100 comes in, Index action of the Product controller will handle the request and the model binder will create an instance of ProductSearchModel having Name=x and PriceFrom=10 will be created and passed as parameter to the Index action. – Claypoole

Do the parameter names have to match the class property names. How or when does it know to use the for instance ProductSearchModel and when not to use this object? – Intermix

@Intermix It's the way that ASP.NET MVC model binding works. It maps the url parameters or form parameters to action parameters. So if the url is /product/index the instance of ProductSearchModel which will be created contains null for all fields and you see the search logic ignores null values. – Claypoole

But how does it know because the name ProductSearchModel? – Intermix

What i am asking is how does mvc know that just because you have a link called product/index does it know to bind to the productsearchmodel object? – Intermix

@Intermix So I suggest you read Model Binding in ASP.NET Core :) – Claypoole

Reza i understand the route from controller to action but not binding to an object. That link doesnt give info on thstt – Intermix

@Intermix When an action has a parameter of type Class1, model binder will always create an instance of Class1 and if there are post/querystring parameters with the same name as properties of Class1, those properties will be initialized with values of those parameters. – Claypoole

Let us continue this discussion in chat. – Intermix

But where does class1 come into play. For instance your URL is: products/index?name=x&priceFrom=100 correct? How does mvc know that this translates to an object of type ProductSearchModel? Is it just ecause the parameter names are the exact same as the properties of this object ??? If so does it matter that the property name is case different ? Meaning the url parameter say is name, but your class 's property is public string Name {get; set; } does that matter ? – Intermix

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. – Claypoole

Exactly what i was looking for and you explained it well now. Thanks! – Intermix

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

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.

Clisthenes

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. – Prescript

Nice approach using WhereIf – Soapbark

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

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. – Goering

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

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.

Dirk

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

Flinger

Add the description of the solution. – Nikolos