Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

How to make Entity Framework only filter data when query fields are not null?

I have an mvc .net core application where the user is displayed some data, and can filter that data based on some input that he/her gives.
If no filters/constraints are given, then the while set of entities should jsut be returned.

I found this example, and found the second answer to be quite neat, with regards to what I want to do.

So I added this thing at the bottom of my controller:

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

public static class QueryableEx
{
    public static IQueryable<T> Where<T>(
        this IQueryable<T> @this,
        bool condition,
        Expression<Func<T, bool>> @where)
    {
        return condition ? @this.Where(@where) : @this;
    }
}

And then made this controller action which filters by one of three possible inputs:

[HttpPost]
public IActionResult query(QueryModel query)
{
    List<CustomerModel> customers = new List<CustomerModel>();

    var db = new DemoContext();
    customers = db.Customers 
        .Where(query.Name != null, x => x.Name == query.Name)
        .Where(query.Surname != null, x => x.Surname == query.Surname)
        .Where(query.Age != null, x => x.Age == query.Age)
            .ToList(); 
    
    
    return View("Index", customers);
}

This works like a charm, If I input a certain name, then I only get the results with that name and vice versa.

There is an issue though. If all of the input fields are null, then everything is filtered out. I want the opposite to happen, if no filters have been entered, then just return everything.

How do I do this? Making sure that no filtering happens if all the input fields are empty?

EDIT

By request, I here is the model I use for queries

public class QueryModel
{
    public string Name {get;set; }
    
    public string Surname { get; set; }
    
    public uint Age { get; set; }
    
    
}

And here is the customer one:

public class CustomerModel
{
        public int Id{get;set; }
        [Required]
        public string Name {get;set; }
        [Required]
        public string Surname { get; set; }
        [Required]
        [Range(18,110)]
        public uint Age { get; set; }
        [Required]
        public virtual AdressModel Adress { get; set; }
        [Required]
        public  DateTime Created { get; set; }
        [Required]
        public virtual List<PurchaseModel> purchases { get; set; }
}

>Solution :

Your model parameters are not nullable, so I suspect that you’ll end up looking for customers with Age equal Zero, hence no results.

Try:

customers = db.Customers 
    .Where(query.Name != default, x => x.Name == query.Name)
    .Where(query.Surname != default, x => x.Surname == query.Surname)
    .Where(query.Age != default, x => x.Age == query.Age)
        .ToList(); 

Change ‘null’ to ‘default’ in each case.

Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading