我有一个基本的搜索控件,它根据下拉列表提供的预定义搜索/过滤条件列出 CRM 中的公司。每个下拉菜单的默认选择是“全部”,否则用户将选择特定的项目。我希望能够根据选择动态构建 Linq 查询。在 5 个选择器中,它们提供了我可以与 Company 表进行匹配的值,但其中两个选择器(如果选择其中一个或两个)将需要一个或多个联接,否则不应对基本结果集再次采取任何操作。我希望这是有道理的。
我不知道如何有效地做到这一点。这是我的代码:
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....
}
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
results = results.Where(c => c.CompanyIndustry.IndustryID == ddlIndustry.SelectedValue);
}
假设您的数据库/DBML 中有正确的外键。
这将隐式生成连接。
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)