Query: Multiple round trips for nav prop projections

[sebitsi]

Hi all.

When i use multiple reference to same entity sql query is not generated properly.

Model:

public class Subject {
    public int SubjectID { get; set; }
    public string Name { get; set; }
}

public class Task {
    public int TaskID { get; set; }
    public string Name { get; set; }
    public Subject AssignedBy { get; set; }
    public int AssignedToID { get; set; }
    public Subject OwnedBy { get; set; }
    public int OwnedByID { get; set; }
    public Subject BlaBy { get; set; }
    public int? BlaID { get; set; }
}

public class ViewModelTest
{
    public int TaskID { get; set; }
    public string Name { get; set; }
    public string AssigneeName { get; set; }
    public string OwnerName { get; set; }
    public string BlaName { get; set; }
}

DBContext

    public class TestContext : DbContext
    {
        public TestContext()
        { }

        public TestContext(DbContextOptions<TestContext> options) : base(options)
        { }

        public DbSet<Subject> Subjects { get; set; }
        public DbSet<Task> Tasks { get; set; }

        protected override void OnModelCreating(ModelBuilder builder)
        {
            builder.Entity<Subject>().ToTable("Subject", "dbo").HasKey("SubjectID");
            builder.Entity<Task>().ToTable("Task", "dbo").HasKey("TaskID");
            builder.Entity<Task>().HasOne(p => p.AssignedBy).WithMany().HasForeignKey(k => k.AssignedToID);
            builder.Entity<Task>().HasOne(p => p.OwnedBy).WithMany().HasForeignKey(k => k.OwnedByID);
            builder.Entity<Task>().HasOne(p => p.BlaBy).WithMany().HasForeignKey(k => k.BlaID);
        }
    }

Query1

When executing this query

var data = ctx.Tasks
                .Where(t => t.TaskID == 1)
                .Select(s => new ViewModelTest()
                {
                    TaskID = s.TaskID,
                    AssigneeName = s.AssignedBy.Name,
                    OwnerName = s.OwnedBy.Name,
                    BlaName = s.BlaBy.Name
                })
                .FirstOrDefault();

SQL is generated in 3 steps (requests):

SELECT [t].[TaskID], [t].[AssignedToID], [t].[BlaID], [t].[Name], [t].[OwnedByID], [t.BlaBy].[SubjectID], [t.BlaBy].[Name]
FROM [dbo].[Task] AS [t]
LEFT JOIN [dbo].[Subject] AS [t.BlaBy] ON [t].[BlaID] = [t.BlaBy].[SubjectID]
WHERE [t].[TaskID] = 1
ORDER BY [t].[BlaID]

SELECT [t.OwnedBy].[SubjectID], [t.OwnedBy].[Name]
FROM [dbo].[Subject] AS [t.OwnedBy]

SELECT [t.AssignedBy].[SubjectID], [t.AssignedBy].[Name]
FROM [dbo].[Subject] AS [t.AssignedBy]

So if subject table has +100K resords this is very unefficient.

Query2

Let's change model little. Just change BlaID field from int? to int:

public class Task {
    public int TaskID { get; set; }
    public string Name { get; set; }
    public Subject AssignedBy { get; set; }
    public int AssignedToID { get; set; }
    public Subject OwnedBy { get; set; }
    public int OwnedByID { get; set; }
    public Subject BlaBy { get; set; }
    public int BlaID { get; set; }
}

In this case SQL query is as expected:

SELECT TOP(1) [t].[TaskID], [t.AssignedBy].[Name], [t.OwnedBy].[Name], [t.BlaBy].[Name]
FROM [dbo].[Task] AS [t]
INNER JOIN [dbo].[Subject] AS [t.BlaBy] ON [t].[BlaID] = [t.BlaBy].[SubjectID]
INNER JOIN [dbo].[Subject] AS [t.OwnedBy] ON [t].[OwnedByID] = [t.OwnedBy].[SubjectID]
INNER JOIN [dbo].[Subject] AS [t.AssignedBy] ON [t].[AssignedToID] = [t.AssignedBy].[SubjectID]
WHERE [t].[TaskID] = 1

Is this bug or something else ?

Technical details

EF Core version: 1.0.1
Operating system: Windows 10 Pro
Visual Studio version: 2015 update 3

[maumar]

This is fixed in current bits, we generate the following query:

SELECT TOP(1) [t].[TaskID], [t].[AssignedToID], [t].[BlaID], [t].[Name], [t].[OwnedByID], [t.BlaBy].[SubjectID], [t.BlaBy].[Name], [t.AssignedBy].[Name], [t.OwnedBy].[Name]
FROM [dbo].[Task] AS [t]
LEFT JOIN [dbo].[Subject] AS [t.BlaBy] ON [t].[BlaID] = [t.BlaBy].[SubjectID]
INNER JOIN [dbo].[Subject] AS [t.OwnedBy] ON [t].[OwnedByID] = [t.OwnedBy].[SubjectID]
INNER JOIN [dbo].[Subject] AS [t.AssignedBy] ON [t].[AssignedToID] = [t.AssignedBy].[SubjectID]
WHERE [t].[TaskID] = 1
ORDER BY [t].[BlaID]

[maumar]

We still materialize unnecessary columns, this is tracked here: #6647

[sebitsi]

@maumar

Thanks.
Are this bits on nuget ? 1.1.0-Alpha1-xxxxx ?

[ErikEJ]

On dotnet.myget.org

[sebitsi]

@ErikEJ Thanks.
@maumar Will this fix be included in 1.0.2 patch ?

[maumar]

@sebitsi no, this change was too big/risky to be included in the 1.0.2, it will be included in 1.1.0.