none
LINQ orderby confusion RRS feed

  • Question

  • I am having trouble getting orderby to work. In the following code, it doesn't work when it's within the LINQ statement, but the .OrderByDescending() method at the end does order it correctly. I'm not sure why.

    GridView1.DataSource = (
                         from RLK in db.RegSerialNumber
                         from RPV in db.RegProductVer
                         orderby RLK.dtCreated descending
                         where true// RLK.dtCreated <= toDate //&& fromDate <= RLK.dtCreated 
                         select new
                        { 
                             SerialNum = RLK.iSerialNumber,
                             Date = RLK.dtCreated,
                             Customer = RLK.RegCustomer.RegContact.sFirstName
                                        + " " +
                                        RLK.RegCustomer.RegContact.sLastName,
                             // Product = RPV.sProductShortDescr,    
                             Allowed = RLK.iQtyAllowed,
                             Installed = (int?)RLK.RegComputerKey.Sum(x => x.bInstalled ? 1 : 0)
                         });//.OrderByDescending(x=>x.Date).Take(200);

    Friday, April 24, 2009 11:20 PM

Answers

  • Joe,

    This is a very interesting question. Although I haven't had a chance to try your query, there is a known behavior of LINQ to Entities that might be the explanation for what you are seeing.

    In .NET 3.5 SP1, an OrderBy operation applies only to the next operation in the definition of the query, and not to the output of the query. When that next operation cares about ordering (i.e. it is a paging operation) or is just a projection, the OrderBy specified is used. Yet, if the next operation happens not to care about ordering (i.e. it is a filter operation) then the OrderBy specified is simply ignored. Since OrderBy applies only to the next operation in the definition of the query, an OrderBy that is ignored like this will never affect the output of the query.

    Consider the following query:

    Products.OrderBy(p => -p.Price).Where(p => p.Name.StartsWith(“c”)).First();

    For a technology like LINQ to Objects that generally operates over an in-memory sequence, preserving the order across these operations is trivial. For instance, filter operations over a sequence will just eat entries that do not comply with the filter’s predicate from the source sequence, whithout perturbating the ordering.

    In the query above, the output for the query in LINQ to Objects will be the most inexpensive product with a name that starts with C.

    For a LINQ queries that need to be translated to database queries, preserving the order on the output in the same fashion is many times not that trivial.

    For instance, the same query above would probably be evaluated in LINQ to Entities to return the first product that has a named starting with C, in any arbitrary order.

    In .NET 4.0 we are adding an improvement to LINQ to Entities that we call "OrderBy Lifting", which basically recognizes certain patterns in a query and transforms them in such a way that the OrderBy is lifted over other operations. This in many cases preserves OrderBys that would have otherwise been ignored.

    For the particular case of your query, although it looks straightforward in the query comprehension syntax, if you look at the expression tree representation, you will probably see that other operations are being applied after the OrderBy.

    I don’t know from the top of my head how the query would look like, so I cannot guarantee how OrderBy Lifting would handle it as you expect, but in the meanwhile, the best advice I can think of is that you continue applying the desired order as close as possible to the output of the query.

    Hope this explanation answers your question,
    Diego


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Sunday, April 26, 2009 6:08 AM
    Moderator
  • Diego is exactly correct. I had assumed that the order of terms in the linq statement didn't matter, and that it was intelligently compiled into the correct sql statement. Apparently this is an extra feature for 4.0. When I changed the order to the following, then it works as expected.
    from RPK in db.RegProductKey
    where true                        
    orderby RPK.dtCreated descending //the orderby now comes last
    select new 
    {...}
    

    • Marked as answer by joeAtBluebeam Monday, April 27, 2009 5:26 PM
    Monday, April 27, 2009 5:26 PM

All replies

  • Hi,

    The above should work, i have tried the similar query and it worked fine. Is is possible for you to give the results from both query i.e., with the order by and with the OrderByDescending.

    Thanks and regards,
    Vijay Pandurangan.
    Saturday, April 25, 2009 6:55 AM
  • Joe,

    This is a very interesting question. Although I haven't had a chance to try your query, there is a known behavior of LINQ to Entities that might be the explanation for what you are seeing.

    In .NET 3.5 SP1, an OrderBy operation applies only to the next operation in the definition of the query, and not to the output of the query. When that next operation cares about ordering (i.e. it is a paging operation) or is just a projection, the OrderBy specified is used. Yet, if the next operation happens not to care about ordering (i.e. it is a filter operation) then the OrderBy specified is simply ignored. Since OrderBy applies only to the next operation in the definition of the query, an OrderBy that is ignored like this will never affect the output of the query.

    Consider the following query:

    Products.OrderBy(p => -p.Price).Where(p => p.Name.StartsWith(“c”)).First();

    For a technology like LINQ to Objects that generally operates over an in-memory sequence, preserving the order across these operations is trivial. For instance, filter operations over a sequence will just eat entries that do not comply with the filter’s predicate from the source sequence, whithout perturbating the ordering.

    In the query above, the output for the query in LINQ to Objects will be the most inexpensive product with a name that starts with C.

    For a LINQ queries that need to be translated to database queries, preserving the order on the output in the same fashion is many times not that trivial.

    For instance, the same query above would probably be evaluated in LINQ to Entities to return the first product that has a named starting with C, in any arbitrary order.

    In .NET 4.0 we are adding an improvement to LINQ to Entities that we call "OrderBy Lifting", which basically recognizes certain patterns in a query and transforms them in such a way that the OrderBy is lifted over other operations. This in many cases preserves OrderBys that would have otherwise been ignored.

    For the particular case of your query, although it looks straightforward in the query comprehension syntax, if you look at the expression tree representation, you will probably see that other operations are being applied after the OrderBy.

    I don’t know from the top of my head how the query would look like, so I cannot guarantee how OrderBy Lifting would handle it as you expect, but in the meanwhile, the best advice I can think of is that you continue applying the desired order as close as possible to the output of the query.

    Hope this explanation answers your question,
    Diego


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Sunday, April 26, 2009 6:08 AM
    Moderator
  • Diego is exactly correct. I had assumed that the order of terms in the linq statement didn't matter, and that it was intelligently compiled into the correct sql statement. Apparently this is an extra feature for 4.0. When I changed the order to the following, then it works as expected.
    from RPK in db.RegProductKey
    where true                        
    orderby RPK.dtCreated descending //the orderby now comes last
    select new 
    {...}
    

    • Marked as answer by joeAtBluebeam Monday, April 27, 2009 5:26 PM
    Monday, April 27, 2009 5:26 PM