Linq to Entities 中是否有有效的中间计算方法

发布于 2024-12-16 01:38:21 字数 2631 浏览 0 评论 0原文

我有一个在幕后使用 LinqToEntities 的查询。

(...)
.GroupBy(x => x.FahrerID)
.Select(x => new FahrerligaEintrag()
{
    FahrerID = x.Key,
    FahrerFullName = string.Empty,
    VollgasKmAufHundertKm = (100m / x.Sum(y => y.BasisMeter)) * (x.Sum(y => y.Gas100ProzentInMeter.Value) + x.Sum(y => y.Gas90ProzentInMeter.Value)),
    LeerlaufInProzent = (100m / x.Sum(y => y.BasisSekunden)) * x.Sum(y => y.LeerlaufInSekunden.Value),
    VerbrauchLiterAufHundertKm = (100m / x.Sum(y => y.BasisMeter)) * x.Sum(y => y.VerbrauchInLiter.Value) * 1000,
    RollenKmAufHundertKm = (100m / x.Sum(y => y.BasisMeter)) * x.Sum(y => y.RollenInMeter.Value),
    TempomatKmAufHundertKm = (100m / x.Sum(y => y.BasisMeter)) * x.Sum(y => y.TempomatInMeter.Value),
    GeschwindigkeitsuebertretungenAnzahlAufHundertKm = (100m / x.Sum(y => y.BasisMeter)) * 1000 * x.Sum(y => y.UebertretungenAnzahl.Value),
    GangwechselAnzahlAufHundertKm = (100m / x.Sum(y => y.BasisMeter)) * 1000 * x.Sum(y => y.GangwechselAnzahl.Value)
});

正如您所看到的,这部分重复了几次(100m / x.Sum(y => y.BasisMeter))

在 Linq to Objects 中,很自然地首先投影到匿名类中来计算因子以避免重复计算。像这样:

.GroupBy(x => x.FahrerID)
.Select(x => new
{
    Grouping = x,
    BasisMeterFaktor = 100m / x.Sum(y => y.BasisMeter),
    BasisSekundenFaktor = 100m /x.Sum(y => y.BasisSekunden)
})
.Select(x => new FahrerligaEintrag()
{
    FahrerID = x.Grouping.Key,
    FahrerFullName = string.Empty,
    VollgasKmAufHundertKm = x.BasisMeterFaktor * (x.Grouping.Sum(y => y.Gas100ProzentInMeter.Value) + x.Grouping.Sum(y => y.Gas90ProzentInMeter.Value)),
    LeerlaufInProzent = x.BasisSekundenFaktor * x.Grouping.Sum(y => y.LeerlaufInSekunden.Value),
    VerbrauchLiterAufHundertKm = x.BasisMeterFaktor * x.Grouping.Sum(y => y.VerbrauchInLiter.Value) * 1000,
    RollenKmAufHundertKm = x.BasisMeterFaktor * x.Grouping.Sum(y => y.RollenInMeter.Value),
    TempomatKmAufHundertKm = x.BasisMeterFaktor * x.Grouping.Sum(y => y.TempomatInMeter.Value),
    GeschwindigkeitsuebertretungenAnzahlAufHundertKm = x.BasisMeterFaktor * 1000 * x.Grouping.Sum(y => y.UebertretungenAnzahl.Value),
    GangwechselAnzahlAufHundertKm = x.BasisMeterFaktor * 1000 * x.Grouping.Sum(y => y.GangwechselAnzahl.Value)
});

但是,在 LinqToEntities 中,这会导致 SQL 代码性能不佳。至少对于我使用的 Oracle 后端(并且我无法对其进行分析以实际显示 SQL)。所以,我想知道是否有另一种方法可以避免重复计算,或者这是否只是我能得到的最快方法。

请原谅所有这些德语变量名。我相信你仍然明白其中的意义。

更新

我能够按照建议使用 ToTraceString() 。有趣的是,通过投影,SQL 包含 18 个(!!!)SELECT 语句。没有它,它只包含 2。

I have this query which uses LinqToEntities behind the scenes.

(...)
.GroupBy(x => x.FahrerID)
.Select(x => new FahrerligaEintrag()
{
    FahrerID = x.Key,
    FahrerFullName = string.Empty,
    VollgasKmAufHundertKm = (100m / x.Sum(y => y.BasisMeter)) * (x.Sum(y => y.Gas100ProzentInMeter.Value) + x.Sum(y => y.Gas90ProzentInMeter.Value)),
    LeerlaufInProzent = (100m / x.Sum(y => y.BasisSekunden)) * x.Sum(y => y.LeerlaufInSekunden.Value),
    VerbrauchLiterAufHundertKm = (100m / x.Sum(y => y.BasisMeter)) * x.Sum(y => y.VerbrauchInLiter.Value) * 1000,
    RollenKmAufHundertKm = (100m / x.Sum(y => y.BasisMeter)) * x.Sum(y => y.RollenInMeter.Value),
    TempomatKmAufHundertKm = (100m / x.Sum(y => y.BasisMeter)) * x.Sum(y => y.TempomatInMeter.Value),
    GeschwindigkeitsuebertretungenAnzahlAufHundertKm = (100m / x.Sum(y => y.BasisMeter)) * 1000 * x.Sum(y => y.UebertretungenAnzahl.Value),
    GangwechselAnzahlAufHundertKm = (100m / x.Sum(y => y.BasisMeter)) * 1000 * x.Sum(y => y.GangwechselAnzahl.Value)
});

As you can see this part is repeated several times (100m / x.Sum(y => y.BasisMeter)).

In Linq to Objects it feels naturally to first project into an anonymous class to calculate the factor to avoid repetitive calculations. Like this:

.GroupBy(x => x.FahrerID)
.Select(x => new
{
    Grouping = x,
    BasisMeterFaktor = 100m / x.Sum(y => y.BasisMeter),
    BasisSekundenFaktor = 100m /x.Sum(y => y.BasisSekunden)
})
.Select(x => new FahrerligaEintrag()
{
    FahrerID = x.Grouping.Key,
    FahrerFullName = string.Empty,
    VollgasKmAufHundertKm = x.BasisMeterFaktor * (x.Grouping.Sum(y => y.Gas100ProzentInMeter.Value) + x.Grouping.Sum(y => y.Gas90ProzentInMeter.Value)),
    LeerlaufInProzent = x.BasisSekundenFaktor * x.Grouping.Sum(y => y.LeerlaufInSekunden.Value),
    VerbrauchLiterAufHundertKm = x.BasisMeterFaktor * x.Grouping.Sum(y => y.VerbrauchInLiter.Value) * 1000,
    RollenKmAufHundertKm = x.BasisMeterFaktor * x.Grouping.Sum(y => y.RollenInMeter.Value),
    TempomatKmAufHundertKm = x.BasisMeterFaktor * x.Grouping.Sum(y => y.TempomatInMeter.Value),
    GeschwindigkeitsuebertretungenAnzahlAufHundertKm = x.BasisMeterFaktor * 1000 * x.Grouping.Sum(y => y.UebertretungenAnzahl.Value),
    GangwechselAnzahlAufHundertKm = x.BasisMeterFaktor * 1000 * x.Grouping.Sum(y => y.GangwechselAnzahl.Value)
});

However, in LinqToEntities this results in poor performing SQL code. At least with this Oracle backend that I use (and which I can't profile to actually show me the SQL). So, I wonder if there is another way to avoid the repetitive calculations or if this is just the fastest that I can get.

Excuse all those German variable names. I'm sure you still get the meaning.

UPDATE

I was able to use ToTraceString() as suggested. Interestingly with the projection the SQL contains 18 (!!!) SELECT statements. Without it, it contains only 2.

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(3

怪异←思 2024-12-23 01:38:21

对查询执行的 .ToTraceString() 是否提供了可供您分析的 SQL 查询?很容易迷失在所有这些计算中,但我确信,如果您想在单个查询中进行所有这些计算,性能将会受到影响。减少计算重复的另一种方法是使用 let 关键字(没有针对它的扩展方法,因此您必须使用“传统”LINQ)。这“让”您分配一个可以在查询中重复使用的变量。但我怀疑它会比你的分组方法表现得更好。

from f in Fahrer
let meterFaktor = 100m / x.Sum(y =>.BasisMeter)
select new FahrerLigaEintrag()
{
    ...
}

Does .ToTraceString() executed on the query provides a SQL query that you can profile? It's easy to get lost in all those calculations, but I'm sure, that if you want to all those calculations in a single query that the performance will suffer. Another way to decrease repetition with calculations is using the let keyword (there isn't a extension-method for it, so you have to use "traditional" LINQ). This "lets" you assign a variable that can be re-used in the query. But I doubt that it will perform any better than your grouping-approach.

from f in Fahrer
let meterFaktor = 100m / x.Sum(y =>.BasisMeter)
select new FahrerLigaEintrag()
{
    ...
}
万水千山粽是情ミ 2024-12-23 01:38:21

我想我应该在这里添加这个,而不仅仅是在 Twitter 上直接添加到 Christoph。我认为对 LINQ 的翻译要求很高。是的,可以做到,但正如他所看到的,这并不漂亮,因为 LINQ to Entities 必须使用通用算法来处理您扔给它的任何内容。如果他有可能向数据库添加存储过程或视图,我会选择这条路线。

我还建议(尽我所能,在 140 个字符内)他查看 EFProfiler (efprof.com) 或 LLBLGen 的新分析器 (llblgen.com),以分析 Oracle 中的 EF 查询。

Thought I'd add this here and not just on twitter directly to Christoph. I think that's a lot to ask of LINQ to translate. Yes it can be done, but as he can see, it's not pretty because LINQ to Entities has to use generic algorithms to handle whatever you throw at it. If he's got the possibility of adding a stored proc or view to teh database, I'd go that route instead.

And I also recommended (the best I could in 140 chars) that he check out EFProfiler (efprof.com) or LLBLGen's new profiler (llblgen.com) for profiling EF queries in Oracle.

凉城凉梦凉人心 2024-12-23 01:38:21

使用通用委托 Func 怎么样?

// declare out of query and provide valid types for x, y, result
Func<TX, TY, TResult> basisMeterFaktorAlgo;

// set formula once
basisMeterFaktorAlgo = (x, y) => 100m / x.Sum(y => y.BasisMeter);

// call it in query
basicMeterFactor = basisMeterFaktorAlgo(xValue, yValue)

What about using generic delegate Func<Tx, Ty, TResult>?

// declare out of query and provide valid types for x, y, result
Func<TX, TY, TResult> basisMeterFaktorAlgo;

// set formula once
basisMeterFaktorAlgo = (x, y) => 100m / x.Sum(y => y.BasisMeter);

// call it in query
basicMeterFactor = basisMeterFaktorAlgo(xValue, yValue)
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文