CODEWITHSUNDEEP
asp.net-mvc-3entity-framework-4
Sniffer
Sniffer

Reputation: 6412

Entity Framework - combined sorting on 2 columns

I've got a query where I get a load of titles back and order them by release date:

        model.NewCollections = db.Collections
                               .Where(p => p.ReleaseDate < DateTime.Now)
                               .OrderByDescending(o => o.ReleaseDate)
                               .Take(5);

I've now added another column into my database so that I now have a paperback release date and a hardback release date (this uses the original release date).

I want a query that will get me all the hardback and paperback where the release date is after today, which I presume I can do like this:

        model.NewCollections = db.Collections
                               .Where(p => p.ReleaseDate < DateTime.Now || p.PBReleaseDate < DateTime.Now)
                               .OrderByDescending(o => o.ReleaseDate)
                               .Take(5);

But how do I order these so that they come back in the right in descending date order?

i.e. if the query brought back the following list (imagining todays date is 14th Nov):

Title 1:    HB: 13/11/11    PB: 31/12/11
Title 2:    HB: 31/12/11    PB: 31/12/11
Title 3:    HB: 11/11/11    PB: 31/12/11
Title 4:    HB: 31/12/11    PB: 31/12/11
Title 5:    HB: 31/12/11    PB: 10/11/11
Title 6:    HB: 31/12/11    PB: 12/11/11
Title 7:    HB: 31/12/11    PB: 31/12/11
Title 8:    HB: 31/12/11    PB: 31/12/11
Title 9:    HB: 31/12/11    PB: 31/12/11
Title 10:   HB: 31/12/11    PB: 09/11/11

Comes back with the following order of titles: 1, 6, 3, 5, 10

Thanks in advance

Upvotes: 1

Views: 442

Answers (1)

nemesv
nemesv

Reputation: 139808

You need to select into temporary object where you calculate the combined release data:

model.NewCollections = db.Collections
    .Where(p => p.ReleaseDate < DateTime.Now || p.PBReleaseDate < DateTime.Now)
    .Select(c => new { c.Title, Date = c.PBReleaseDate < c.ReleaseDate ? c.PBReleaseDate : c.ReleaseDate })
    .OrderByDescending(c => c.Date)
    .Take(5).ToList();

From this LINQ query EF will generate the following SQL query with the proper CASE statement:

SELECT TOP (5) 
[Project1].[C1] AS [C1], 
[Project1].[Title] AS [Title], 
[Project1].[C2] AS [C2]
FROM ( SELECT 
    [Extent1].[Title] AS [Title], 
    1 AS [C1], 
    CASE WHEN ([Extent1].[PBReleaseDate] < [Extent1].[ReleaseDate]) THEN [Extent1].[PBReleaseDate] ELSE [Extent1].[ReleaseDate] END AS [C2]
    FROM [dbo].[Collections] AS [Extent1]
    WHERE ([Extent1].[ReleaseDate] < (SysDateTime())) OR ([Extent1].[PBReleaseDate] < (SysDateTime()))
)  AS [Project1]
ORDER BY [Project1].[C2] DESC

Which is on your test data produces the expected result:

[0]: { Title = "1", Date = {11/13/2011 12:00:00 AM} }
[1]: { Title = "6", Date = {11/12/2011 12:00:00 AM} }
[2]: { Title = "3", Date = {11/11/2011 12:00:00 AM} }
[3]: { Title = "5", Date = {11/10/2011 12:00:00 AM} }
[4]: { Title = "10", Date = {11/9/2011 12:00:00 AM} }

Upvotes: 1

Related Questions