代码之家 › 专栏 › 技术社区 › Eitan Seri-Levi

多个过滤选项的查询结构和语法

linq-to-entities linq entity-framework c#

Eitan Seri-Levi · 技术社区 · 7 年前

我有一个非常大的数据集。为了优化查询性能,我根据用户选择的过滤器进行查询。

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

1 回复 | 直到 7 年前

nbokmans 7 年前

您可以用“查询选项”定义一个自定义类,您可以用您想要的任何参数填充该类。它看起来像这样:

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

然后可以这样使用(我不确定您的项目是什么类型的 BM_OPT_MASTER 所以我就这么做了):

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

你只要打电话给 AddItems

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

或

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

要添加新的查询过滤器选项,只需向 QueryFilterOptions 类并添加 FilterOn... 方法