优化LINQ查询，检查结果是否有值
本文关键字：结果是 是否 结果 检查 LINQ 查询 优化 | 更新日期: 2023-09-27 18:09:47
我有LINQ查询检查查询结果并结束语句。数据库大约有60万数据。下面是LINQ查询和if语句。
var feetypelist = from feetype in dbDataContext.tbl_fee_types
orderby feetype.seq
select feetype.id;
foreach (var fty in feetypelist)
{
var checkFeeCharges = from fee in dbDataContext.tbl_fee_schedules
where fee.arr == arr && fee.dep == dep && fee.feetypeid == fty && fee.site_id == siteId
orderby fee.seq
select fee;
if (checkFeeCharges.Count() == 0)
{
checkFeeCharges = from fee in dbDataContext.tbl_fee_schedules
where fee.arr == arr && fee.dep == dep && fee.feetypeid == fty && fee.site_id == siteId
&& fee.country == country
orderby fee.seq
select fee;
}
if (checkFeeCharges.Count() == 0)
{
checkFeeCharges = from fee in dbDataContext.tbl_fee_schedules
where fee.site_id == siteId && fee.country == country &&
((fee.arr == arr && fee.dep == dep) || (fee.arr.Equals("DOM") && fee.dep.Equals("DOM")) ||
(fee.arr.Equals("*") && fee.dep == dep) || (fee.arr == arr && fee.dep.Equals("*")))
&& fee.feetypeid == fty
orderby fee.seq
select fee;
if (checkFeeCharges.Count() == 0)
{
var country_route = from route in dbDataContext.tbl_country_routes
where route.origin_airport_cd == arr && route.destination_airport_cd == dep
select route;
if (country_route.Count() >= 1)
{
foreach (var c in country_route)
{
if (c.air_asiax == 'Y')
{
//con_flight = "Y";
checkFeeCharges = from fee in dbDataContext.tbl_fee_schedules
where fee.site_id == siteId && fee.country == country &&
((fee.arr == "AAX" && fee.dep == "AAX") || (fee.arr == "*" && fee.dep == dep) ||
(fee.arr == arr && fee.dep == "*")) && fee.feetypeid == fty
orderby fee.seq
select fee;
if (checkFeeCharges.Count() == 0)
{
checkFeeCharges = from fee in dbDataContext.tbl_fee_schedules
where fee.site_id == siteId && fee.country == country &&
((fee.arr == arr && fee.dep == dep) || (fee.arr == "INT" && fee.dep == "INT") ||
(fee.arr == "*" && fee.dep == dep) || (fee.arr == arr && fee.dep == "*") ||
(fee.arr == country && fee.dep == country)) &&
fee.feetypeid == fty
orderby fee.seq
select fee;
}
}
else
{
checkFeeCharges = from fee in dbDataContext.tbl_fee_schedules
where fee.site_id == siteId && fee.country == country &&
((fee.arr == arr && fee.dep == dep) || (fee.arr == "INT" && fee.dep == "INT") ||
(fee.arr == "*" && fee.dep == dep) || (fee.arr == arr && fee.dep == "*") ||
(fee.arr == country && fee.dep == country)) &&
fee.feetypeid == fty
orderby fee.seq
select fee;
}
}
}
else
{
checkFeeCharges = from fee in dbDataContext.tbl_fee_schedules
where fee.site_id == siteId && fee.country == country &&
((fee.arr == arr && fee.dep == dep) || (fee.arr == "INT" && fee.dep == "INT") ||
(fee.arr == "*" && fee.dep == dep) || (fee.arr == arr && fee.dep == "*")) &&
fee.feetypeid == fty
orderby fee.seq
select fee;
}
}
}
if (checkFeeCharges.Count() == 0)
{
checkFeeCharges = from fee in dbDataContext.tbl_fee_schedules
where fee.site_id == siteId && fee.country == country &&
((fee.arr == arr && fee.dep == dep) || (fee.arr == "INT" && fee.dep == "INT") ||
(fee.arr == "*" && fee.dep == dep) || (fee.arr == arr && fee.dep == "*")) &&
fee.feetypeid == fty
orderby fee.seq
select fee;
}
查询执行需要一段时间，知道如何优化这个查询吗
返回json请求
if (checkFeeCharges.Any())
{
List<FeeAndChargesModel_Fee_Schedule> feeTable = new List<FeeAndChargesModel_Fee_Schedule>();
foreach (var p in checkFeeCharges)
{
feeTable.Add(
new FeeAndChargesModel_Fee_Schedule()
{
arr = FlightScheduleAPIController.GetCountryName(arr, siteId),
dep = FlightScheduleAPIController.GetCountryName(dep, siteId),
country = p.country,
feedesc = p.feedesc,
feetype = p.feetype,
currency = p.currency,
value = p.value,
remark = p.remark
}
);
label = p.feetype;
}
mainJson.Add(
new FeeAndChargesModel_Main
{
label = label,
con_flight = FlightScheduleAPIController.GetCountryName(GetConnectingFlight(dep, arr), siteId),
details = feeTable
}
);
}
}
return mainJson;
好吧，这段代码真的很难读，但让我们给你一些建议。
我注意到的第一件事是第一个sql语句被执行
var checkFeeCharges = from fee in dbDataContext.tbl_fee_schedules
where fee.arr == arr && fee.dep == dep && fee.feetypeid == fty && fee.site_id == siteId
orderby fee.seq
select fee;
如果没有返回值
if (checkFeeCharges.Count() == 0)
{
执行一个比第一个查询更具体的sql查询。它有所有相同的AND语句+另一个。如果第一个查询不返回任何结果，那么期望(所有东西都是常量)应该是第二个查询不返回任何结果。如果是这样，为什么要这样做呢?
checkFeeCharges = from fee in dbDataContext.tbl_fee_schedules
where fee.arr == arr && fee.dep == dep && fee.feetypeid == fty && fee.site_id == siteId
&& fee.country == country
orderby fee.seq
select fee;
}
就我个人而言，我会尝试理解或解决以下问题
1预期的结果集大小是多少，我是否可以检索"少量"记录并在内存中处理这些记录。我需要了解结果集是否会随着时间的推移而增长，以及它的最小、最大类型边界。
2是否有一个更好的数据库结构，我可以重构代码使用，这将允许这个查询更容易和更有效。
3 为了真正能够优化这个查询，我将尝试首先直接用sql编写这个查询，并使用explain来帮助理解sql数据库正在做什么，正在使用哪些索引以及每个查询返回的结果集的大小。
。我还认为，你应该能够将许多查询组合到一个表达式中。
这可能不完全正确，但应该可以作为一个演示
from fee in dbDataContext.tbl_fee_schedules
where (fee.arr == arr && fee.dep == dep && fee.feetypeid == fty && fee.site_id == siteId
&& fee.country == country) || (fee.site_id == siteId && fee.country == country &&
((fee.arr == "AAX" && fee.dep == "AAX") || (fee.arr == "*" && fee.dep == dep) ||
(fee.arr == arr && fee.dep == "*")) && fee.feetypeid == fty)
|| ( ..... ) | ( .... )
从我可以看到，在那里你将需要几个连接或子选择，这就是为什么在sql中尝试它将是最快的。此外，还有许多linq2sql/ORM分析工具可能能够帮助进行诊断。
很难说如何优化，因为这里有很多重复。我想从减少这个开始。
一个明显需要改变的地方是:
if (checkFeeCharges.Count() == 0)
在每次调用查询时枚举整个查询。一种更便宜的检查方法:
if (!checkFeeCharges.Any())
这将只从查询中枚举一个结果来确定它是否为空。