locked
Linq to Entities 4.1 - Group query forces ordering RRS feed

  • Question

  • I'm developing an application using Entity Framework 4.1 Code First. The problem is when I try to group a query, it automatically adds an order by using the group key, overriding previous orderby statements. Here is the query:

     

    using (var database = new Database())
    {
      var query = from frame in database.Set<Frame>()
            orderby frame.DtStart, frame.DtStartMs
            group frame by frame.ReadoutID;
      Console.WriteLine(query.ToTraceString());
    }
    

    When I look at the generated sql (intercepting the dbquery's objectquery) I get the following:

     

    SELECT
    `Project2`.`devices_leituras_key`,
    `Project2`.`C1`,
    `Project2`.`devices_frames_key`,
    `Project2`.`devices_key`,
    `Project2`.`devices_leituras_key1`,
    `Project2`.`devices_arquivos_key`,
    `Project2`.`init_byte_arquivo`,
    `Project2`.`dt_inicio`,
    `Project2`.`dt_inicio_ms`,
    `Project2`.`dt_fim`,
    `Project2`.`dt_fim_ms`
    FROM (SELECT
    `Distinct1`.`devices_leituras_key`,
    `Extent2`.`devices_frames_key`,
    `Extent2`.`devices_key`,
    `Extent2`.`devices_leituras_key` AS `devices_leituras_key1`,
    `Extent2`.`devices_arquivos_key`,
    `Extent2`.`init_byte_arquivo`,
    `Extent2`.`dt_inicio`,
    `Extent2`.`dt_inicio_ms`,
    `Extent2`.`dt_fim`,
    `Extent2`.`dt_fim_ms`,
    CASE WHEN (`Extent2`.`devices_frames_key` IS NULL) THEN (NULL) ELSE (1) END AS `
    C1`
    FROM (SELECT DISTINCT
    `Extent1`.`devices_leituras_key`
    FROM `devices_frames` AS `Extent1`) AS `Distinct1` LEFT OUTER JOIN `devices_frames`
    AS `Extent2` ON `Distinct1`.`devices_leituras_key` = `Extent2`.`devices_leituras_key
    `) AS `Project2`
     ORDER BY
    `devices_leituras_key` ASC,
    `C1` ASC
    

    Note that there is some mapping between the Frame POCO e the table, for example the "ReadoutID" property to the "devices_leituras_key". However, the field C1 isnt present in the table.

    Questions: Why have the especified orderings gone missing (orderby frame.DtStart, frame.DtStartMs)? Why did the ReadoutID e the other strange field ordering ( ORDER BY devices_leituras_key ASC,C1 ASC) appear?

    When I remove the grouping, the original ordering aplies normally.

    Thanks in advance

    Thursday, June 9, 2011 6:06 PM

Answers

  • Hello Arthurnunesq,

    Thanks for your post.

    According to your description, I think usually we put the operator Groupby before Orderby in the LINQ query, or in the Lambda expression.

    Because Groupby will give the resultset a default order, the we use Orderby, it will override the order of the result based on our requirment.

    So how about you change the order of the two operators? Maybe it will suprise you.

    Hope this can help you.

     

    Have a nice day,


    Jackie Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Proposed as answer by Andriy Svyryd Monday, June 20, 2011 9:01 PM
    • Marked as answer by Jackie-Sun Tuesday, June 21, 2011 1:59 AM
    Monday, June 13, 2011 8:33 AM