I'm trying to create a query that joins four tables together, only one of which is guaranteed to exist. I've been successful creating left joins in EF before, but never with more than one join. Here is the query:
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();
And here is the SQL that it generates. No matter what I seem to do, I always get several INNER JOIN followed by LEFT JOIN, when what I want is all LEFT 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'
Hoping someone can point out what I'm doing wrong here. Thanks!
joinin stead of using navigation properties?plan.PlanId?plan.PlanIdis the header record from above in the code. This is happening in a loop and we're pulling the details for chosen Plans.