Entity Framework Code First One-To-Many doing wrong queries?

Question

So I have a model

public class Player { public int PlayerId { get; set; } [Required] public string Name2 { get; set; } public string Cv { get; set; } public int TeamId { get; set; } public virtual Team Team { get; set; } } public class Team { public int TeamId { get; set; } [Required] public string Name { get; set; } public string City { get; set; } public DateTime Founded { get; set; } public virtual IList<Player> Players { get; set; } }

My Teams table in DB contains records, my Players table in DB does not contain any (empty).

When I run this query:

 IQueryable<Player> query = playerRepository.All.Include(p => p.Team); return View((query);

I get this query in DB (via profiler):

SELECT [Project1].[TeamId] AS [TeamId], [Project1].[Name] AS [Name], [Project1].[City] AS [City], [Project1].[Founded] AS [Founded], [Project1].[C1] AS [C1], [Project1].[PlayerId] AS [PlayerId], [Project1].[Name2] AS [Name2], [Project1].[Cv] AS [Cv], [Project1].[TeamId1] AS [TeamId1] FROM ( SELECT [Limit1].[TeamId] AS [TeamId], [Limit1].[Name] AS [Name], [Limit1].[City] AS [City], [Limit1].[Founded] AS [Founded], [Extent2].[PlayerId] AS [PlayerId], [Extent2].[Name2] AS [Name2], [Extent2].[Cv] AS [Cv], [Extent2].[TeamId] AS [TeamId1], CASE WHEN ([Extent2].[PlayerId] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1] FROM ( SELECT TOP (10) [c].[TeamId] AS [TeamId], [c].[Name] AS [Name], [c].[City] AS [City], [c].[Founded] AS [Founded] /* HERE */ FROM [dbo].[Teams] AS [c] ) AS [Limit1] /* AND HERE */ LEFT OUTER JOIN [dbo].[Players] AS [Extent2] ON [Limit1].[TeamId] = [Extent2].[TeamId] ) AS [Project1] ORDER BY [Project1].[TeamId] ASC, [Project1].[C1] ASC

Which as a result I get one empty row shown on the screen. This is because this joins are done in wrong order...instead of joining Teams on Players, I get Players on Teams...which in turn means that even though I have NO players in the DB, I get an empty row in the grid.

Anyone have any ideas why???

Vladan

Where did the TOP(10) come from? What is hidden behind your repository? — Ladislav Mrnka
– Ladislav Mrnka, Commented May 5, 2011 at 8:51
no logic...here is the method: public IQueryable<Player> All { get { return context.Players; } } — Vladan Strigo
– Vladan Strigo, Commented May 5, 2011 at 8:54

Ladislav Mrnka · Accepted Answer · 2011-05-05 21:36:33Z

You must be doing something wrong or you are looking at wrong query. I used your entities and your linq query:

var data = context.Players.Include(p => p.Team).ToList();

and I get this SQL query:

SELECT [Extent1].[PlayerId] AS [PlayerId], [Extent1].[Name2] AS [Name2], [Extent1].[Cv] AS [Cv], [Extent1].[TeamId] AS [TeamId], [Extent2].[TeamId] AS [TeamId1], [Extent2].[Name] AS [Name], [Extent2].[City] AS [City], [Extent2].[Founded] AS [Founded] FROM [dbo].[Players] AS [Extent1] INNER JOIN [dbo].[Teams] AS [Extent2] ON [Extent1].[TeamId] = [Extent2].[TeamId]

But if I use this query:

var data = context.Teams.Include(t => t.Players).Take(10).ToList();

I will get exactly your SQL query. TOP (10) and reverse table querying will not appear without reason.

Collectives™ on Stack Overflow

Entity Framework Code First One-To-Many doing wrong queries?

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related