All geek questions in one place

How to optimize this LINQ query for Visual Studio?

I have one giant complex LINQ to SQL query that I need to optimize somehow, because the background C # compiler starts the CPU completely, and I cannot type or edit my file .csusually in Visual Studio 2010 (each letter, especially if IntelliSense wants to pop up, it goes awfully).

This culprit:

var custFVC = 
    (from cfvc in customer.CustomerFrameVariationCategories
    let lastValue = cfvc.CustomerFrameVariationCategoryValueChanges.Where(cfvcvc => cfvcvc.ChangeDateTime <= this.SelectedDateTime).OrderByDescending(cfvcvc2 => cfvcvc2.ChangeDateTime).FirstOrDefault() ?? new CustomerFrameVariationCategoryValueChange()
    let lastValue2 = cfvc.FrameVariationCategory.FrameVariation.Frame.FrameValueChanges.Where(fvc => fvc.ChangeDateTime <= this.SelectedDateTime).OrderByDescending(fvc2 => fvc2.ChangeDateTime).FirstOrDefault() ?? new FrameValueChange()
    let lastValue3 = cfvc.FrameVariationCategory.FrameVariationCategoryValueChanges.Where(fvcvc => fvcvc.ChangeDateTime <= this.SelectedDateTime).OrderByDescending(fvcvc2 => fvcvc2.ChangeDateTime).FirstOrDefault() ?? new FrameVariationCategoryValueChange()
    let lastValue4 = cfvc.FrameVariationCategory.FrameVariation.FrameVariationModules.Any(fvm => (fvm.FrameModule.FrameModuleValueChanges.Where(fmvc => fmvc.ChangeDateTime <= this.SelectedDateTime).OrderByDescending(fmvc2 => fmvc2.ChangeDateTime).FirstOrDefault() ?? new FrameModuleValueChange()).IsActive == false)
    where lastValue.IsActive == true
    orderby cfvc.FrameVariationCategory.FrameVariation.Frame.Name, cfvc.FrameVariationCategory.Category.Name, cfvc.FrameVariationCategory.FrameVariation.Name
    select new
    {
       cfvc.Id,
       cfvc.FrameVariationCategory,
       lastValue.CoverCoefficient,
       lastValue.NeiserNet,
       PlywoodName = lastValue2.Plywood.Name,
       FrameIsActive = lastValue2.IsActive,
       OwnCost = cfvc.FrameVariationCategory.FrameVariation.FrameVariationModules.Sum(fvm => // sum all frame variation modules
          (lastValue4 ? 0 : fvm.FrameModule.FrameModuleValueChanges.Where(fmvc => fmvc.ChangeDateTime <= this.SelectedDateTime) // if module not active then 0
          .OrderByDescending(fmvc2 => fmvc2.ChangeDateTime).FirstOrDefault().Porolone) + // otherwise get Porolone
           fvm.FrameModule.FrameModuleComponents.Sum(fmc => // add to Porolone sum of all module components
           (fmc.Article.ArticleDetails.Any() ? fmc.Article.ArticleDetails.Sum(ad => // if any article details then use A*L*W*T instead of Amount
           WindowExcel.MultiplyArticleDetailValues(ad.ArticleDetailValueChanges.Where(advc => advc.ChangeDateTime <= this.SelectedDateTime)
          .OrderByDescending(advc2 => advc2.ChangeDateTime).FirstOrDefault() ?? new ArticleDetailValueChange())) : 
           WindowExcel.GetModuleComponentAmount(fmc.FrameModuleComponentValueChanges.Where(fmcvc => fmcvc.ChangeDateTime <= this.SelectedDateTime) // no details = get amount
             .OrderByDescending(fmcvc2 => fmcvc2.ChangeDateTime).FirstOrDefault() ?? new FrameModuleComponentValueChange())) * // times article values
           WindowExcel.MultiplyArticleValues(fmc.Article.ArticleValueChanges.Where(avc => avc.ChangeDateTime <= this.SelectedDateTime)
           .OrderByDescending(avc2 => avc2.ChangeDateTime).FirstOrDefault() ?? new ArticleValueChange()))),
       Cubes = cfvc.FrameVariationCategory.FrameVariation.FrameVariationModules.Sum(fvm => (fvm.FrameModule.FrameModuleValueChanges.Where(fmvc => fmvc.ChangeDateTime <= this.SelectedDateTime && fmvc.IsActive == true).OrderByDescending(fmvc2 => fmvc2.ChangeDateTime).FirstOrDefault() ?? new FrameModuleValueChange()).Cubes),
       lastValue3.CoverNet,
       lastValue3.CoverGarbage,
       lastValue3.CoverGross,
       lastValue3.CoverPrice,
       lastValue3.BackgroundNet,
       lastValue3.BackgroundGarbage,
       lastValue3.BackgroundGross,
       lastValue3.BackgroundPrice,
       FVCIsActive = lastValue3.IsActive,
       FrameModuleAnyNonActive = lastValue4
    }).ToList();

- OwnCost, , Visual Studio . (, ), . /, LINQ DataContext ( ), , using).

, , - - , LINQ - . , , , , , ...

( ) OwnCost .cs , , ( ) "-",...

+3
7

, LINQ to SQL, . ; , , .

, OwnCost ,

OwnCost = cfvc.Select(CalculateOwnCost)

, , WindowExcel, , , , Excel , , ?

Edit

OwnCost , - :

public decimal CalculateOwnCost(CustomerFrameVariationCategory cvfc)
{
   return cfvc.FrameVariationCategory.FrameVariation.FrameVariationModules.Sum(fvm => // sum all frame variation modules
                 (lastValue4 ? 0 : fvm.FrameModule.FrameModuleValueChanges.Where(fmvc => fmvc.ChangeDateTime <= this.SelectedDateTime) // if module not active then 0
                 .OrderByDescending(fmvc2 => fmvc2.ChangeDateTime).FirstOrDefault().Porolone) + // otherwise get Porolone
                  fvm.FrameModule.FrameModuleComponents.Sum(fmc => // add to Porolone sum of all module components
                  (fmc.Article.ArticleDetails.Any() ? fmc.Article.ArticleDetails.Sum(ad => // if any article details then use A*L*W*T instead of Amount
                  WindowExcel.MultiplyArticleDetailValues(ad.ArticleDetailValueChanges.Where(advc => advc.ChangeDateTime <= this.SelectedDateTime)
                 .OrderByDescending(advc2 => advc2.ChangeDateTime).FirstOrDefault() ?? new ArticleDetailValueChange())) : 
                  WindowExcel.GetModuleComponentAmount(fmc.FrameModuleComponentValueChanges.Where(fmcvc => fmcvc.ChangeDateTime <= this.SelectedDateTime) // no details = get amount
                 .OrderByDescending(fmcvc2 => fmcvc2.ChangeDateTime).FirstOrDefault() ?? new FrameModuleComponentValueChange())) * // times article values
                  WindowExcel.MultiplyArticleValues(fmc.Article.ArticleValueChanges.Where(avc => avc.ChangeDateTime <= this.SelectedDateTime)
                  .OrderByDescending(avc2 => avc2.ChangeDateTime).FirstOrDefault() ?? new ArticleValueChange()))),
              Cubes = cfvc.FrameVariationCategory.FrameVariation.FrameVariationModules.Sum(fvm => (fvm.FrameModule.FrameModuleValueChanges.Where(fmvc => fmvc.ChangeDateTime <= this.SelectedDateTime && fmvc.IsActive == true).OrderByDescending(fmvc2 => fmvc2.ChangeDateTime).FirstOrDefault() ?? new FrameModuleValueChange()).Cubes)
}

, CustomerFrameVariationCategories CustomerFrameVariationCategory OwnCost decimal.

, Select, ,

OwnCost = cfvc.Select(x => CalculateOwnCost(x))

(, , , , ).

, . - - .

+5

, #. , , ,

  • let
  • OwnCost select

, , - , , , .

+1

. VS . , SELECT LINQ-to-object. . :

var custFVC = (from cfvc in customer.CustomerFrameVariationCategories
               let lastValue = cfvc.CustomerFrameVariationCategoryValueChanges.Where(cfvcvc => cfvcvc.ChangeDateTime <= this.SelectedDateTime).OrderByDescending(cfvcvc2 => cfvcvc2.ChangeDateTime).FirstOrDefault() ?? new CustomerFrameVariationCategoryValueChange()
               let lastValue2 = cfvc.FrameVariationCategory.FrameVariation.Frame.FrameValueChanges.Where(fvc => fvc.ChangeDateTime <= this.SelectedDateTime).OrderByDescending(fvc2 => fvc2.ChangeDateTime).FirstOrDefault() ?? new FrameValueChange()
               let lastValue3 = cfvc.FrameVariationCategory.FrameVariationCategoryValueChanges.Where(fvcvc => fvcvc.ChangeDateTime <= this.SelectedDateTime).OrderByDescending(fvcvc2 => fvcvc2.ChangeDateTime).FirstOrDefault() ?? new FrameVariationCategoryValueChange()
               let lastValue4 = cfvc.FrameVariationCategory.FrameVariation.FrameVariationModules.Any(fvm => (fvm.FrameModule.FrameModuleValueChanges.Where(fmvc => fmvc.ChangeDateTime <= this.SelectedDateTime).OrderByDescending(fmvc2 => fmvc2.ChangeDateTime).FirstOrDefault() ?? new FrameModuleValueChange()).IsActive == false)
               where lastValue.IsActive == true
               orderby cfvc.FrameVariationCategory.FrameVariation.Frame.Name, cfvc.FrameVariationCategory.Category.Name, cfvc.FrameVariationCategory.FrameVariation.Name
               select new
               { cfvc, lastValue, lastValue1, lastValue2, lastValue3}).ToList();

. , , , . , .

, db, , , , , db.

, , , IQuerable, . ( ), , . , .ToList() . LINQ-to-object IEnumerable .

+1

VS, , , , .

OwnCost LINQ- cfvc lastValue4, , OwnCost LINQ. lastValue4 , LINQ, OwnCost .ToList() . cfvc, , , .FrameVariationCategory, .

CustFVC OwnCost , , , . .ToList() . .

, , . foreach OwnCost , - , LINQ .

LINQ , - , - LINQ , . ToList() . foreach . LINQ LINQ , , - .

+1

, , , (, , ) ?

+1

, LINQ to SQL, , .

0

, :)

LINQ googling. , , , . , , , .cs:

public partial class WindowExcel
{
    private static decimal GetOwnCost(CustomerFrameVariationCategory cfvc, bool frameModuleAnyNonActive, DateTime selectedDateTime)
    {
        return cfvc.FrameVariationCategory.FrameVariation.FrameVariationModules.Sum(fvm => // sum all frame variation modules
            (frameModuleAnyNonActive ? 0 : fvm.FrameModule.FrameModuleValueChanges.Where(fmvc => fmvc.ChangeDateTime <= selectedDateTime) // if module not active then 0
            .OrderByDescending(fmvc2 => fmvc2.ChangeDateTime).FirstOrDefault().Porolone) + // otherwise get Porolone
            fvm.FrameModule.FrameModuleComponents.Sum(fmc => // add to Porolone sum of all module components
                (fmc.Article.ArticleDetails.Any() ? fmc.Article.ArticleDetails.Sum(ad => // if any article details then use A*L*W*T instead of Amount
                    WindowExcel.MultiplyArticleDetailValues(ad.ArticleDetailValueChanges.Where(advc => advc.ChangeDateTime <= selectedDateTime)
                    .OrderByDescending(advc2 => advc2.ChangeDateTime).FirstOrDefault() ?? new ArticleDetailValueChange())) :
                    WindowExcel.GetModuleComponentAmount(fmc.FrameModuleComponentValueChanges.Where(fmcvc => fmcvc.ChangeDateTime <= selectedDateTime) // no details = get amount
                    .OrderByDescending(fmcvc2 => fmcvc2.ChangeDateTime).FirstOrDefault() ?? new FrameModuleComponentValueChange())) * // times article values
                    WindowExcel.MultiplyArticleValues(fmc.Article.ArticleValueChanges.Where(avc => avc.ChangeDateTime <= selectedDateTime)
                    .OrderByDescending(avc2 => avc2.ChangeDateTime).FirstOrDefault() ?? new ArticleValueChange())));
    }
}

LINQ OwnCost :

OwnCost = WindowExcel.GetOwnCost(cfvc, lastValue4, this.SelectedDateTime)

GetOwnCost - , , . , . , CustomerFrameVariationCategory OwnCost , LINQ, . , - , .

, . , ( ), . , , .

, - .

PS! Internet Explorer 8 - (- ). VS....

Edit:

, , . , , , ...

:)

0

Source: https://habr.com/ru/post/1771190/

More articles:


All Articles