CODEWITHSUNDEEP
linqentity-framework
Lighter
Lighter

Reputation: 21

Entity Framework bug with query?

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.

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

Upvotes: 2

Views: 130

Answers (1)

Daniel Hilgarth
Daniel Hilgarth

Reputation: 174447

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();

Upvotes: 3

Related Questions