Entity Framework bug with query?

Question

I have an array with this data (ID, Time, and Name):

var array = new[]
{
    new { ID = 1, Time = DateTime.ParseExact("12:01", "HH:mm", null), Name = "Peter" },
    new { ID = 2, Time = DateTime.ParseExact("12:06", "HH:mm", null), Name = "James" }, 
    new { ID = 3, Time = DateTime.ParseExact("12:03", "HH:mm", null), Name = "Jackie" },
    new { ID = 4, Time = DateTime.ParseExact("12:08", "HH:mm", null), Name = "Peter" }, 
    new { ID = 5, Time = DateTime.ParseExact("12:05", "HH:mm", null), Name = "James" },
    new { ID = 6, Time = DateTime.ParseExact("12:07", "HH:mm", null), Name = "Peter" },
};

The following statement on the array produces the correct result:

var result = array.OrderBy(x => x.Time).GroupBy(x => x.Name)
                  .SelectMany(x => x).ToArray();

The result:

Time: 2013/3/6 12:01:00, Name: Peter
Time: 2013/3/6 12:07:00, Name: Peter
Time: 2013/3/6 12:08:00, Name: Peter
Time: 2013/3/6 12:03:00, Name: Jackie
Time: 2013/3/6 12:05:00, Name: James
Time: 2013/3/6 12:06:00, Name: James

But when I use the same statement with EF and SQL Server, the order is wrong:

Time: 2013/3/6 12:03:00, Name: Jackie
Time: 2013/3/6 12:06:00, Name: James
Time: 2013/3/6 12:05:00, Name: James
Time: 2013/3/6 12:07:00, Name: Peter
Time: 2013/3/6 12:01:00, Name: Peter
Time: 2013/3/6 12:08:00, Name: Peter

Here is the SQL EF generates:

SELECT 
[Extent2].[Id] AS [Id], 
[Extent2].[Time] AS [Time], 
[Extent2].[Name] AS [Name]
FROM   (SELECT DISTINCT 
    [Extent1].[Name] AS [Name]
    FROM [dbo].[testt1] AS [Extent1] ) AS [Distinct1]
INNER JOIN [dbo].[testt1] AS [Extent2]
ON ([Distinct1].[Name] = [Extent2].[Name]) OR 
   (([Distinct1].[Name] IS NULL) AND ([Extent2].[Name] IS NULL))

There is no order by clause.

What did I forget? Is this a bug of EF?
How to get the same result from EF as from the array?

Just had to say: This is a very well asked question. You have code to reproduce (at least in linq to objects), expected output, actual output, generated SQL, and even good formatting. Keep it up! — Kobi
– Kobi, Commented Mar 6, 2013 at 7:19

Daniel Hilgarth · Accepted Answer · 2013-03-06 07:09:19Z

3

Just like in SQL:

First group, then order:

var result = array.GroupBy(x => x.Name)
                  .Select(x => x.OrderBy(y => y.Time))
                  .SelectMany(x => x)
                  .ToArray();

But in your case I don't see why you would need the group at all. Every time seems to be different, so a simpler version that yields the same result (at least with your test data) would be:

var result = array.OrderBy(x => x.Name)
                  .ThenBy(y => y.Time)
                  .ToArray();

answered Mar 6, 2013 at 7:09

Daniel Hilgarth

175k41 gold badges346 silver badges450 bronze badges

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

Collectives™ on Stack Overflow

Entity Framework bug with query?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related