在Linq中凝聚结果[英] Coalescing results in Linq

本文是小编为大家收集整理的关于在Linq中凝聚结果的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

查看了关于 SO 的许多帖子,但没有找到任何解决此问题的帖子.请注意,此处提供的所有代码都经过简化,但代表了真实代码.我有一个描述保险计划的一些属性的数据表.带回最佳匹配的查询如下所示:

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 选择数据以加快速度.

我有这个函数可以完全按照我的意愿返回第一个匹配项:

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 查询?

更新:我最终用这个函数作为替代:

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 合并运算符来汇总后备级别.

我不知道他们为什么不给你一个 DefaultIfEmpty 接受一个 IEnumeration,它只是这样:

public static IEnumerable<TSource> DefaultIfEmpty<TSource>(this IEnumerable<TSource> source, IEnumerable<TSource> defaultValue)
{
    return (source != null && source.Any()) ? source : defaultValue;
}

其实我想我会使用那个扩展方法,我的功能是这样的:

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().

我还建议 resharper 提供有关优化 LINQ 的建议.

我也觉得你应该遵守 DRY 原则,不要有这行代码:

x.Coverage == coverage && x.PlanCode == defaultPlanCode && x.StateCode == stateCode

而不是用类似的东西替换它:

x.Equals(coverage,defaultPlanCode,stateCode)

我建议你的方法的 linq 看起来像这样(确保你添加了等于方法优化以及这个):

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;

}

本文地址:https://www.itbaoku.cn/post/1556893.html

问题描述

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

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;
}

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;
}

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.

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;
}

推荐答案

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().

I would also recommend resharper at being helpful with suggestions on optimizing LINQ.

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

instead replacing it with something like:

x.Equals(coverage,defaultPlanCode,stateCode)

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;

}
相关标签/搜索