Linq to Entities 4.1 - Group query forces ordering

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

score 1 · Answer 1 · answered Jun 08 '11 at 22:12

1

LINQ-to-SQL and LINQ-to-Entities use remote model. Sequences in query implement the IQueryable and after the compilation resolve into query operators from the Queryable class, known as expression trees. Expression trees are interpreted by the runtime.

Change the order in your query and you shall be fine:

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

    Console.WriteLine(query.ToTraceString());
}

answered Jun 08 '11 at 22:12

oleksii

33,544
11
83
149

1

After the groupby statement the 'frame' is no longer in scope; if I want to add a orderby after the group, I need to add an 'into' to continue modifying the query, and the target of the orderby will be now an IGroupable, allowing me to order the generated groups, not the frames. – Arthur Nunes Jun 09 '11 at 16:45
@Arthur yeah, you are right, my bet. Can you try multiple order by via `ThenBy`, like [here](http://stackoverflow.com/questions/298725/linq-multiple-order-by) – oleksii Jun 09 '11 at 17:37
I tried making the query whitout using the query language sintax: `var query = database.Set().OrderBy(f => f.DtStart).ThenBy(f => f.DtStartMs).GroupBy(f => f.ReadoutID);` But i got the same result... – Arthur Nunes Jun 09 '11 at 17:50

Linq to Entities 4.1 - Group query forces ordering

1 Answers1