For a simple join in Linq, the generated query seems to contain too many select columns.
This is why the multi-part identifier could not be bound.
This is a simplified version of my query:
var checkListPreSelect = (from it in Tasks
join p in Tasks on it.ParentId equals p.Id
where p.Type == TaskType.Checklist
select it).ToList();
The generated query is shown below. Why does it need to select [t2] which is not used anywhere?
If I restrict the select to a particular column, the query is generated correctly.
var checkListPreSelect = (from it in Tasks
join p in Tasks on it.ParentId equals p.Id
where p.Type == TaskType.Checklist
select it.Name).ToList();
The working query:
SELECT
[t0].[Name] AS [t0.Name]
FROM
[Task] [t0]
INNER JOIN
[Task] [t1]
ON
[t0].[ParentId] = [t1].[Id] AND [t1].[DeletedOn] IS NULL
WHERE
([t1].[Type] = 22 AND [t0].[DeletedOn] IS NULL)
--> Time: 82 ms
The broken query:
SELECT
[t0].[Id] AS [t0.Id],
[t0].[CreatedBy] AS [t0.CreatedBy],
[t0].[CreatedOn] AS [t0.CreatedOn],
[t0].[DeletedBy] AS [t0.DeletedBy],
[t0].[DeletedOn] AS [t0.DeletedOn],
[t0].[UpdatedBy] AS [t0.UpdatedBy],
[t0].[UpdatedOn] AS [t0.UpdatedOn],
[t0].[LastSyncOn] AS [t0.LastSyncOn],
[t0].[ShouldBeSynced] AS [t0.ShouldBeSynced],
[t0].[SyncId] AS [t0.SyncId],
[t0].[ArticleQuantity] AS [t0.ArticleQuantity],
[t0].[ArticleTargetQuantity] AS [t0.ArticleTargetQuantity],
[t0].[ArticleVersionId] AS [t0.ArticleVersionId],
[t0].[Barcode] AS [t0.Barcode],
[t0].[BaselineEndDate] AS [t0.BaselineEndDate],
[t0].[BaselinePercentDone] AS [t0.BaselinePercentDone],
[t0].[BaselineStartDate] AS [t0.BaselineStartDate],
[t0].[BilledSum] AS [t0.BilledSum],
[t0].[BillingMode] AS [t0.BillingMode],
[t0].[Color] AS [t0.Color],
[t0].[ConfigWorkflowId] AS [t0.ConfigWorkflowId],
[t0].[ContactPersonHistoryId] AS [t0.ContactPersonHistoryId],
[t0].[Currency] AS [t0.Currency],
[t0].[Depth] AS [t0.Depth],
[t0].[Description] AS [t0.Description],
[t0].[DocumentId] AS [t0.DocumentId],
[t0].[Duration] AS [t0.Duration],
[t0].[DurationUnit] AS [t0.DurationUnit],
[t0].[EndDate] AS [t0.EndDate],
[t0].[FeedbackFileUniqueId] AS [t0.FeedbackFileUniqueId],
[t0].[FileId] AS [t0.FileId],
[t0].[ICalUId] AS [t0.ICalUId],
[t0].[ImportTaskId] AS [t0.ImportTaskId],
[t0].[Index] AS [t0.Index],
[t0].[IsAdminPhase] AS [t0.IsAdminPhase],
[t0].[IsAppointment] AS [t0.IsAppointment],
[t0].[IsApproved] AS [t0.IsApproved],
[t0].[IsDone] AS [t0.IsDone],
[t0].[IsEmailDone] AS [t0.IsEmailDone],
[t0].[IsFixed] AS [t0.IsFixed],
[t0].[IsPriority] AS [t0.IsPriority],
[t0].[JiraProjectName] AS [t0.JiraProjectName],
[t0].[LeadId] AS [t0.LeadId],
[t0].[Name] AS [t0.Name],
[t0].[ParentId] AS [t0.ParentId],
[t0].[PercentDone] AS [t0.PercentDone],
[t0].[PhaseId] AS [t0.PhaseId],
[t0].[Place] AS [t0.Place],
[t0].[PlannedEffort] AS [t0.PlannedEffort],
[t0].[ProjectBudgetId] AS [t0.ProjectBudgetId],
[t0].[ProjectId] AS [t0.ProjectId],
[t0].[ProjectRoleId] AS [t0.ProjectRoleId],
[t0].[ProjectTemplateId] AS [t0.ProjectTemplateId],
[t0].[RecurrencyStorage] AS [t0.RecurrencyStorage],
[t0].[Remaining] AS [t0.Remaining],
[t0].[StartDate] AS [t0.StartDate],
[t0].[TaskTypeId] AS [t0.TaskTypeId],
[t0].[TaskTypeStatusId] AS [t0.TaskTypeStatusId],
[t0].[Type] AS [t0.Type],
[t1].[Id] AS [t1.Id],
[t1].[CreatedBy] AS [t1.CreatedBy],
[t1].[CreatedOn] AS [t1.CreatedOn],
[t1].[DeletedBy] AS [t1.DeletedBy],
[t1].[DeletedOn] AS [t1.DeletedOn],
[t1].[UpdatedBy] AS [t1.UpdatedBy],
[t1].[UpdatedOn] AS [t1.UpdatedOn],
[t1].[LastSyncOn] AS [t1.LastSyncOn],
[t1].[ShouldBeSynced] AS [t1.ShouldBeSynced],
[t1].[SyncId] AS [t1.SyncId],
[t1].[ArticleQuantity] AS [t1.ArticleQuantity],
[t1].[ArticleTargetQuantity] AS [t1.ArticleTargetQuantity],
[t1].[ArticleVersionId] AS [t1.ArticleVersionId],
[t1].[Barcode] AS [t1.Barcode],
[t1].[BaselineEndDate] AS [t1.BaselineEndDate],
[t1].[BaselinePercentDone] AS [t1.BaselinePercentDone],
[t1].[BaselineStartDate] AS [t1.BaselineStartDate],
[t1].[BilledSum] AS [t1.BilledSum],
[t1].[BillingMode] AS [t1.BillingMode],
[t1].[Color] AS [t1.Color],
[t1].[ConfigWorkflowId] AS [t1.ConfigWorkflowId],
[t1].[ContactPersonHistoryId] AS [t1.ContactPersonHistoryId],
[t1].[Currency] AS [t1.Currency],
[t1].[Depth] AS [t1.Depth],
[t1].[Description] AS [t1.Description],
[t1].[DocumentId] AS [t1.DocumentId],
[t1].[Duration] AS [t1.Duration],
[t1].[DurationUnit] AS [t1.DurationUnit],
[t1].[EndDate] AS [t1.EndDate],
[t1].[FeedbackFileUniqueId] AS [t1.FeedbackFileUniqueId],
[t1].[FileId] AS [t1.FileId],
[t1].[ICalUId] AS [t1.ICalUId],
[t1].[ImportTaskId] AS [t1.ImportTaskId],
[t1].[Index] AS [t1.Index],
[t1].[IsAdminPhase] AS [t1.IsAdminPhase],
[t1].[IsAppointment] AS [t1.IsAppointment],
[t1].[IsApproved] AS [t1.IsApproved],
[t1].[IsDone] AS [t1.IsDone],
[t1].[IsEmailDone] AS [t1.IsEmailDone],
[t1].[IsFixed] AS [t1.IsFixed],
[t1].[IsPriority] AS [t1.IsPriority],
[t1].[JiraProjectName] AS [t1.JiraProjectName],
[t1].[LeadId] AS [t1.LeadId],
[t1].[Name] AS [t1.Name],
[t1].[ParentId] AS [t1.ParentId],
[t1].[PercentDone] AS [t1.PercentDone],
[t1].[PhaseId] AS [t1.PhaseId],
[t1].[Place] AS [t1.Place],
[t1].[PlannedEffort] AS [t1.PlannedEffort],
[t1].[ProjectBudgetId] AS [t1.ProjectBudgetId],
[t1].[ProjectId] AS [t1.ProjectId],
[t1].[ProjectRoleId] AS [t1.ProjectRoleId],
[t1].[ProjectTemplateId] AS [t1.ProjectTemplateId],
[t1].[RecurrencyStorage] AS [t1.RecurrencyStorage],
[t1].[Remaining] AS [t1.Remaining],
[t1].[StartDate] AS [t1.StartDate],
[t1].[TaskTypeId] AS [t1.TaskTypeId],
[t1].[TaskTypeStatusId] AS [t1.TaskTypeStatusId],
[t1].[Type] AS [t1.Type],
[t2].[Id] AS [t2.Id],
[t2].[CreatedBy] AS [t2.CreatedBy],
[t2].[CreatedOn] AS [t2.CreatedOn],
[t2].[DeletedBy] AS [t2.DeletedBy],
[t2].[DeletedOn] AS [t2.DeletedOn],
[t2].[UpdatedBy] AS [t2.UpdatedBy],
[t2].[UpdatedOn] AS [t2.UpdatedOn],
[t2].[LastSyncOn] AS [t2.LastSyncOn],
[t2].[ShouldBeSynced] AS [t2.ShouldBeSynced],
[t2].[SyncId] AS [t2.SyncId],
[t2].[ArticleQuantity] AS [t2.ArticleQuantity],
[t2].[ArticleTargetQuantity] AS [t2.ArticleTargetQuantity],
[t2].[ArticleVersionId] AS [t2.ArticleVersionId],
[t2].[Barcode] AS [t2.Barcode],
[t2].[BaselineEndDate] AS [t2.BaselineEndDate],
[t2].[BaselinePercentDone] AS [t2.BaselinePercentDone],
[t2].[BaselineStartDate] AS [t2.BaselineStartDate],
[t2].[BilledSum] AS [t2.BilledSum],
[t2].[BillingMode] AS [t2.BillingMode],
[t2].[Color] AS [t2.Color],
[t2].[ConfigWorkflowId] AS [t2.ConfigWorkflowId],
[t2].[ContactPersonHistoryId] AS [t2.ContactPersonHistoryId],
[t2].[Currency] AS [t2.Currency],
[t2].[Depth] AS [t2.Depth],
[t2].[Description] AS [t2.Description],
[t2].[DocumentId] AS [t2.DocumentId],
[t2].[Duration] AS [t2.Duration],
[t2].[DurationUnit] AS [t2.DurationUnit],
[t2].[EndDate] AS [t2.EndDate],
[t2].[FeedbackFileUniqueId] AS [t2.FeedbackFileUniqueId],
[t2].[FileId] AS [t2.FileId],
[t2].[ICalUId] AS [t2.ICalUId],
[t2].[ImportTaskId] AS [t2.ImportTaskId],
[t2].[Index] AS [t2.Index],
[t2].[IsAdminPhase] AS [t2.IsAdminPhase],
[t2].[IsAppointment] AS [t2.IsAppointment],
[t2].[IsApproved] AS [t2.IsApproved],
[t2].[IsDone] AS [t2.IsDone],
[t2].[IsEmailDone] AS [t2.IsEmailDone],
[t2].[IsFixed] AS [t2.IsFixed],
[t2].[IsPriority] AS [t2.IsPriority],
[t2].[JiraProjectName] AS [t2.JiraProjectName],
[t2].[LeadId] AS [t2.LeadId],
[t2].[Name] AS [t2.Name],
[t2].[ParentId] AS [t2.ParentId],
[t2].[PercentDone] AS [t2.PercentDone],
[t2].[PhaseId] AS [t2.PhaseId],
[t2].[Place] AS [t2.Place],
[t2].[PlannedEffort] AS [t2.PlannedEffort],
[t2].[ProjectBudgetId] AS [t2.ProjectBudgetId],
[t2].[ProjectId] AS [t2.ProjectId],
[t2].[ProjectRoleId] AS [t2.ProjectRoleId],
[t2].[ProjectTemplateId] AS [t2.ProjectTemplateId],
[t2].[RecurrencyStorage] AS [t2.RecurrencyStorage],
[t2].[Remaining] AS [t2.Remaining],
[t2].[StartDate] AS [t2.StartDate],
[t2].[TaskTypeId] AS [t2.TaskTypeId],
[t2].[TaskTypeStatusId] AS [t2.TaskTypeStatusId],
[t2].[Type] AS [t2.Type]
FROM
[Task] [t0]
INNER JOIN
[Task] [t1]
ON
[t0].[ParentId] = [t1].[Id] AND [t1].[DeletedOn] IS NULL
WHERE
([t1].[Type] = 22 AND [t0].[DeletedOn] IS NULL)
--> Time: 0 ms
