Geek Answers Handbook

Linq to Entity skips OrderBy with SelectMany

I encounter a rather strange behavior using Linq to Entity (code first).

All my entities, context, databases are working fine, this is the current project that has been developed, updated and online for two years. I am using .NET 4.5, EF 5. For this particular request, LazyLoading is disabled (does not activate anything).

I have the following tables (I only mention what is related to the problem):

  • Games in which games are stored
  • Click Where Games Press Articles Are Stored
  • PressTypes, which contains the categories of press articles.
  • Press_Games, which defines one for many between Press to Games (one article can be about many games)

About tables and their corresponding object:

  • There are many fields in games, but let's say that it contains only two: ID (guid) and Name, the name of the game.
  • The press also contains many fields, including: ID (guid), CategoryID (guid) and UpdateDate (DateTime), which represent the last time an article was updated.
  • Entity Press has a Category object and IEnumerable Games.
  • Game Entity does NOT have Press navigation features (intended and necessary)
  • PressTypes is a list of press article categories and has an identifier (guid)
  • Press_Games has two fields: GameID (guid) and PressID (guid)

I need to get a list of 5 games related to the latest press articles of a certain category. I get them through the following request:

Context.Press
    .Where(press => press.Category.ID == MagicValues.ReviewGuid)) // MagicValues.ReviewGuid returns a Guid
    .OrderByDescending(press => press.UpdateDate)
    .SelectMany(press => press.Games)
    .Take(5);

, , . LINQPad, , :

SELECT TOP (5) 
[Join1].[ID] AS [ID], 
[Join1].[Name] AS [Name], 
FROM  [dbo].[Press] AS [Extent1]
INNER JOIN  (SELECT [Extent2].[PressID] AS [PressID], [Extent3].[ID] AS [ID] /* lots of selected fields */
    FROM  [dbo].[Press_Games] AS [Extent2]
    INNER JOIN [dbo].[Games] AS [Extent3] ON [Extent3].[ID] = [Extent2].[GameID] ) AS [Join1] ON [Extent1].[ID] = [Join1].[PressID]
WHERE cast('b5c18183-14e2-4bf2-b4e1-641b56694c55' as uniqueidentifier) = [Extent1].[CategoryID]

ORDER BY. , :

Context.Press
    .Include(press => press.Games)
    .Where(press => press.Category.ID == MagicValues.ReviewGuid)) // MagicValues.ReviewGuid returns a Guid
    .OrderByDescending(press => press.UpdateDate)
    .Take(25);

SQL :

SELECT 
[Project2].[C1] AS [C1], 
[Project2].[ID] AS [ID], 
[Project2].[Name] AS [Name], 
FROM ( 
    /* Lots of irrelevant stuff with JOINs and SELECTs */
)  AS [Project2]
ORDER BY [Project2].[UpdateDate] DESC, [Project2].[ID] ASC, [Project2].[C2] ASC

ORDER BY ( , ).

() : ?

, , SelectMany, EF, , , , , OrderBy on Press. , .

( ), .

+4
1

EF, . , EF smartness .

1

, , .

-, barebone...

from p in Context.Press
from g in p.Games
select g

Context.Press.SelectMany(press => press.Games). , , Press Game - , Press_Games. Game Press ( ).

EF , , , , , barebone- Games Press_Games. Press SQL.

...

from p in Context.Press
from g in p.Games
where p.Category.ID == guid
select g

... , Press . SQL- Press, . , Press.CategoryID SQL-, Category .

, EF SQL-. , -, : no Press, Press .

barebone- ( , )...

from p in Context.Press
orderby p.UpdateDate
from g in p.Games
select g

orderby ! , SQL .

, EF "", Games - from g in p.Games - , , - , , , .

...

from p in Context.Press
from g in p.Games
orderby p.UpdateDate
select g

... . , EF, , , .

, LINQ SelectMany, . , .

-, orderby ( , ), , , .

2

Press - , , , , . , Press Games , 1.

+3

More articles:


All Articles