CodeGo.net>在DBML中使用LINQ的多个联接表中的多个搜索参数

我想建立一个高级的多参数搜索网站.
这是我的DBML和ORM模式：

在此高级多参数搜索中,用户可以搜索具有多个参数(例如大小,楼层,城市,价格等)的房地产.
这是我编写的处理此部分的功能.

private DataTable linq_search_by_details()
{
myDBMLDataContext ctx = new myDBMLDataContext(address);
var query = ctx.Estates.AsQueryable();
query = query.Where(c => c.eshId == int.Parse(ddlEshape.SelectedValue.ToString()));
query = query.Where(c => c.cityId == int.Parse(ddlcity.SelectedValue.ToString()));
query = query.Where(c => c.ETId == int.Parse(ddlType.SelectedValue.ToString()));
query = query.Where(c => c.dealingId == int.Parse(ddldeal.SelectedValue.ToString()));
query = query.Where(c => c.deedId == int.Parse(ddldeed.SelectedValue.ToString()));
if (!string.IsNullOrEmpty(txtPrepaymentFrom.Value.Trim()))
{
query = query.Where(c => int.Parse(c.prepayment) <= int.Parse(txtPrepaymentFrom.Value));
}
if (!string.IsNullOrEmpty(txtPrepaymentTo.Value.Trim()))
{
query = query.Where(c => int.Parse(c.prepayment) >= int.Parse(txtPrepaymentTo.Value));
}
if (!string.IsNullOrEmpty(txtPrepaymentFrom.Value.Trim()) && !string.IsNullOrEmpty(txtPrepaymentTo.Value.Trim()))
{
query = query.Where(c => int.Parse(c.prepayment) <= int.Parse(txtPrepaymentFrom.Value) && int.Parse(c.prepayment) >= int.Parse(txtPrepaymentTo.Value));
}
if (!string.IsNullOrEmpty(txtPriceFrom.Value.Trim()))
{
query = query.Where(c => int.Parse(c.price) <= int.Parse(txtPriceFrom.Value));
}
if (!string.IsNullOrEmpty(txtPriceTo.Value.Trim()))
{
query = query.Where(c => int.Parse(c.price) >= int.Parse(txtPriceTo.Value));
}
if (!string.IsNullOrEmpty(txtPriceFrom.Value.Trim()) && !string.IsNullOrEmpty(txtPriceTo.Value.Trim()))
{
query = query.Where(c => int.Parse(c.price) <= int.Parse(txtPriceFrom.Value) && int.Parse(c.price) >= int.Parse(txtPriceTo.Value));
}
if (!string.IsNullOrEmpty(txtFloor.Value.Trim()))
{
query = query.Where(c => c.eFloor == short.Parse(txtFloor.Value));
}
if (chbExchange.Checked)
{
query = query.Where(c => c.exchange == true);
}
var final = query.Select(c => new { c.esId,c.owId, c.City.cityName, c.EstateShape.eshName, c.EstateType.ETName, c.owner.owFname, c.owner.owLname, c.esSize, c.prepayment, c.price });
return Special.LINQResultToDataTable(final.ToList());
}


该功能可以正常工作,但是现在我想更多地从EstateEquipment和EstateFacility中添加一些参数.
正如您在ORM中看到的那样,房地产与房地产设备(也包括房地产与房地产设施)之间的关系是一对多的.
 现在,我想让用户可以搜索cityId = 1的Estate,其大小约为400m,例如从EstateEquipment获得eqId = 1和2,然后从EstateFacility搜索到fId = 1和2.
这就是我试图处理最后一部分的方式.

foreach (ListItem item in cblEquipment.Items)
{
if (item.Selected)
{
eq = true;
}
}
if(eq)
{
var eqQuery = ctx.EstateEquipments.AsQueryable();
foreach (ListItem item in cblEquipment.Items)
{
if (item.Selected)
{
eqQuery = eqQuery.Where(c => c.eqId == int.Parse(item.Value.ToString()));
}
}
var eqFinal = eqQuery.Select(c => new { c.Estate.esId, c.Estate.owner.owId, c.Estate.City.cityName, c.Estate.EstateShape.eshName, c.Estate.EstateType.ETName, c.Estate.owner.owFname, c.Estate.owner.owLname, c.Estate.esSize, c.Estate.prepayment, c.Estate.price });
DataTable dtEq = Special.LINQResultToDataTable(eqFinal.ToList());
if(dtEq.Rows.Count>0)
{
this.Build_search(dtEq);
}
else
{
msg = "No record found";
sysMsg.Attributes["class"] = "";
sysMsg.Attributes["class"] = "alert alert-warning";
}
}


在这里,首先,我检查从列表中选择了哪种设备.然后根据选择的项目扩展查询.但是我不知道如何将这个新结果与最后结果甚至EstateFacility结果结合起来.
谢谢你的帮助.

解决方法:

由于您的案例比较复杂,因此无法测试我的解决方案,我希望它至少可以为您指明正确的方向.

我会说尝试这样的事情：

if (cblEquipment.Items.Any(item => item.Selected))
{
var selectedEquipmentIds = cblEquipment.Items.Where(item => item.Selected).Select(item => int.Parse(item.Value.ToString()));
query = query.Where(c => ctx.EstateEquipments.Any(eq => eq.esId == c.esId && selectedEquipmentIds.Contains(eq.eqId)));
}


这会在var final …之前的第一个代码段中出现….然后将不使用第二个代码段,因此您在那儿的错误处理也需要移动…