使用Linq动态添加条件连接 [英] Add Conditional Join Dynamically with Linq

问题描述

我有一个基本的搜索控件，该控件根据下拉列表提供的预定义搜索/过滤条件从CRM中列出公司.每个DropDown的默认选择是"ALL"，否则用户选择一个特定的项目.我希望能够根据选择动态地构建Linq查询.在这5个选择器中，它们提供了我可以与Company表匹配的值，但是其中两个选择器(如果选择了一个或两个)将需要一个或多个联接，否则基本结果集将不再执行任何操作.我希望这是有道理的.

I have a basic search control which lists companies from a CRM depending on predefined search/filtering criteria supplied by dropdowns. The default selection is "ALL" for each DropDown, otherwise the user chooses a specific item(s). I'd like to be able to construct a Linq query dynamically based on the selections. Out of the 5 selectors they supply values that I can match against the Company table, but two of the selectors (if either or both are chosen) would require a join or joins, else no action should be taken again the base result set. I hope this makes sense.

我不确定如何有效地做到这一点.这是我的代码:

I'm not sure how to do this effectively. Here is my code:

private void Search()
{
    EnergyPubsCRMDataContext dc = new EnergyPubsCRMDataContext();

    var results = (from c in dc.Companies
                   select c);


    //only create the join if the selected index > 0
    if (ddlIndustry.SelectedIndex > 0)
    {
        //A company can be in 1 or more industries, thus here I want to join
        //with the CompanyIndustry table and have a WHERE clause to match on the ddlIndustry.SelectedValue
    }

    //only create the join if the selected index > 0
    if (ddlServices.SelectedIndex > 0)
    {
        //A company can offer 1 or more services. Here I want to join to the CompanyService table
        //on the CompanyID and have a WHERE clause to match the ddlServices.SelectedValue
    }        

    //These work OK to shape the overal query further (they don't need joins)
    if (ddlCountry.SelectedIndex > 0)
        results = results.Where(c => c.CountryID == Convert.ToInt32(ddlCountry.SelectedValue));

    if (ddlStateRegion.SelectedIndex > 0)
        results = results.Where(c => c.StateRegionID == Convert.ToInt32(ddlStateRegion.SelectedValue));

    if (ddlAccountManagers.SelectedIndex > 0)
    {
        Guid g = new Guid(ddlAccountManagers.SelectedValue);
        results = results.Where(c => c.UserId == g);
    }

    results = results.OrderBy(c => c.CompanyName);

    //Bind to Grid....        
}

推荐答案

我遇到了非常相似的问题，没有可以利用的外键. 我的解决方案会翻译成这样:

I had very similar issue and no foreign keys I could leverage. My solution would translate to something like this:

results = results
  .Join(dc.CompanyIndustry, c => c.CompanyID, ci => ci.CompanyID, (c, ci) => new { c, ci.IndustryID })
  .Where (a => a.IndustryID == ddlIndustry.SelectedValue)
  .Select(a => a.c);

基本上:

1)首先，我们创建一个连接，并为其提供一个行业ID(连接)的投影

1) first we create a join, with a projection that gives us IndustryID (join)

2)我们根据IndustryID(其中)进行过滤

2) we filter based on IndustryID (where)

3)我们返回原始匿名类型，以便我们可以修改原始查询(选择)

3) we return original anonymous type, so that we can modify original query (select)

这篇关于使用Linq动态添加条件连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！