CODEWITHSUNDEEP
c#entity-frameworkef-code-firstlinq-to-entities
Masoud
Masoud

Reputation: 8211

EF does not generate expected SQL

I'm using EF 6.1.2-beta1 code first in my program and have following model in my project(my database generated by EF itself, too):

enter image description here

public class Document
{
    public int Id { get; set; }
    public AppUser Creator { get; set; }
    public AppUser Modifier { get; set; }
}
public class AppUser
{
    public int Id { get; set; }
    public string UserId { get; set; }
    public Party Party { get; set; }
}    
public class Party
{
    public int Id { get; set; }
    public string Name { get; set; }
}

I want to write a linq to entities query to find Creator.Name and Modifier.Name of first Document(for simplicity I supposed that I want to find the first Document). So I wrote following code:

var result = context.Documents.Select(d =>
             new{
                  d.Id,
                  CreatorName = d.Creator.Party.Name,
                  ModifierName = d.Modifier.Party.Name,
             }).FirstOrDefault();

EF generate following SQL for above query:

SELECT TOP (1) 
[Extent1].[Id] AS [Id], 
[Extent3].[Name] AS [Name]
FROM   [dbo].[Documents] AS [Extent1]
LEFT OUTER JOIN [dbo].[AppUser] AS [Extent2] 
             ON [Extent1].[Creator_Id] = [Extent2].[AppUserId]
LEFT OUTER JOIN [dbo].[Parties] AS [Extent3]
            ON [Extent2].[Party_Id] = [Extent3].[Id]

But, as you see, above SQL has only one join to Party table and so it get only Creator.Name.

[Updated]

You can get my test project source code from here

Dos anyone know where is the problem?

Upvotes: 0

Views: 116

Answers (3)

Dmitry
Dmitry

Reputation: 16825

As soon as downgrading to EF 6.1.1 solves this problem (generated SQL and results are correct) - I think this is bug in EF 6.1.2 beta.

For example this command

var result = context.Documents.Select(d =>
   new
   {
       d.Id,
       Creator = d.Creator,
       CreatorParty = d.Creator.Party,
       CreatorPartyName = d.Creator.Party.Name,
       Modifier = d.Modifier,
       ModifierParty = d.Modifier.Party,
       ModifierPartyName = d.Modifier.Party.Name
   }).FirstOrDefault();

Generates SQL

SELECT TOP (1) 
    [Extent1].[Id] AS [Id], 
    [Extent2].[Id] AS [Id1], 
    [Extent2].[UserId] AS [UserId], 
    [Extent2].[Party_Id] AS [Party_Id], 
    [Extent3].[Id] AS [Id2], 
    [Extent3].[Name] AS [Name], 
    [Extent4].[Id] AS [Id3], 
    [Extent4].[UserId] AS [UserId1], 
    [Extent4].[Party_Id] AS [Party_Id1]
    FROM    [dbo].[Documents] AS [Extent1]
    LEFT OUTER JOIN [dbo].[AppUsers] AS [Extent2] ON [Extent1].[Creator_Id] = [Extent2].[Id]
    LEFT OUTER JOIN [dbo].[Parties] AS [Extent3] ON [Extent2].[Party_Id] = [Extent3].[Id]
    LEFT OUTER JOIN [dbo].[AppUsers] AS [Extent4] ON [Extent1].[Modifier_Id] = [Extent4].[Id]

There is no second Parties join from Extent4 (second AppUsers)

[Update]

Thanks to Masoud (in comments) - this bug in EF had been fixed on Oct 20.

Upvotes: 1

Alaa Masoud
Alaa Masoud

Reputation: 7135

Try multiple joins instead.

var q = from d in context.Documents
        join uCreator in context.AppUsers on d.Creator.Id equals uCreator.Id
        join uModifier in context.AppUsers on d.Modifier.Id equals uModifier.Id
        select new {
          Id = d.Id,
          Creator = uCreator.Party.Name,
          Modifier = uModifier.Party.Name
        };

Upvotes: 1

Janne Matikainen
Janne Matikainen

Reputation: 5121

That query does not match the anonymous type you are giving, so I think that is just executed when you are referring to the d.Creator.Party.Name and you stop logging there? Or is there multiple queries executed?

Try following maybe?

var doc = (from d in context.Documents
            .Include("Creator.Party").Include("Modifier.Party")
            select new
            {
                Id = d.Id,
                CreatorName = d.Creator.Party.Name,
                ModifierName = d.Modifier.Party.Name
            }).FirstOrDefault();

Upvotes: 0

Related Questions