Ordering not working in Entity Framework query

Question

I'm currently battling a linq query for my application using Entity Framework (6.1.3)

The query is as follows:

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 latest price from the prices collection of a product period, grouped by the firm ID and then select the best price of the latest prices from each firm.

This works perfectly in Linqpad, but the first OrderByDescending(x => x.Created) doesn't work when used in context of Entity Framework.

Does anyone knows why? And perhaps have a solution for it? :-)

Thanks in advance!

Update

Thanks for all replies. 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 seems like ThenByDescending(x => x.ProductPrice) gets ignored as well. The prices are not sorted correctly in the output. They're output like this:

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 came to the solution that the initial query just returns the latest prices from each firm. There's currently three firms, so the performance should be alright.

Later in my code, where I'm actually using the latest and best price, I simply do an .OrderByDescending(x => x.ProductPrice).FirstOrDefault() and check if it's 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()

ordering and then grouping won't preserve the order, especially if you're re-ordering. look into ThenByDescending() to do a second ordering — DLeh
– DLeh, Commented Apr 29, 2015 at 13:16
No exception being thrown, but the results are not correct :-) I'll definitely look into the ThenByDescending call. Thanks a lot! On a side note, I just read that this is a bug in the entity framework, can anyone confirm this? — Bo Mortensen
– Bo Mortensen, Commented Apr 29, 2015 at 13:18
Yes, "Created" is a DateTime and "ProductPrice" is a decimal :-) — Bo Mortensen
– Bo Mortensen, Commented Apr 30, 2015 at 6:57

Thomas · Accepted Answer · 2015-04-29 13:19:54Z

0

The problem is the commands you are using. OrderBy and OrderByDescending do NOT add additional order by statements to the resulting query but instead they CREATE the order by statement and eliminate all orderby statements that existed before.

In order to use multiple orderby's you need to do the following:

OrderBy or OrderByDescending
ThenBy or ThenByDescending

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

answered Apr 29, 2015 at 13:19

Thomas

3,0144 gold badges41 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Kind Contributor Over a year ago

And you would need to move the Created orderby to be just above the ProductPrice one (with thenby). An order by before a group is not clear - in SQL you put orderbys after groupings. It might still work but it's not obvious that you need to use thenby, unless they're juxtaposed.

Thomas Over a year ago

the group by COULD be used before an orderby (didnt try so far) in linq BECAUSE the statement itself is only run when it is needed, thus in this case when the ToList() is being made. until then the query is not executed thus it should not be a problem (but like I said didn't try it out if the order of groupby/orderby is important before the ToList or not)

michalczukm · Accepted Answer · 2015-04-29 14:33:48Z

0

According to yours update, omnit select and type:

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

That select was useless and could be the cause of problem

answered Apr 29, 2015 at 14:33

michalczukm

10.5k6 gold badges42 silver badges47 bronze badges

2 Comments

Bo Mortensen Over a year ago

Hi @michasm, thanks for the input :-) I don't think the select was useless since I start by grouping the prices by the firm ID. If I skip the select, I can't order by Created since the collection is now a grouping and not a collection of price. Makes sense? :-)

michalczukm Over a year ago

mmm, right @BoMortensen it's in group, now it's seems legit to me :)

Collectives™ on Stack Overflow

Ordering not working in Entity Framework query

2 Answers 2

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related