Is there an efficient way of intermediate computing in Linq for Entities

Question

I have this query that uses LinqToEntities backstage.

(...) .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 (100 m /x.Sum(y => y.BasisMeter)) .

In Linq to Objects, of course, you first need to run the project in an anonymous class to calculate the coefficient in order to avoid repeated 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 leads to poor execution of the SQL code. At least with this Oracle backend, which I use (and which I cannot profile to actually show me SQL). So, I am wondering if there is another way to avoid repetitive calculations or if it is only the fastest I can get.

Sorry all the German variable names. I am sure you still understand the point.

UPDATE

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

Answer 1

Thought I'd post it here, and not just twitter directly to Christophe. I think a lot to ask LINQ to translate. Yes, it can be done, but as he sees it, it is not, because LINQ to Entities must use general algorithms to process what you throw on it. If he had the opportunity to add a saved proc or view to the database, I would choose this route.

And I also recommended (140 characters best) to check it with EFProfiler (efprof.com) or the new LLBLGen profiler (llblgen.com) to profile EF requests in Oracle.

Answer 2

Is .ToTraceString() in a query with an SQL query that you can profile? It's easy to get lost in all these calculations, but I'm sure if you want all these calculations in one query to suffer from performance. Another way to reduce repetition through computation is to use the let keyword (there is no extension method for it, so you need to use the "traditional" LINQ). This "allows" to assign a variable that can be reused in the request. But I doubt that it will work better than your group approach.

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

Answer 3

How to use shared 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)