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()
source to share
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.
source to share