Query :: Include with multiple navigations (including optional navigation) fails

[ImmePak]

I`am running ASP.NET Core 1.0 application, using EF7 rc2
Part of class structure:

public sealed class Member
{
    public Guid Id { get; set; }
    public MemberState State { get; set; }
}
public sealed class MemberState
{
     public Guid Id { get; set; }
     public Org Org { get; set; }
}

So, first goal was to make field Org in MemberState nullable and I configured EF context in the following way:

entity.Property<Guid?>("Idf_Org");
entity.HasOne(e => e.Org).WithMany().HasForeignKey("Idf_Org").IsRequired(false);

It was fine before i have need to search Members by Org.Id
First approach, that worked before i marked Org field as nullable:

_provider.Members.Include(m => m.State).ThenInclude(s => s.Org)
        .Where(m => m.State.Org.Id == idfOrg).ToList();

Now it start firing exception : Object reference not set to an instance of an object.
Considering the fact that database not contains any record of MemberState with NULL in Idf_Org it was quite strange.
but this one is working O_o but give me unappropriate result (all included instances is null)

_provider.Members.Where(m => m.State.Org.Id == idfOrg).ToList();

One way i found is to do direct SQL query in that way

_provider.Members.FromSql("SELECT mem.* "
         + "FROM [dbo].[Member] as mem "
         + "inner join [dbo].[State] as st on st.Id = mem.Idf_State "
         + "where st.Idf_Org = {0} ", idfOrg)
         .Include(m => m.State).ThenInclude(s => s.Org).ToList();

The question is follows:

1. Is this behaviour is normal to EF ?
2. Is there a way to get queries without using direct sql in this case ? In my app MemberState and included object Org are involved in many application parts as reports and so on and it is unappropriate to manage all queries with sql.

[divega]

@maumar does this look familiar? In triage we discussed that the NRE may come from m.State.Org.Id if .Org is null, but we believe you improved the expansion to compensate for that. Was that after RC2?

[maumar]

@ImmePak can you provide some more information? im unable to reproduce this. Ideally provide standalone repro. Otherwise please provide code for Org class and a complete code for OnModelCreating. If you are using sample data, that would be great also.

[maumar]

@ImmePak as a workaround you can try using two queries, first one would gather all the Ids needed, and the other one would perform the query:

var memberIds = _provider.Members.Where(m => m.State.Org.Id == idfOrg).Select(m => m.Id).ToList();

var query = _provider.Members.Include(m => m.State).ThenInclude(s => s.Org)
        .Where(m => memberIds.Contains(m.Id)).ToList()

[ImmePak]

@maumar as you asked:

EF OnModelCreating code

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
     modelBuilder.Entity<OrgType>(entity =>
     {
           entity.ToTable("OrganizationType");
           entity.HasKey(e => e.Id);
     });
     modelBuilder.Entity<Org>(entity =>
     {
           entity.ToTable("Organization");
           entity.HasKey(e => e.Id);
           entity.Property<int>("Idf_TypeOrg");
           entity.HasOne(org => org.Type).WithMany().HasForeignKey("Idf_TypeOrg");
           entity.Property(x => x.ContactPerson).HasColumnName("Contacts");
     });

     modelBuilder.Entity<MemberState>(entity =>
     {
           entity.ToTable("State");
           entity.HasKey(e => e.Id);
           entity.Property<Guid?>("Idf_Org");
           entity.HasOne(e => e.Org).WithMany().HasForeignKey("Idf_Org").IsRequired(false);
           entity.Property<Guid>("Idf_District");
           entity.HasOne(e=>e.District).WithMany().HasForeignKey("Idf_District");
           entity.Property<int>("Idf_Class");
           entity.HasOne(e => e.Class).WithMany().HasForeignKey("Idf_Class");
           entity.Property<int>("Idf_Level");
           entity.HasOne(e => e.Level).WithMany().HasForeignKey("Idf_Level");
    });
    modelBuilder.Entity<Member>(entity =>
    {
           entity.HasKey(e => e.Id);
           entity.Property<Guid>("Idf_State");
           entity.HasOne(member => member.State).WithMany().HasForeignKey("Idf_State");
    });
....

Expanded class code

    public sealed class Org
    {
        public Guid Id { get; set; }
        public string Name { get; set; }
        public OrgType Type { get; set; }
        public string UrAddress { get; set; }
        public string ContactPerson { get; set; }
        public string ExtraInfo { get; set; }
        public bool Active { get; set; }
   } 
   public sealed class MemberState
   {
        public Guid Id { get; set; }
        public Level Level { get; set; }
        public Org Org { get; set; }
        public Class Class { get; set; }
        public District District { get; set; }
   }
   public sealed class Member
   {
        public Guid Id { get; set; }
        public String Fio { get; set; }
        public String IdGto { get; set; }
        public Boolean Gender { get; set; }
        public DateTime Birthday { get; set; }
        public MemberState State { get; set; }
    }

[maumar]

I was able to reproduce the issue. It is a legitimate bug, looking into it...

[maumar]

fixed in 5f48163