Entity Framework 5 code first cannot get model to work

Question

Tearing my hair out again on this one...using EF 5 Code First, building the model in code - compiles, so it is syntactically correct, but I get an exception when the code builds the model. Here is the entity class (I also have vaidation attributes, but have removed them here for readability):

[Table("USERS")]
public class User : IPEntity
{
    #region Constructor (needs to initialize list objects for related entities)

    public User()
    {
        this.Profiles = new List<Profile>();
        this.ProfileDivs = new List<ProfileDiv>();
        this.ProfileDepts = new List<ProfileDept>();
    }

    #endregion

    #region Entity properties and validation attributes

    [Key]
    public long UserId { get; set; }

    public long PclientId { get; set; }

    public string UserName { get; set; }

    public string UserDescription { get; set; }

    public long? EmpId { get; set; }

    public string MustChangePassword { get; set; }

    public long? FailedLogins { get; set; }

    public DateTime? LastLogin { get; set; }

    public long? SequenceNumber { get; set; }

    public string AllDivs { get; set; }

    public string AllDepts { get; set; }

    public string UserRole { get; set; }

    public DateTime? BeginSupport { get; set; }

    public DateTime? EndSupport { get; set; }

    public string OneTimeAccess { get; set; }

    public long? ClonedFromUser { get; set; }

    public string Email { get; set; }

    public string ResetEmail { get; set; }

    public DateTime? ResetTimeout { get; set; }

    public long? ChallengeFailures { get; set; }

    public string PermUserRole { get; set; }

    public DateTime? PasswordChangedDate { get; set; }

    public virtual ICollection<Profile> Profiles { get; set; }

    public virtual ICollection<ProfileDiv> ProfileDivs { get; set; }

    public virtual ICollection<ProfileDept> ProfileDepts { get; set; }

    public virtual WorkSession WorkSession { get; set; }

}

The model builder class is:

public class User_Map : EntityTypeConfiguration<User>
{
    public User_Map()
    {
        this.ToTable("USERS");

        this.HasKey(t => new { t.UserId });

        this.Property(t => t.UserId)
            .HasColumnName("USER_ID")
            .HasDatabaseGeneratedOption(DatabaseGeneratedOption.None)
            .IsRequired();

        this.Property(t => t.UserName)
            .HasColumnName("USERNAME")
            .IsRequired()
            .HasMaxLength(25);

        this.Property(t => t.PclientId)
            .HasColumnName("PCLIENT_ID");

        this.Property(t => t.EmpId)
            .HasColumnName("EMP_ID");

        this.Property(t => t.MustChangePassword)
            .HasColumnName("MUST_CHANGE_PASSWORD")
            .HasMaxLength(1);

        this.Property(t => t.UserDescription)
            .HasColumnName("USER_DESCRIPTION")
            .HasMaxLength(80);

        this.Property(t => t.FailedLogins)
            .HasColumnName("FAILED_LOGINS");

        this.Property(t => t.LastLogin)
            .HasColumnName("LAST_LOGIN");

        this.Property(t => t.SequenceNumber)
            .HasColumnName("SEQUENCE_NUMBER");

        this.Property(t => t.AllDivs)
            .HasColumnName("ALL_DIVS")
            .HasMaxLength(1);

        this.Property(t => t.AllDepts)
            .HasColumnName("ALL_DEPTS")
            .HasMaxLength(1);

        this.Property(t => t.UserRole)
            .HasColumnName("USER_ROLE")
            .HasMaxLength(2);

        this.Property(t => t.BeginSupport)
            .HasColumnName("BEGIN_SUPPORT");

        this.Property(t => t.EndSupport)
            .HasColumnName("END_SUPPORT");

        this.Property(t => t.OneTimeAccess)
            .HasColumnName("ONE_TIME_ACCESS")
            .HasMaxLength(1);

        this.Property(t => t.ClonedFromUser)
            .HasColumnName("CLONED_FROM_USER");

        this.Property(t => t.Email)
            .HasColumnName("EMAIL")
            .HasMaxLength(60);

        this.Property(t => t.ResetEmail)
            .HasColumnName("RESET_EMAIL")
            .HasMaxLength(60);

        this.Property(t => t.ResetTimeout)
            .HasColumnName("RESET_TIMEOUT");

        this.Property(t => t.ChallengeFailures)
            .HasColumnName("CHALLENGE_FAILURES");

        this.Property(t => t.PermUserRole)
            .HasColumnName("PERM_USER_ROLE")
            .HasMaxLength(2);

        this.Property(t => t.PasswordChangedDate)
            .HasColumnName("PASSWORD_CHANGED_DATE");

        this.HasOptional(t => t.WorkSession)
            .WithRequired(t => t.User);

        // TODO: This is syntactically correct but model blows up!

        this.HasMany(t => t.Profiles)
            .WithRequired(t => t.User)
            .HasForeignKey(t => t.UserId);

    }
}

When the model builder class' constructor executes, I get the following exception thrown on the line above (after the comment):

The expression 't => t.User' is not a valid property expression. 
The expression should represent a property: C#: 't => t.MyProperty'  
VB.Net: 'Function(t) t.MyProperty'.

The Profiles entity is very simple:

[Table("PROFILE")]
public class Profile : IPEntity
{
    [Key, Column(Order = 0)]
    [ForeignKey("UserId")]
    public long UserId { get; set; }

    [Key, Column(Order = 1)]
    public string FunctionalArea { get; set; }

    public int RightsId { get; set; }

    public User User;

}

I have been beating on this for two or three days, if anyone can spot my mistake, I would be MOST appreciative!

Thanks, Peter

UPDATE: I found one bone-headed mistake by reading this post, since I had declared something as a field and not a property...but now I get a different exception which I do not understand:

The navigation property 'UserId' is not a declared property on type 'Profile'. 
Verify that it has not been explicitly excluded from the model and that 
it is a valid navigation property.

This has me confused as UserId IS a declared property on Profile...?

SECOND UPDATE:

I understand the exception but (since there is no inner exception detail) cannot determine where it is coming from. I changed the User_map class to include:

        this.HasMany(t => t.Profiles)
            .WithRequired(t => t.User)
            .HasForeignKey(t => t.UserId)
            .WillCascadeOnDelete(false);

and the Profile_map class to include:

        this.HasRequired(t => t.User)
            .WithMany()
            .HasForeignKey(t => t.UserId)
            .WillCascadeOnDelete(false);

Since I have to work with the existing database, I am stuck with the property "UserId" being a foreign key in both tables (in User table it is foreign key for Profiles table, in Profiles table it is foreign key for User table.) I added the .WillCascadeOnDelete(false) to both in case there was some kind of circularity problem. Like I said, I cannot determine which map is blowing things up, the calls to both constructors pass without exception, it is only when the override for OnModelCreating in the context exits that the exception is thrown. Here is the override:

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {

        var mbProfile = new Profile_map();
        modelBuilder.Configurations.Add(mbProfile);

        var mbUser = new User_Map();
        modelBuilder.Configurations.Add(mbUser);

        base.OnModelCreating(modelBuilder);
    }

I know I'm still an EF newbie, but I cannot find the problem (well, haven't yet anyway...)

Thanks again.

Answer 1

Peter, with regard to your issue with the two "UserId" columns, you said that they were both foreign keys. I don't think you are correct. The User.UserId is marked with [Key] and it is the only one in the User class marked that way. It must be the primary id for the User table. On the Profile class, you have both UserId and FunctionalArea marked with [Key]. This means that the Profile table has a joint primary key, one of which is the foreign key to the User table's UserId column.

That design is perfectly fine. However, in order to have a relationship between User and Profile records where the Profile record is the parent, you would have to have TWO columns on your User table that reference the Profile table's two primary keys, and NEITHER of those columns could be the UserId column of the User table, otherwise you would have a circular dependency with no way of creating either record.

Bottom line is, you don't have two relationships between the User and Profile tables, just one. So delete the following block from your Profile_map class:

this.HasRequired(t => t.User)
    .WithMany()
    .HasForeignKey(t => t.UserId)
    .WillCascadeOnDelete(false);

That should fix it right up! ;)

Answer 2

public User User;

should be a property instead of a field - like all the others :)

public User User { get; set; }

With virtual preferably (to allow lazy loading), because you have marked all your navigation properties as virtual.

Edit about the UPDATE

The exception talks about a navigation property UserId and complains that it is not a "valid navigation property". Indeed it isn't a valid navigation property. The exception indicates that you possibly have used UserId (instead of User) in the WithRequired method. You should take a closer look at that.