EF Core 3.1.7 Eager Load two level ThenInclude last one with inherit type not working

[alfred-zaki]

second .ThenInclude with a derived type class, .ThenInclude(....).ThenInclude(x => ((derivedClass)x).prop)

i have below code not working and need help to make it work.
i have two data models classes in two separate DLLs, and wanted to create a one-to-one optional relationship, of course with direct reference that would lead to "circular reference" situation.
my solution was to have a third DLL referencing both and have another data model inherited from one of the models and add relationship to that.
now i need to include the extra property on that module, my code goes as follow

SecurityGroup securityGroup = await _securityGroupRepository.GetAll().Where(sg => sg.SecurityGroupId == new Guid(groupId))
                                                   .Include(sg => sg.SecurityPolicies)
                                                   .Include(sg => sg.Users)
                                                        .ThenInclude(sgu => sgu.User)
                                                        .ThenInclude(su => ((CrossSecurityUser)su).Person) //this is not working, if i remove it work
                                                        .FirstOrDefaultAsync();

on 1st DLL, Person Module

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Text;

namespace WorkNxt.Contacts.Models
{
    public class Person
    {
        [Required]
        public Guid PersonId { get; set; }
        [Required]
        public string FirstName { get; set; }
        public string MiddleName { get; set; }
        [Required]
        public string LastName { get; set; }
        //public virtual List<PersonsOrgnizations> Organizations { get; set; }
        [NotMapped]
        public string FullName => $"{FirstName} {MiddleName} {LastName}";
        public byte[] RowVersion { get; set; }
    }

    public class PersonEfConfig : IEntityTypeConfiguration<Person>
    {
        public void Configure(EntityTypeBuilder<Person> builder)
        {
            builder.ToTable("Person");
            builder.Property(e => e.PersonId).HasColumnName("PersonId").ValueGeneratedOnAdd();
            builder.HasKey(e => e.PersonId);
            //builder.HasMany(p => p.Organizations).WithOne(po => po.Person).HasForeignKey(po => po.PersonId).IsRequired(false);
            builder.Property(e => e.RowVersion).IsConcurrencyToken().ValueGeneratedOnAddOrUpdate();
        }
    }
}

on 2nd DLL, SecurityGroup, SecurityGroupUsers and Security User which i want one-to-one with Person and same from Person (Circular reference) Modules

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;

namespace WorkNxt.Security.Models
{
    public class SecurityGroup
    {
        [Required]
        public Guid SecurityGroupId { get; set; }
        public string SecurityGroupCode { get; set; }
        public string Descrption { get; set; }
        public byte[] RowVersion { get; set; }

        public virtual List<SecurityGroupsTenantsPolicies> SecurityPolicies { get; set; }

        public virtual List<SecurityGroupsUsers> Users { get; set; }
    }

    public class SecurityGroupEfConfig : IEntityTypeConfiguration<SecurityGroup>
    {
        public void Configure(EntityTypeBuilder<SecurityGroup> builder)
        {
            builder.ToTable("SecurityGroup");
            builder.Property(e => e.SecurityGroupId).HasColumnName("SecurityGroupId");

            builder.HasKey(e => new { e.SecurityGroupId });

            builder.Property(e => e.RowVersion).IsConcurrencyToken().ValueGeneratedOnAddOrUpdate();

            builder.HasMany(sg => sg.SecurityPolicies).WithOne(pg => pg.SecurityGroup).HasForeignKey(pg => pg.SecurityGroupId);
            builder.HasMany(sg => sg.Users).WithOne(ug => ug.SecurityGroup).HasForeignKey(ug => ug.GroupId);
        }
    }
}

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;
using System;
using System.ComponentModel.DataAnnotations;

namespace WorkNxt.Security.Models
{
    public class SecurityGroupsUsers
    {
        [Required]
        public Guid UserId { get; set; }
        public SecurityUser User { get; set; }
        [Required]
        public Guid GroupId { get; set; }
        public SecurityGroup SecurityGroup { get; set; }

        public class SecurityGroupsUsersEfConfig : IEntityTypeConfiguration<SecurityGroupsUsers>
        {
            public void Configure(EntityTypeBuilder<SecurityGroupsUsers> builder)
            {
                builder.ToTable("SecurityGroupsUsers");
                builder.Property(e => e.UserId).HasColumnName("UserId");
                builder.Property(e => e.GroupId).HasColumnName("GroupId");

                builder.HasKey(e => new { e.UserId, e.GroupId });

                builder.HasOne(ug => ug.User).WithMany(u => u.SecurityGroups).HasForeignKey(ug => ug.UserId);
                builder.HasOne(ug => ug.SecurityGroup).WithMany(sg => sg.Users).HasForeignKey(ug => ug.GroupId);
            }
        }
    }
}

using Microsoft.EntityFrameworkCore.Metadata.Builders;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Text;


namespace WorkNxt.Security.Models
{
    public class SecurityUser
    {
        public Guid SecurityUserId { get; set; }
        [Required]
        public string UserName { get; set; }
        [NotMapped]
        [Required]
        [DataType(DataType.Password)]
        public string Password { get; set; }
        public string Hash { get; set; }
        public string Salt { get; set; }
        public byte[] RowVersion { get; set; }
        public virtual List<SecurityGroupsUsers> SecurityGroups { get; set; }
    }

    public class SecurityUserEfConfig : IEntityTypeConfiguration<SecurityUser>
    {
        public void Configure(EntityTypeBuilder<SecurityUser> builder)
        {
            builder.ToTable("SecurityUser");
            builder.Property(e => e.SecurityUserId).HasColumnName("SecurityUserId");
            builder.HasKey(e => new { e.SecurityUserId });

            builder.HasIndex(e => e.UserName).IsUnique();

            builder.Property(e => e.RowVersion).IsConcurrencyToken().ValueGeneratedOnAddOrUpdate();

            builder.HasMany(u => u.SecurityGroups).WithOne(sg => sg.User).HasForeignKey(sg => sg.UserId);
        }
    }
}

to avoid that i have a 3rd DLL with

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;
using System;
using System.ComponentModel.DataAnnotations.Schema;
using WorkNxt.Contacts.Models;
using WorkNxt.Security.Models;

namespace WorkNxt.Security.CrossModels
{
    public class CrossSecurityUser : SecurityUser
    {
        public Guid? PersonId { get; set; }
        [ForeignKey("PersonId")]
        public virtual Person Person { get; set; }
    }
}

now when this execute

SecurityGroup securityGroup = await _securityGroupRepository.GetAll().Where(sg => sg.SecurityGroupId == new Guid(groupId))
                                                   .Include(sg => sg.SecurityPolicies)
                                                   .Include(sg => sg.Users)
                                                        .ThenInclude(sgu => sgu.User)
                                                        .ThenInclude(su => ((CrossSecurityUser)su).Person) //this is not working, if i remove it work
                                                        .FirstOrDefaultAsync();

i get "Invalid include."

Stack Trace

at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.PopulateIncludeTree(IncludeTreeNode includeTreeNode, Expression expression)
at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.ProcessInclude(NavigationExpansionExpression source, Expression expression, Boolean thenInclude)
at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.Expand(Expression query)
at Microsoft.EntityFrameworkCore.Query.QueryTranslationPreprocessor.Process(Expression query)
at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)
at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass12_01.<ExecuteAsync>b__0() at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQueryCore[TFunc](Object cacheKey, Func1 compiler)
at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func1 compiler) at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteAsync[TResult](Expression query, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.ExecuteAsync[TResult](Expression expression, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ExecuteAsync[TSource,TResult](MethodInfo operatorMethodInfo, IQueryable1 source, Expression expression, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ExecuteAsync[TSource,TResult](MethodInfo operatorMethodInfo, IQueryable1 source, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.FirstOrDefaultAsync[TSource](IQueryable1 source, CancellationToken cancellationToken)
at WorkNxt.Security.Api.Controllers.GroupController.d__7.MoveNext() in {removed for privacy}\WorkNxt.Security.Api\Controllers\GroupController.cs:line 101

Further technical details

EF Core version: 3.1.7
Database provider: (Microsoft.EntityFrameworkCore.SqlServer)
Target framework: (.NET Core 3.1)
Operating system: Windows10
IDE: (Visual Studio 2019 16.7.2)

[smitpatel]

CrossSecurityUser does not seem to be part of any model. If it is not included in EF Core model then you will get exception.

[alfred-zaki]

@smitpatel it's the intermediate between Person and SecurityUser to avoid circular reference as each live on a different project/DLL, so CrossSecurityUser inherit SecurityUser and add one-to-one to Person.

[smitpatel]

@alfred-zaki - That is all fine but where in EF Core model are you registering it as an entity type? You also need to add it to EF as an entity type.

[alfred-zaki]

@smitpatel are you referring to IEntityTypeConfiguration ?
if that's the case then i have tried to create that with and without inheritance from IEntityTypeConfiguration

i either get that this need a PrimaryKey or i need to set it to HasNoKey which is not the case, or i get Invalid Column Name Discriminator !

this is basically the same exact table on SQL level

[smitpatel]

Yes, there is no IEntityTypeConfiguration for CrossSecurityUser. Without it (and looking at the code above), the type is not registered as entity type. Further, it is an hierarchy so you will need a discriminator column.

[alfred-zaki]

@smitpatel ok so i guess i was on the right track and drafted as i encountered a problem, if you please can guide on how to do that discriminator, if i am getting this correctly the difference between the two models are in CrossSecurityUser PersonId is not null

so on SQL level i have SecurityUser table which have a a Guid? PersonId column, if you have a value there that mean this security user have Person record with this Id, if not then no record, can you please help me express that or guide me on what to read.

[alfred-zaki]

@smitpatel will hit the bed now, thanks for all your help.

[AndriySvyryd]

#10140 is needed to map this scenario correctly

[alfred-zaki]

@smitpatel @AndriySvyryd thanks for responding!, so let me ask this in a different way, how can i have two classes in EF that map to the same table with one being the base class and the other being the same just with a couple of extra columns? what if we want each to live on a different project/dll?

[smitpatel]

@alfred-zaki - Without #10140, it is not possible unless you also add discriminator column to your table.

Though, I am not able to understand, how are the classes in first 2 DLLs are related to each other, why the second DLL not reference first one? The code you have shared has no multiple circular dependency issue.

[alfred-zaki]

@smitpatel yes the code doesn't cause i removed it by having that 3rd project/dll solution.

but originally what i wanted to achieve was simple, an optional one-to-one relationship between "Person" and "SecurityUser" each is living on a different DLL, to do that from both sides i need for project1 to reference project2 to use SecurityUser in Person model class and IEntityTypeConfiguration and the other way around, of course that will trigger circular reference and VS prevent it.

so i created a third project DLL and referenced both projects in it, started to build the "CorssSecurityUser" which is "SecurityUser" plus the foreign key column and ..... here i am

[AndriySvyryd]

See https://docs.microsoft.com/en-us/ef/core/modeling/owned-entities

The classes would look something like

    public class SecurityUser
    {
        public Guid SecurityUserId { get; set; }
        [Required]
        public string UserName { get; set; }
        [NotMapped]
        [Required]
        [DataType(DataType.Password)]
        public string Password { get; set; }
        public string Hash { get; set; }
        public string Salt { get; set; }
        public byte[] RowVersion { get; set; }
        public virtual List<SecurityGroupsUsers> SecurityGroups { get; set; }
        public virtual CrossSecurityUser CrossSecurityUser { get; set; }
    }

    public class CrossSecurityUser
    {
        public Guid SecurityUserId { get; set; }
        public Guid? PersonId { get; set; }
        [ForeignKey("PersonId")]
        public virtual Person Person { get; set; }
    }

And you'd configure it with modelBuilder.Entity<SecurityUser>().OwnsOne(u => u.CrossSecurityUser);

[alfred-zaki]

@AndriySvyryd Thank you for your suggestion, but it seems i will face the same circular reference situation if i want to do this from both sides.
and if i am going that route i can go from SecurityUser to Person directly without the need to have a midway class (CrossSecurityUser)

[ajcvickers]

@alfred-zaki We discussed this but we don't have any further advice to give, and we don't see anything actionable on the EF side. In the future, shadow navigations might help.

[alfred-zaki]

@ajcvickers Thanks for your feedback and consideration.
After many hours of banging my head into the wall I think I found a solution to make things act as i want, put them where I want and avoid circular reference, just one final check when I wakeup today 😄 .
Is it ok if I share my personal blog post about it here?

[ajcvickers]

@alfred-zaki Please share the link if you think it will help others.

[alfred-zaki]

here's my solution to the issue i encountered, hope it's useful to someone.
https://blog.alfredzaki.com/2020/09/ef-core-two-data-models-on-two-separate.html