Linq中的合并结果 [英] Coalescing results in Linq
问题描述
浏览了SO上的许多帖子,但没有找到解决此问题的任何帖子.请注意,此处提供的所有代码均已简化,但代表了真实代码.我有一个数据表,该数据表描述了保险计划的某些属性.找回最匹配项的查询如下所示:
have looked through many posts here on SO, and haven't found any that address this. Just a note that all code presented here is simplified but representative of the real code. I have a data table that describes some properties of coverage plans. The query to bring back the best match looks something like this:
select coalesce
(
(select c.PercentOfCoverageA from CoveragePlans c
where c.coverage = :COVERAGE
and c.plancode = :PLANCODE
and c.statecode = :STATECODE),
(select c.PercentOfCoverageA from CoveragePlans c
where c.coverage = :COVERAGE
and c.plancode = :DEFAULTPLANCODE
and c.statecode = :STATECODE),
(select c.PercentOfCoverageA from CoveragePlans c
where c.coverage = :COVERAGE
and c.plancode = :DEFAULTPLANCODE
and c.statecode = :COUNTRYWIDE)
) as PercentOfCoverageA
from dual
这是一个很小的表(几十行),被击中的次数很少,并且不经常更改,因此我想将其存储到内存中,并使用Linq选择数据以加快速度.
This is a small table (a few dozen rows) that gets hit a lot and changes infrequently, so I want to bring it into memory and use Linq to select the data to speed this up.
我有此函数,该函数完全按照我的要求返回第一个匹配项:
I have this function which returns the first match exactly as I want it to:
decimal GetCoveragePercentage(string coverage, string planCode, string stateCode)
{
IEnumerable<CoveragePlan> result = Coverages
.Where(x => x.Coverage == coverage && x.PlanCode == planCode && x.StateCode == stateCode)
.Select(x => x);
if (!result.Any())
{
result = Coverages
.Where(x => x.Coverage == coverage && x.PlanCode == defaultPlanCode && x.StateCode == stateCode)
.Select(x => x);
}
if (!result.Any())
{
result = Coverages
.Where(x => x.Coverage == coverage && x.PlanCode == defaultPlanCode && x.StateCode == countryWide)
.Select(x => x);
}
return result.First().PercentOfCoverageA;
}
我的问题是:是否有更好的方法(更快,更少的代码,更少的重复)来执行此Linq查询?
My question is: Is there a better way (faster, less code, less repetition) to do this Linq query?
更新:我最终用此功能代替了
Update: I ended up with this function as a replacement:
decimal GetCoveragePercentage(string coverage, string planCode, string stateCode)
{
return Coverages.Where(x => x.Equals(coverage, planCode, stateCode))
.DefaultIfEmpty(Coverages.Where(x => x.Equals(coverage, defaultPlanCode, stateCode)).FirstOrDefault()
?? Coverages.Where(x => x.Equals(coverage, defaultPlanCode, defaultStateCode)).First())
.First().PercentOfCoverageA;
}
DefaultIfEmpty需要一个实例,而不是实例的IEnumeration.这导致我在回退子查询上添加First/FirstOrDefault,事实证明,如果为它提供null,则DefaultIfEmpty会讨厌它,因此我使用了null合并运算符来汇总回退级别.
The DefaultIfEmpty wants an instance instead of an IEnumeration of instances. This lead me to adding the First/FirstOrDefault on the fallback subqueries, and it turns out that DefaultIfEmpty hates it if you give it a null, so I used the null coalescing operater to roll up the fallback levels.
我不确定他们为什么不给您带IEnumeration的DefaultIfEmpty,就是这样:
I'm not sure why they don't give you a DefaultIfEmpty that takes an IEnumeration, it would just be this:
public static IEnumerable<TSource> DefaultIfEmpty<TSource>(this IEnumerable<TSource> source, IEnumerable<TSource> defaultValue)
{
return (source != null && source.Any()) ? source : defaultValue;
}
实际上,我想我将使用该扩展方法,并使我的功能如下:
Actually, I think I'll use that extension method, and have my function be this:
decimal GetCoveragePercentage(string coverage, string planCode, string stateCode)
{
return Coverages.Where(x => x.Equals(coverage, planCode, stateCode))
.DefaultIfEmpty(Coverages.Where(x => x.Equals(coverage, defaultPlanCode, stateCode)))
.DefaultIfEmpty(Coverages.Where(x => x.Equals(coverage, defaultPlanCode, defaultStateCode)))
.First().PercentOfCoverageA;
}
推荐答案
我相信.Select(x => x);
实际上不执行任何操作.这样就可以删除了.您可以使用并集函数加入查询.至于是否检查结果,则可以使用此功能DefaultIfEmpty()
进行调查.
I believe that .Select(x => x);
actually does nothing. So that can be removed. You can join your queries using a union function. As for the if no results checks, you could investigate using this function DefaultIfEmpty()
.
我还建议使用harsharp,以提供有关优化LINQ的建议.
I would also recommend resharper at being helpful with suggestions on optimizing LINQ.
我还认为您应该遵守DRY原则,并且不要使用以下代码行:
I also think you should abide by DRY principals and not have this line of code:
x.Coverage == coverage && x.PlanCode == defaultPlanCode && x.StateCode == stateCode
而不是将其替换为:
x.Equals(coverage,defaultPlanCode,stateCode)
我建议您的方法的linq看起来像这样(确保同时添加等于方法的优化):
I propose your linq for your method would look like this (make sure you add that equals method optimization as well as this):
decimal GetCoveragePercentage(string coverage, string planCode, string stateCode)
{
return Coverages
.Where(x => x.Coverage == coverage && x.PlanCode == planCode && x.StateCode == stateCode)
.DefaultIfEmpty(Coverages.Where(x => x.Coverage == coverage && x.PlanCode == defaultPlanCode && x.StateCode == stateCode))
.DefaultIfEmpty(Coverages.Where(x => x.Coverage == coverage && x.PlanCode == defaultPlanCode && x.StateCode == countryWide))First().PercentOfCoverageA;
}
这篇关于Linq中的合并结果的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!