Query structure and syntax for multiple filtering options

Question

I have a very large data set. In order to optimize query performance I'm making queries based on which filters a user selected.

using (_db)
{
    if (string.IsNullOrEmpty(CompanyID))
    {
        if (string.IsNullOrEmpty(HealthplanCode))
        {
            foreach (string x in _db.BM_OPT_MASTER.Select(y => y.OPT).Distinct())
            {
                currentComboBox.Items.Add(x);
            }
        }
        else
        {
            foreach (string x in _db.BM_OPT_MASTER.Where(y => y.HPCODE == HealthplanCode).Select(y => y.OPT).Distinct())
            {
                currentComboBox.Items.Add(x);
            }

        }
    }
    else
    {
        if (string.IsNullOrEmpty(HealthplanCode))
        {
            foreach (string x in _db.BM_OPT_MASTER.Where(y => y.COMPANY_ID == CompanyID).Select(y => y.OPT).Distinct())
            {
                currentComboBox.Items.Add(x);
            }

        }
        else
        {
            foreach (string x in _db.BM_OPT_MASTER.Where(y => y.COMPANY_ID == CompanyID && y.HPCODE == HealthplanCode).Select(y => y.OPT).Distinct())
            {
                currentComboBox.Items.Add(x);
            }
        }
    }
}

As you can see this can get pretty annoying as more and more filter options are added. Is there a way to refactor this code in such a manner that the query is still optimized without relying on nested if else statements?

Answer 1

You could define a custom class with "query options" that you could fill out with whatever parameters you want. It would look something like this:

public class QueryFilterOptions 
{
    public string CompanyID { get; set; }
    public string HealthplanCode { get; set; }
    public string SomeOtherQueryOption { get; set; }
}

This can then be used as such (I was unsure what type your items in BM_OPT_MASTER are so I just took that):

public void AddItems(QueryFilterOptions options = null) {
    using (_db)
    {
        if (options == null) {
            options = new QueryFilterOptions();
        }

        var items = _db.BM_OPT_MASTER; 

        items = FilterOnCompanyID(items, options.CompanyID);
        items = FilterOnHealthPlanCode(items, options.HealthplanCode);
        items = FilterOnSomeOtherQueryOption(items, options.SomeOtherQueryOption);
        //...other filters

        items = items.Select(y => y.OPT).Distinct();

        foreach (var item in items) 
        {
            currentComboBox.Items.Add(item);
        }
    }
}

private IQueryable<BM_OPT_MASTER> FilterOnCompanyID(IQueryable<BM_OPT_MASTER> items, string companyID)
{
    if (!(string.IsNullOrEmpty(companyID)))
    {
        items = items.Where(y => y.COMPANY_ID == companyID);
    }

    return items;
}

private IQueryable<BM_OPT_MASTER> FilterOnHealthPlanCode(IQueryable<BM_OPT_MASTER> items, string healthplanCode)
{
    if (!(string.IsNullOrEmpty(healthplanCode)))
    {
        items = items.Where(y => y.HPCODE == healthplanCode);
    }

    return items;
}

private IQueryable<BM_OPT_MASTER> FilterOnSomeOtherQueryOption(IQueryable<BM_OPT_MASTER> items, string someOtherQueryOption)
{
    if (!(string.IsNullOrEmpty(someOtherQueryOption)))
    {
        items = items.Where(y => y.SOME_OTHER_QUERY_OPTION == someOtherQueryOption);
    }

    return items;       
}

You would just call the AddItems functions with whatever filter values you have, for example:

AddItems(new QueryFilterOptions 
{
    CompanyID = "SOME-COMPANY-ID"
});

or

AddItems(new QueryFilterOptions 
{
    HealthplanCode = "SOME-HEALTHPLAN-CODE",
    SomeOtherQueryOption = "SOME-OTHER-QUERY-OPTION"
});

To add new query filter options, you just add a new field to the QueryFilterOptions class and add a FilterOn... method.