Code First Invalid Column Name SecondaryUser_Id

Question

I have two tables called User and UserSession, there are two relationships between these two tables:

UserSession.User -> User.UserSession

UserSession.SecondaryUser -> User.UserSessions

A UserSession will always have a user, but may not have a SecondaryUser.

I am attempting to map these relationships using Code First Fluent, below are my current mappings and the class definitions:

UserSessionMap:

        public UserSessionMap()
    {
        // Primary Key
        this.HasKey(t => t.UserId);

        // Properties
        this.Property(t => t.UserId)
            .HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);

        // Table & Column Mappings
        this.ToTable("UserSession");
        this.Property(t => t.UserId).HasColumnName("UserId");
        this.Property(t => t.SecondaryUserId).HasColumnName("SecondaryUserId");
        this.Property(t => t.RoleNameCompanyInfo).HasColumnName("RoleNameCompanyInfo");
        this.Property(t => t.RecordCreate).HasColumnName("RecordCreate");
        this.Property(t => t.RecordUpdate).HasColumnName("RecordUpdate");

        // Relationships
        this.HasRequired(t => t.User).WithRequiredPrincipal(p => p.UserSession);
        this.HasOptional(t => t.SecondaryUser).WithMany(p => p.UserSessions).HasForeignKey(p=>p.SecondaryUserId);

    }

User Map:

    public class UserMap : EntityTypeConfiguration<User>
{
    public UserMap()
    {
        // Primary Key
        this.HasKey(t => t.Id);

        // Properties
        ...snip...

        // Table & Column Mappings
        this.ToTable("User");
        this.Property(t => t.Id).HasColumnName("Id");
        ...snip...
    }
}

UserSession entity:

    public partial class UserSession
{
    public int UserId { get; set; }
    [ForeignKey("SecondaryUser")]
    public int? SecondaryUserId { get; set; }
    public string RoleNameCompanyInfo { get; set; }
    public DateTime RecordCreate { get; set; }
    public DateTime RecordUpdate { get; set; }

    public virtual User User { get; set; }
    
    public virtual User SecondaryUser { get; set; }
}

User entity:

    public partial class User
{
    public User()
    {
        this.Carts = new List<Cart>();
        this.DirectDispenses = new List<DirectDispense>();
        this.Downloads = new List<Download>();
        this.MatchQuestionSessions = new List<MatchQuestionSession>();
        this.Orders = new List<Order>();
        this.UserSessions = new List<UserSession>();
        this.UserUserRoles = new List<UserUserRole>();
    }

    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public int CompanyId { get; set; }
    public string Email { get; set; }
    public string Password { get; set; }
    public string PasswordSalt { get; set; }
    public string Role { get; set; }
    public bool Active { get; set; }
    public string ColibriUserId { get; set; }
    public Nullable<int> DispenserId { get; set; }
    public Nullable<System.Guid> PasswordResetCode { get; set; }
    public Nullable<System.DateTime> PasswordResetSent { get; set; }
    public System.DateTime RecordUpdate { get; set; }
    public System.DateTime RecordCreate { get; set; }
    public virtual ICollection<Cart> Carts { get; set; }
    public virtual Company Company { get; set; }
    public virtual ICollection<DirectDispense> DirectDispenses { get; set; }
    public virtual ICollection<Download> Downloads { get; set; }
    public virtual ICollection<MatchQuestionSession> MatchQuestionSessions { get; set; }
    public virtual ICollection<Order> Orders { get; set; }
    public virtual ICollection<UserSession> UserSessions { get; set; }
    public virtual UserSession UserSession { get; set; }
    public virtual ICollection<UserUserRole> UserUserRoles { get; set; }
}

I have also tried adding [ForeignKey("SecondaryUserId") to the SecondaryUser property without luck.

Basically, both the UserId and SecondaryUserId properties should map to the Id property of User. The error EF is generating is:

System.Data.SqlClient.SqlException: Invalid column name 'SecondaryUser_Id'.

What I think it is doing is looking for a property called SecondaryUser_Id on the User table, which obviously isn't there. How can I tell it to look at the Id property on the User object. All of the articles I have found show how to map PKs to FKs that are not named how EF expects, this appears to be a case of the opposite for which I can find no examples.

There seem to be a lot of similar issues with this same error on SO but none of those solutions have worked for me and this situation appears to be different (as I say, this seems to be the opposite of the most common cause of this error).

Answer 1

The solution is to add the missing column in the table. Simple.

Answer 2

After spending a day maddeningly looking at this and even going so far as completely removing the secondaryUser property, I was still getting the same issue. Starting a new project gave me the solution.

Within the project there is a partial class of User which has a SecondaryUser property which is essentially a proxy for the SecondaryUser property on UserSession, it was this property that EF was trying to map.

I added the [NotMapped] attribute to this property and this error has gone away.

Lesson learnt-check your partials!