Entity Framework left join on multiple tables creates inner join instead

c# entity-framework entity-framework-6 linq

Question

Only one of the four tables in the query I'm trying to build will be guaranteed to exist. Creating left joins in EF has always worked for me, but never with more than one join. This is the problem:

   var details = (from planInfo in context.PlanInfo
                 join templateRec in context.ProductTemplates
                    on planInfo.TemplateId equals templateRec.TemplateId into templateGroup
                 from template in templateGroup.DefaultIfEmpty()
                 join profileRec in context.CustomerProfiles
                    on planInfo.ProfileId equals profileRec.ProfileId into profileGroup
                 from profile in profileGroup.DefaultIfEmpty()
                 join territoryRec in context.Territories
                    on planInfo.TerritoryId equals territoryRec.TerritoryId into territoryGroup
                 from territory in territoryGroup.DefaultIfEmpty()
                 where planInfo.ActiveStatus
                 && planInfo.PlanId == plan.PlanId
                 select new
                 {
                    PlanId = planInfo.PlanId,
                    TemplateId = planInfo.TemplateId,
                    TemplateGridId = planInfo.TemplateId,
                    ProfileRec = (profile == null ?
                    new
                    {
                       ProfileId = 0,
                       ProfileGridId = 0,
                       Description = string.Empty
                    } :
                    new
                    {
                       ProfileId = profile.ProfileId,
                       ProfileGridId = profile.ProfileId,
                       Description = profile.Description
                    }),
                    ProfileId = (profile == null ? 0 : profile.ProfileId),
                    TerritoryRec = (territory == null ?
                    new
                    {
                       TerritoryId = 0,
                       TerritoryGridId = 0,
                       Description = string.Empty
                    } :
                    new
                    {
                       TerritoryId = territory.TerritoryId,
                       TerritoryGridId = territory.TerritoryId,
                       Description = territory.Description
                    }),
                    TerritoryId = (territory == null ? 0 : territory.TerritoryId),
                    Description = (template == null ? string.Empty: template.Description),
                    TemplateEffectiveDate = planInfo.TemplateEffectiveDate,
                    TemplateExpiryDate = planInfo.TemplateExpiryDate,
                    MinVolume = planInfo.MinVolume,
                    MaxVolume = planInfo.MaxVolume,
                    AccrualPercent = planInfo.AccrualPercent,
                    AccrualPercentNatl = planInfo.AccrualPercentNatl,
                    EffectiveDate = planInfo.EffectiveDate,
                    ExpiryDate = planInfo.ExpiryDate
                 }).ToList();

Here is the generated SQL, by the way. I consistently seem to get numerous no matter what I do.INNER JOIN proceeded byLEFT JOIN when all I want is what I wantLEFT JOIN .

SELECT 
    [Filter1].[PlanId] AS [PlanId], 
    [Filter1].[TemplateId1] AS [TemplateId], 
    [Filter1].[ProfileId1] AS [ProfileId], 
    [Filter1].[Description1] AS [Description], 
    CASE WHEN ([Extent4].[TerritoryId] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1], 
    [Extent4].[TerritoryId] AS [TerritoryId], 
    [Extent4].[Description] AS [Description1], 
    CASE WHEN ([Extent4].[TerritoryId] IS NULL) THEN 0 ELSE [Extent4].[TerritoryId] END AS [C2], 
    [Filter1].[Description2] AS [Description2], 
    [Filter1].[TemplateEffectiveDate] AS [TemplateEffectiveDate], 
    [Filter1].[TemplateExpiryDate] AS [TemplateExpiryDate], 
    [Filter1].[MinVolume] AS [MinVolume], 
    [Filter1].[MaxVolume] AS [MaxVolume], 
    [Filter1].[AccrualPercent] AS [AccrualPercent], 
    [Filter1].[AccrualPercentNatl] AS [AccrualPercentNatl], 
    [Filter1].[EffectiveDate] AS [EffectiveDate], 
    [Filter1].[ExpiryDate] AS [ExpiryDate]
    FROM   (SELECT [Extent1].[PlanId] AS [PlanId], [Extent1].[TemplateId] AS [TemplateId1], [Extent1].[TerritoryId] AS [TerritoryId], [Extent1].[TemplateEffectiveDate] AS [TemplateEffectiveDate], [Extent1].[TemplateExpiryDate] AS [TemplateExpiryDate], [Extent1].[MinVolume] AS [MinVolume], [Extent1].[MaxVolume] AS [MaxVolume], [Extent1].[AccrualPercent] AS [AccrualPercent], [Extent1].[AccrualPercentNatl] AS [AccrualPercentNatl], [Extent1].[EffectiveDate] AS [EffectiveDate], [Extent1].[ExpiryDate] AS [ExpiryDate], [Extent2].[Description] AS [Description2], [Extent3].[ProfileId] AS [ProfileId1], [Extent3].[Description] AS [Description1]
        FROM   [dbo].[PlanInfo] AS [Extent1]
        INNER JOIN [dbo].[ProductTemplates] AS [Extent2] ON [Extent1].[TemplateId] = [Extent2].[TemplateId]
        INNER JOIN [dbo].[CustomerProfiles] AS [Extent3] ON [Extent1].[ProfileId] = [Extent3].[ProfileId]
        WHERE [Extent1].[ActiveStatus] = 1 ) AS [Filter1]
    LEFT OUTER JOIN [dbo].[Territories] AS [Extent4] ON [Filter1].[TerritoryId] = [Extent4].[TerritoryId]
    WHERE [Filter1].[PlanId] = '12345'

I'm hoping someone can let me know what I'm doing incorrectly. Thanks!

1
1
1/9/2015 10:06:59 PM

Accepted Answer

ZZZ_tmp
1
1/9/2015 10:53:19 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow