Querying with mapped relations

Jul 27, 2015 at 10:47 AM
I have a TVF that returns rows with relations to other tables.

If I query it like this:
this.dataContext.PlannedTasks.GetScheduledTasksBetweenDates(viewModel.StartDate, viewModel.EndDate)
.Join(
    this.dataContext.TaskTypes.All,
    plannedTask => plannedTask.TaskTypeID,
    taskType => taskType.ID,
    (plannedTask, taskType) => new { plannedTask, taskType })
.Join(
    this.dataContext.TaskPriorities.All,
    tmp => tmp.plannedTask.TaskPriorityID,
    taskPriority => taskPriority.ID,
    (tmp, taskPriority) => new { tmp.plannedTask, tmp.taskType, taskPriority })
.AsNoTracking()
.Join(
    this.dataContext.Entities.All,
    tmp => tmp.plannedTask.EntityID,
    entity => entity.ID,
    (tmp, entity) => new { tmp.plannedTask, tmp.taskType, tmp.taskPriority, entity })
.AsNoTracking()
.Select(tmp => new PlannedTaskListItemViewModel
{
    ID = tmp.plannedTask.ID,
    Title = tmp.plannedTask.Title,
    StartDate = tmp.plannedTask.StartDate,
    Duration = tmp.plannedTask.Duration,
    TaskTypeID = tmp.taskType.ID,
    TaskTypeTitle = tmp.taskType.Title,
    TaskPriorityID = tmp.taskPriority.ID,
    TaskPriorityTitle = tmp.taskPriority.Title,
    EntityID = tmp.entity.ID,
    EntityTitle = tmp.entity.Title
})
.ToList();
I get this TSQL query:
SELECT 
    [Extent2].[ID] AS [ID], 
    [Extent1].[ID] AS [ID1], 
    [Extent1].[Title] AS [Title], 
    [Extent1].[StartDate] AS [StartDate], 
    [Extent1].[Duration] AS [Duration], 
    [Extent2].[Title] AS [Title1], 
    [Extent3].[ID] AS [ID2], 
    [Extent3].[Title] AS [Title2], 
    [Extent4].[ID] AS [ID3], 
    [Extent4].[Title] AS [Title3]
    FROM    [Api].[GetScheduledTasksBetweenDates](7/27/2015 12:00:00 AM /* @startDate */, 7/27/2015 12:00:00 AM /* @endDate */) AS [Extent1]
    INNER JOIN [Api].[TaskTypes] AS [Extent2] ON [Extent1].[TaskTypeID] = [Extent2].[ID]
    INNER JOIN [Api].[TaskPriorities] AS [Extent3] ON [Extent1].[TaskPriorityID] = [Extent3].[ID]
    INNER JOIN [Api].[Entities] AS [Extent4] ON [Extent1].[EntityID] = [Extent4].[ID]
However, if I rely on entity framework mapping:
this.dataContext.PlannedTasks.GetScheduledTasksBetweenDates(viewModel.StartDate, viewModel.EndDate)
.Join(
    this.dataContext.TaskTypes.All,
    plannedTask => plannedTask.TaskTypeID,
    taskType => taskType.ID,
    (plannedTask, taskType) => new { plannedTask, taskType })
.Join(
    this.dataContext.TaskPriorities.All,
    tmp => tmp.plannedTask.TaskPriorityID,
    taskPriority => taskPriority.ID,
    (tmp, taskPriority) => new { tmp.plannedTask, tmp.taskType, taskPriority })
.AsNoTracking()
.Join(
    this.dataContext.Entities.All,
    tmp => tmp.plannedTask.EntityID,
    entity => entity.ID,
    (tmp, entity) => new { tmp.plannedTask, tmp.taskType, tmp.taskPriority, entity })
.AsNoTracking()
.Select(tmp => new PlannedTaskListItemViewModel
{
    ID = tmp.ID,
    Title = tmp.Title,
    StartDate = tmp.StartDate,
    Duration = tmp.Duration,
    TaskTypeID = tmp.TaskType.ID,
    TaskTypeTitle = tmp.TaskType.Title,
    TaskPriorityID = tmp.TaskPriority.ID,
    TaskPriorityTitle = tmp.TaskPriority.Title,
    EntityID = tmp.Entity.ID,
    EntityTitle = tmp.Entity.Title
})
.ToList();
I get this TSQL query:
SELECT 
    1 AS [C1], 
    [Extent1].[ID] AS [ID], 
    [Extent1].[Title] AS [Title], 
    [Extent1].[StartDate] AS [StartDate], 
    [Extent1].[Duration] AS [Duration], 
    [Extent4].[TaskTypeID] AS [TaskTypeID], 
    [Extent5].[Title] AS [Title1], 
    [Extent3].[TaskPriorityID] AS [TaskPriorityID], 
    [Extent6].[Title] AS [Title2], 
    [Extent2].[EntityID] AS [EntityID], 
    [Extent7].[Title] AS [Title3]
    FROM      [Api].[GetScheduledTasksBetweenDates](7/27/2015 12:00:00 AM /* @startDate */, 7/27/2015 12:00:00 AM /* @endDate */) AS [Extent1]
    LEFT OUTER JOIN [dbo].[PlannedTasks] AS [Extent2] ON [Extent1].[ID] = [Extent2].[ID]
    LEFT OUTER JOIN [dbo].[PlannedTasks] AS [Extent3] ON [Extent1].[ID] = [Extent3].[ID]
    LEFT OUTER JOIN [dbo].[PlannedTasks] AS [Extent4] ON [Extent1].[ID] = [Extent4].[ID]
    LEFT OUTER JOIN [Api].[TaskTypes] AS [Extent5] ON [Extent4].[TaskTypeID] = [Extent5].[ID]
    LEFT OUTER JOIN [Api].[TaskPriorities] AS [Extent6] ON [Extent3].[TaskPriorityID] = [Extent6].[ID]
    LEFT OUTER JOIN [Api].[Entities] AS [Extent7] ON [Extent2].[EntityID] = [Extent7].[ID]
The [dbo].[PlannedTasks] doesn't exist nor is needed for the query.

Is this not supported, or am I doing smotething wrong?
Aug 3, 2015 at 5:35 AM
Is there any difference between your Linq queries? I could not find any. I also am not sure what "if I rely on entity framework mapping" means. Please provide more background otherwise I am afraid I won't be able to answer or give any hints as I don't understand the problem.

Thanks,
Pawel
Aug 3, 2015 at 5:58 AM
I should have tried to make them more different.

The entity returned by the GetScheduledTasksBetweenDates has relations with three other entities:
  • TaskTypeID
    • TaskType
      • ID
      • Title
  • TaskPriorityID
    • TaskPriority
      • ID
      • Title
  • EntityID
    • Entity
      • ID
      • Title
The second query could be just:
this.dataContext.PlannedTasks.GetScheduledTasksBetweenDates(viewModel.StartDate, viewModel.EndDate)
.AsNoTracking()
.Select(tmp => new PlannedTaskListItemViewModel
{
    ID = tmp.ID,
    Title = tmp.Title,
    StartDate = tmp.StartDate,
    Duration = tmp.Duration,
    TaskTypeID = tmp.TaskType.ID,
    TaskTypeTitle = tmp.TaskType.Title,
    TaskPriorityID = tmp.TaskPriority.ID,
    TaskPriorityTitle = tmp.TaskPriority.Title,
    EntityID = tmp.Entity.ID,
    EntityTitle = tmp.Entity.Title
})
.ToList();
I just explicitly wrote the joins. By the way, that was my initial query.

The problem is that the generated query has 3 joins to [dbo].[PlannedTasks] which doesn't exist nor I understand where it's coming from.
Aug 4, 2015 at 1:35 AM
Isn't it because you are accessing related entities using navigation properties like this: 'tmp.TaskPriority.Title`? Note that EF does not support graphs in results returned by TVFs or stored procs so if you are accessing related entities there need to be a join somewhere.

Thanks,
Pawel
Aug 4, 2015 at 7:05 AM
That must be it. but it looks EF went berserk.

It's killing my AutoMapper use.

I'm tempted to intercept the query and see how it goes. :D