Sto cercando di creare una query che unisce quattro tabelle insieme, solo una delle quali è garantita per esistere. Ho avuto successo a creare join a sinistra in EF prima, ma mai con più di un join. Ecco la 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();
Ed ecco l'SQL che genera. A prescindere da ciò che mi sembra di fare, ottengo sempre più INNER JOIN
seguito da LEFT JOIN
, quando quello che voglio è tutto 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'
Sperando che qualcuno possa far notare cosa sto facendo male qui. Grazie!
Gli INNER JOINS
rappresentano le associazioni richieste n: 1. Se disponi di proprietà di navigazione, il join sarà un riferimento a un altro, richiesto, entità, non una raccolta. EF sa dal modello di dati che un OUTER JOIN
avrebbe alcun effetto qui, quindi genera un INNER JOIN
.