Efcore: Query: accessing property on a required navigation defined on a derived type produces INNER JOIN

[maumar]

Repro:

    class Program
    {
        static void Main(string[] args)
        {
            using (var ctx = new MyContext())
            {
                ctx.Database.EnsureDeleted();
                ctx.Database.EnsureCreated();

                var school = new School { Name = "S1" };

                var p1 = new Parent { ParentName = "p1" };
                var p2 = new Parent { ParentName = "p2" };
                var s1 = new Student { StudentName = "p1", School = school };
                var s2 = new Student { StudentName = "p1", School = school };

                ctx.Schools.Add(school);
                ctx.People.AddRange(p1, p2, s1, s2);
                ctx.SaveChanges();
            }

            using (var ctx = new MyContext())
            {
                Console.WriteLine(ctx.People.Count()); // 4

                var query = ctx.People.OrderBy(p => ((Student)p).School.Name);
                var result = query.ToList();

                Console.WriteLine(result.Count); // 2
            }
        }
    }

    public class MyContext : DbContext
    {
        public DbSet<Person> People { get; set; }
        public DbSet<School> Schools { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer(@"Server=.;Database=Repro10988;Trusted_Connection=True;MultipleActiveResultSets=True");
        }
    }

    public class Person
    {
        public int Id { get; set; }
    }

    public class Student : Person
    {
        public string StudentName { get; set; }

        public School School { get; set; }
        public int SchoolId { get; set; }
    }

    public class Parent : Person
    {
        public string ParentName { get; set; }
    }

    public class School
    {
        public int Id { get; set; }

        public string Name { get; set; }

        public List<Student> Students { get; set; }
    }

We generate the following query:

SELECT [p].[Id], [p].[Discriminator], [p].[SchoolId], [p].[StudentName]
FROM [People] AS [p]
INNER JOIN [Schools] AS [p.School] ON CASE
    WHEN [p].[Discriminator] = N'Student'
    THEN [p].[SchoolId] ELSE NULL
END = [p.School].[Id]
WHERE [p].[Discriminator] IN (N'Student', N'Person')
ORDER BY [p.School].[Name]

We should be producing LOJ here, in case not all people are students (such query would throw cast exception in L2O, but we allow it to execute)

[heightpi]

Until this will be fixed, i used nullable int to generate a LEFT JOIN
public int? SchoolId { get; set; } in the Student Class

[ajcvickers]

Decision from triage: we should make both the explicit cast and the _as_ cast work in the same way, including null compensation/propagation.

[maumar]

Fixed in 1fcdbeacf8c948f5cbb41967c678587f80217fe9

[iudelsmann]

This is not included in 2.1.0-preview1 but seems important to me. I was happy to have the fix for #3910 but including my derived properties is causing INNER JOIN thus not solving my problem (I'm currently explicitly loading those properties). Is it planned to add this to 2.1?

[heightpi]

@iudelsmann as I said in a comment before, changing from int to int? solved my problem, and generates a left join, without the need to explicitly load the properties

[iudelsmann]

@heightpi It does work indeed, but it would also allow someone to save a null value where I wouldn't want one. I can't have the restriction in the database since the parent class doesn't have the property, so I must have the restriction in my code. When someone tries to save value zero the database will throw an error since it violates the foreign key constraint.

I could do this or another workaround, but I prefer waiting for the release since this is already fixed.

[smitpatel]

@iudelsmann - This is fixed in 2.1.0 milestone so it will be included in RTM release of 2.1.0. It should be included in next release (preview/rc/RTM). The milestone will change accordingly to indicate the exact release number.

[ajcvickers]

@iudelsmann Could you test with out nightly build? The feeds are listed on the repo home: https://github.com/aspnet/EntityFrameworkCore

[iudelsmann]

@ajcvickers Sorry for the delay, had some busy days and some troubles. Tried to test this now but seems to be getting a problem with missing apostrophes surrounding my discriminator values, causing the query to be invalid throwing an exception in postgres. Using ef core 2.1.0-preview2-30301 and npgsql.PostgreSQL 2.1.0-ci-00132.

[ajcvickers]

@iudelsmann Can you file a new issue for this including a runnable project/solution or complete code listing so that we can investigate? /cc @roji

[roji]

Any missing apostrophes sound more like an issue in the Npgsql provider (and unrelated to the original issue above), can you please post a code sample on Npgsql.EntityFrameworkCore.PostgreSQL?