Filtering data with multiple filter values

Question

Hello fellow stackoverflowers,

I'm currently working on a project which gives me a bit of trouble concerning filtering data from a database by using multiple filter values. The filter happens after selecting the filters and by clicking a button.

I have 5 filters: Region, Company, Price, and 2 boolean values
Note that Region and Company are special dropdownlist with checkboxes which means the user can select one or more regions and company names.

I already made a few tests and came up with a incomplete code which works a bit but not to my liking.

Problems arise when one of my filters is NULL or empty. I don't really know how to process this. The only way i thought of was using a bunch of IF ELSE statements, but i'm starting to think that this will never end since there are so much possibilities...

I'm sure there is a far more easier way of doing this without using a bunch of IF ELSE statements, but i don't really know how to do it. If anyone could steer me in the right direction that would be appreciated. Thanks

Here is what i have right now (I haven't added the Price to the query for now):

protected void filterRepeater(List<int> regionIDs, string[] companyArray,
    string blocFiltValue, bool bMutFunds, bool bFinancing)
{
    DatabaseEntities db = new DatabaseEntities();
    PagedDataSource pagedDsource = new PagedDataSource();

    IQueryable<Blocs> query = (from q in db.Blocs
                               where q.isActive == true
                               orderby q.date descending
                               select q);
    IQueryable<Blocs> queryResult = null;

    //if some filters are NULL or Empty, it create a null queryResult
    queryResult = query.Where(p => companyArray.Contains(p.company) &&
                                   regionIDs.Contains((int)p.fkRegionID) &&
                                   (bool)p.mutual_funds == bMutFunds &&
                                   (bool)p.financing == bFinancing);

    if (queryResult.Count() > 0)
    {
        //Bind new data to repeater
        pagedDsource.DataSource = queryResult.ToArray();
        blocRepeater.DataSource = pagedDsource;
        blocRepeater.DataBind();
    }
}

What are you expecting to happen if filters are null or empty? — Olivier Jacot-Descombes, Jun 16 '14 at 19:59

score 2 · Answer 1 · answered Jun 16 '14 at 20:06

Only add the relevant filters to query:

IQueryable<Blocs> query =
    from q in db.Blocs
    where q.isActive == true
    orderby q.date descending
    select q;

if (companyArray != null)
{
    query = query.Where(p => companyArray.Contains(p.company));
}

if (regionIDs != null)
{
     query = query.Where(p => regionIDs.Contains((int)p.fkRegionID));
}

// ...
// etc
// ...

if (query.Any()) // Any() is more efficient than Count()
{
    //Bind new data to repeater
    pagedDsource.DataSource = query.ToArray();
    blocRepeater.DataSource = pagedDsource;
    blocRepeater.DataBind();
}

Thank you qujck for taking the time to help me. I accepted Olivier's answer, but in the end, both of your answers are exact. — IndieRok, Jun 16 '14 at 20:42

score 1 · Accepted Answer · edited Jun 16 '14 at 22:05

1

If you want to filter only by the filter values that are not null or empty then you can construct the query by appending the where clauses one by one:

if(companyArray != null && companyArray.Length > 0) {
    query = query.Where(p => companyArray.Contains(p.company));
}
if(regionIDs!= null && regionIDs.Length > 0) {
    query = query.Where(p => regionIDs.Contains((int)p.fkRegionID));
}
if (!String.IsNullOrEmpty(blocFiltValue)) {
    query = query.Where(p => p.Block == blocFiltValue);
}

Also you can use nullable values for value types, if you need to filter them optionally

bool? bMutFunds = ...; // Possible values: null, false, true.

...

if(bMutFunds.HasValue) {
    query = query.Where(p => (bool)p.mutual_funds == bMutFunds.Value); 
}

edited Jun 16 '14 at 22:05

IndieRok

1,778
1
14
21

answered Jun 16 '14 at 20:09

Olivier Jacot-Descombes

104,806
13
138
188

Ok, i see, by adding the where clauses to the query only if values are not null then the query will produce only the relevant set of data. Gonna test that right now – IndieRok Jun 16 '14 at 20:13
That did the trick rather quite well, if i can say so myself! Thank you for your help Olivier. – IndieRok Jun 16 '14 at 20:38

score 0 · Answer 3 · answered Jun 16 '14 at 20:00

0

Maybe you can create a string for the SQL sentence, and dynamically add parts to this sentence like if something was selected or checked you add something to this string when thh selection was completed by the user you can execute this SQL sentence.

answered Jun 16 '14 at 20:00

serginhofogo

53
1
10

"a string for the SQL sentence": Check this link and the accepted answer. Do you mean like that?: http://stackoverflow.com/questions/17329713/dynamic-linq-to-entities-how-to-build-query-based-on-variables – IndieRok Jun 16 '14 at 20:04

Filtering data with multiple filter values

3 Answers3