Entity Framework can't understand foreign key and trying add “_Id” column to query

[pavelkatkovsky]

Hello,
I am trying to map two table through one.
Here is my db structure:

Here is my models:

public class Notification
    {
        public Notification()
        {
            Accounts = new HashSet<Account>();
        }
        public Guid Id { get; set; }
        public NotificationType NotificationType { get; set; }
        public virtual ICollection<Account> Accounts { get; set; }
        public virtual NotificationModelRequest NotificationModelRequest { get; set; }
    }

    public class NotificationModelRequest
    {
        public Guid NotificationId { get; set; }
        public Guid ModelId { get; set; }
        public Guid AccountId { get; set; }
        public virtual Notification Notification { get; set; }
        public virtual Model Model { get; set; }
        public virtual Account Account { get; set; }
    }
    public partial class Model
    {
        public Guid id { get; set; }
        public virtual NotificationModelRequest NotificationModelRequest { get; set; }
    }

    public partial class Account
    {
         public Account()
        {
            Notifications = new HashSet<Notification>();
        }
        public Guid id { get; set; }
        public virtual NotificationModelRequest NotificationModelRequest { get; set; }
    }

Here is my mappings:

        public NotificationConfiguration()
        {
            ToTable("Notification");
            HasKey(x => x.Id);
            Property(x => x.Id)
                .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

            Property(x => x.NotificationType);

            HasMany(e => e.Accounts)
                .WithMany(c => c.Notifications)
                .Map(m =>
                {
                    m.MapLeftKey("NotificationId");
                    m.MapRightKey("AccountId");
                    m.ToTable("AccountNotifications");
                });

            Ignore(x => x.ModelName);
            Ignore(x => x.CreatedBy);

            HasRequired(x => x.NotificationModelRequest).WithRequiredPrincipal(x => x.Notification);
        }

        public NotificationModelRequestConfiguration()
        {
            ToTable("NotificationModelRequest");
            HasKey(x => x.NotificationId);

            Property(t => t.ModelId).HasColumnName("ModelId");
            Property(t => t.AccountId).HasColumnName("AccountId");

            HasRequired(x => x.Model).WithMany().HasForeignKey(x => x.ModelId);
            HasRequired(x => x.Account).WithMany().HasForeignKey(x => x.AccountId);
        }

        public AccountConfiguration()
        {
            ToTable("Account");
            HasKey(x => x.id);
        }

        public ModelConfiguration()
        {
            ToTable("Model");
            HasKey(x => x.id);
        }

When i am trying make include the nested properties i have error:

        public async Task<List<Notification>> GetNotificationByAccountId(Guid accountId)
        {
            var query = GetQuery()
                .Include(x => x.NotificationModelRequest)
                .Include(x => x.NotificationModelRequest.Model)
                .Include(x => x.NotificationModelRequest.Account)
                .Include(x => x.Accounts);

            var testQuery = _context.Set<NotificationModelRequest>()
                .Include(x => x.Model)
                .Include(x => x.Account);
            var test = testQuery.FirstOrDefault();

            return await query.ToListAsync();
        }

Msg 207, Level 16, State 1, Line 15
Invalid column name 'NotificationModelRequest_NotificationId'.
Msg 207, Level 16, State 1, Line 37
Invalid column name 'NotificationModelRequest_NotificationId'.

Could you please help how to i have to write this mapping?
Thanks.

[ajcvickers]

@pavelkatkovsky I was not able to compile the code posted. After making appropriate changes to make the code compile I was unable to reproduce the issue you are seeing--my code is below. Can you please post a runnable project/solution or complete code listing that demonstrates the behavior you are seeing?

namespace SizTwoRider
{
    public class NotificationType
    {
        public Guid Id { get; set; }

        public virtual Notification Notification { get; set; }
    }

    public class Notification
    {
        public Guid Id { get; set; }

        public virtual NotificationType NotificationType { get; set; }
        public virtual ICollection<Account> Accounts { get; set; } = new HashSet<Account>();
        public virtual NotificationModelRequest NotificationModelRequest { get; set; }
    }

    public class NotificationModelRequest
    {
        public Guid NotificationId { get; set; }
        public virtual Notification Notification { get; set; }
        
        public Guid ModelId { get; set; }
        public virtual Model Model { get; set; }
        
        public Guid AccountId { get; set; }
        public virtual Account Account { get; set; }
    }

    public class Model
    {
        public Guid Id { get; set; }

        public virtual NotificationModelRequest NotificationModelRequest { get; set; }
    }

    public class Account
    {
        public Guid Id { get; set; }

        public virtual ICollection<Notification> Notifications { get; set; } = new HashSet<Notification>();
        public virtual NotificationModelRequest NotificationModelRequest { get; set; }
    }

    public class NotificationConfiguration : EntityTypeConfiguration<Notification>
    {
        public NotificationConfiguration()
        {
            ToTable("Notification");
            
            HasKey(x => x.Id);
            
            Property(x => x.Id)
                .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

            HasMany(e => e.Accounts)
                .WithMany(c => c.Notifications)
                .Map(m =>
                {
                    m.MapLeftKey("NotificationId");
                    m.MapRightKey("AccountId");
                    m.ToTable("AccountNotifications");
                });

            HasRequired(x => x.NotificationModelRequest).WithRequiredPrincipal(x => x.Notification);

            HasRequired(x => x.NotificationType).WithRequiredDependent(x => x.Notification);
        }
    }

    public class NotificationModelRequestConfiguration : EntityTypeConfiguration<NotificationModelRequest>
    {
        public NotificationModelRequestConfiguration()
        {
            ToTable("NotificationModelRequest");

            HasKey(x => x.NotificationId);

            Property(t => t.ModelId).HasColumnName("ModelId");
            Property(t => t.AccountId).HasColumnName("AccountId");

            HasRequired(x => x.Model).WithMany().HasForeignKey(x => x.ModelId);
            HasRequired(x => x.Account).WithMany().HasForeignKey(x => x.AccountId);
        }
    }

    public class AccountConfiguration : EntityTypeConfiguration<Account>
    {
        public AccountConfiguration()
        {
            ToTable("Account");
            HasKey(x => x.Id);
        }
    }

    public class ModelConfiguration : EntityTypeConfiguration<Model>
    {
        public ModelConfiguration()
        {
            ToTable("Model");
            HasKey(x => x.Id);
        }
    }

    public class SizTwoContext : DbContext
    {
        static SizTwoContext()
        {
            Database.SetInitializer(new DropCreateDatabaseAlways<SizTwoContext>());
        }

        public SizTwoContext()
            : base(@"Server=(localdb)\mssqllocaldb;Database=SixTwo;ConnectRetryCount=0")
        {
        }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Configurations.AddFromAssembly(typeof(SizTwoContext).Assembly);
        }
    }

    public class Program
    {
        public static void Main()
        {
            using (var context = new SizTwoContext())
            {
                Console.WriteLine($"Starting at {DateTime.Now}...");

                var query = context.Set<Notification>()
                    .Include(x => x.NotificationModelRequest)
                    .Include(x => x.NotificationModelRequest.Model)
                    .Include(x => x.NotificationModelRequest.Account)
                    .Include(x => x.Accounts);

                var testQuery = context.Set<NotificationModelRequest>()
                    .Include(x => x.Model)
                    .Include(x => x.Account);
                
                var test = testQuery.FirstOrDefault();

                var results = query.ToList();

                Console.WriteLine("Done!");
            }
        }
    }
}

[ajcvickers]

EF Team Triage: Closing this issue as the requested additional details have not been provided and we have been unable to reproduce it.

BTW this is a canned response and may have info or details that do not directly apply to this particular issue. While we'd like to spend the time to uniquely address every incoming issue, we get a lot traffic on the EF projects and that is not practical. To ensure we maximize the time we have to work on fixing bugs, implementing new features, etc. we use canned responses for common triage decisions.