Order not working in Entity Framework request

I am currently struggling with a linq request for my application using Entity Framework (6.1.3)

The request looks like this:

var productPeriods = (from pp in ctx.ProductPeriods                                          
  where pp.IsActive && pp.Product.IsBuyBackForProduct == null && !pp.Product.ProductAddOns.Any() && pp.PowerRegionID == powerRegionId
  select new
  {
      ProductPeriod = pp,
      Price = pp.Prices
        .OrderByDescending(x => x.Created)
        .GroupBy(x => x.FirmID)
        .Select(pr => pr.FirstOrDefault())
        .OrderByDescending(x => x.ProductPrice)
        .FirstOrDefault()
  }).ToList();

      

The purpose of the query is to find the most recent price from the price collection of the product period, grouped by firm ID, and then select the best price from each firm's most recent prices.

This works fine in Linqpad, but the former OrderByDescending(x => x.Created)

does not work when used in the context of the Entity Framework.

Does anyone know why? And maybe there is a solution for this? :-)

Thanks in advance!

Update

Thanks for all the answers. I've tried the following:

select new {
    ProductPeriod = p,  
    Price = p.Prices.GroupBy(x => x.FirmID).Select(pr => pr.OrderByDescending(x => x.Created).ThenByDescending(x => x.ProductPrice).FirstOrDefault())
}

      

But it looks like it is ThenByDescending(x => x.ProductPrice)

also being ignored. Prices are not sorted correctly at the exit. They are displayed as follows:

Price: 0,22940, Created: 06-03-2015 10:15:09,
Price: 0,23150, Created: 06-03-2015 10:05:48
Price: 0,20040, Created: 06-03-2015 09:24:24

      

Update 2 (solution for now)

I have come to the conclusion that the initial query simply returns the latest prices from each firm. There are currently three firms, so performance should be fine.

Later in my code, where I actually use the latest and greatest price, I just do .OrderByDescending(x => x.ProductPrice).FirstOrDefault()

and check if it is not null.

i.e:

var productPeriods = (from pp in ctx.ProductPeriods
                      where pp.IsActive && pp.Product.IsBuyBackForProduct == null && !pp.Product.ProductAddOns.Any() && pp.PowerRegionID == powerRegionId
                      select new
                      {
                          ProductPeriod = pp,
                          Prices = pp.Prices.GroupBy(x => x.FirmID).Select(pr => pr.OrderByDescending(x => x.Created).FirstOrDefault())
                      }).ToList();

      

Later in my code:

var bestPriceOfToday = period.Prices.OrderByDescending(x => x.ProductPrice).FirstOrDefault()

      

0


source to share


2 answers


The problem is which commands you are using. OrderBy and OrderByDescending DO NOT add extra order by instructions to the resulting query, but instead, they CREATE the instruction in order and eliminate any orderby that existed before.

To use multiple orders, you need to do the following:



  • OrderBy or OrderByDescending
  • ThenBy or ThenByDescending

ThenBy statements can be used 1 or more times, they simply add additional statements to the resulting query.

0


source


As per your update omnit select

and enter:

select new {
    ProductPeriod = p,  
    Price = p.Prices.GroupBy(x => x.FirmID)
        .OrderByDescending(x =>x.Created).ThenByDescending(x=>x.ProductPrice).FirstOrDefault()
}

      



What select

was useless and could be causing the problem

0


source







All Articles