Code First with existing database one to one mapping column error

Question

I have an existing database and it contains these two table below:

I am trying to create a EF Code First with database from scratch using fluent mapping.

I have the following dbContext configured:

public partial class EFContext : DbContext
{
    public EFContext()
        : base("name=DbContext")
    {
    }

    public virtual DbSet<Users> Users { get; set; }
    public virtual DbSet<Log> Log { get; set; }
    public virtual DbSet<Token> Tokens { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Configurations.Add(new UserConfiguration());
        modelBuilder.Configurations.Add(new LogConfiguration());
        modelBuilder.Configurations.Add(new TokenConfiguration());
    }
}

    public partial class Users
    {

        public int UserId { get; set; }
        public string Username { get; set; }
        public string Password { get; set; }
        public int Active { get; set; }
        public DateTime RegDate { get; set; }
        public virtual Token Token { get; set; }
     }



     public class Token
     {   
        public string TokenId { get; set; }
        public int UserId { get; set; }
        public string TokenValue { get; set; }
        public int Active { get; set; }
        public DateTime Fecalt { get; set; }
        public virtual Users User { get; set; }
     }

public class UserConfiguration : EntityTypeConfiguration<Users>
{
    public UserConfiguration() : base()
    {
        HasKey(p => p.UserId);

        Property(e => e.Username)
            .IsUnicode(false)
            .IsRequired()
            .HasMaxLength(50);

        Property(e => e.Password)
             .IsUnicode(false)
             .IsRequired()
             .HasMaxLength(50);

        Property(a => a.Active).IsRequired();
        Property(d => d.RegDate).IsRequired();
        HasOptional(u => u.Token).WithRequired(u => u.User);
    }
}

 public class TokenConfiguration: EntityTypeConfiguration<Token>
    {
        public TokenConfiguration()
        {
            HasKey(p => p.TokenId);
            Property(p => p.TokenId).HasMaxLength(50);
            Property(p => p.TokenValue).HasColumnName("Token").IsRequired().HasMaxLength(500);
            Property(p => p.Active).IsRequired();
            Property(p => p.Fecalt).IsRequired();
            ToTable("Tokens");
        }
    }

I have the following exception:

Invalid column name 'User_UserId'.\r\nInvalid column name 'User_UserId'.\r\nInvalid column name 'User_UserId'."

The generated query is this (obviously wrong):

SELECT [Extent1].[UserId] AS [UserId], [Extent1].[Username] AS [Username], [Extent1].[Password] AS [Password], [Extent1].[Active] AS [Active], [Extent1].[RegDate] AS [RegDate], [Extent3].[TokenId] AS [TokenId], [Extent3].[UserId] AS [UserId1], [Extent3].[Token] AS [Token], [Extent3].[Active] AS [Active1], [Extent3].[Fecalt] AS [Fecalt], [Extent3].[User_UserId] AS [User_UserId] FROM [dbo].[Users] AS [Extent1] LEFT OUTER JOIN [dbo].[Tokens] AS [Extent2] ON [Extent1].[UserId] = [Extent2].[User_UserId] LEFT OUTER JOIN [dbo].[Tokens] AS [Extent3] ON [Extent1].[UserId] = [Extent3].[User_UserId]

The query is the following:

 var query = from p in efContext.Users
                       .Include( p =>p.Token)
                        select p;

The foreign key is not well assigned and the left join is repeated but I dont know how to fix it.

The relationship is in user:

 HasOptional(u => u.Token).WithRequired(u => u.User);

The registry user is 1 to 0..1, A user token is optional, and the PK/FK relationship is UserID.

Answer 1

One way to achieve is Remove UserId property from the class Token and configure the relationship inside the TokenConfiguration instead of UserConfiguration calling MapKey method to specify the foreign key explicitly.

public class Token
{
    public string TokenId { get; set; }
    public string TokenValue { get; set; }
    public int Active { get; set; }
    public DateTime Fecalt { get; set; }
    public virtual Users User { get; set; }
}

public class UserConfiguration : EntityTypeConfiguration<Users>
{
    public UserConfiguration()
        : base()
    {
        HasKey(p => p.UserId);

        Property(e => e.Username)
            .IsUnicode(false)
            .IsRequired()
            .HasMaxLength(50);

        Property(e => e.Password)
             .IsUnicode(false)
             .IsRequired()
             .HasMaxLength(50);

        Property(a => a.Active).IsRequired();
        Property(d => d.RegDate).IsRequired();

    }
}

public class TokenConfiguration : EntityTypeConfiguration<Token>
{
    public TokenConfiguration()
    {
        HasKey(p => p.TokenId);
        Property(p => p.TokenId).HasMaxLength(50);
        Property(p => p.TokenValue).HasColumnName("Token").IsRequired().HasMaxLength(500);
        Property(p => p.Active).IsRequired();
        Property(p => p.Fecalt).IsRequired();
        HasRequired(d => d.User).WithOptional(d => d.Token).Map(m => m.MapKey("UserId"));
        ToTable("Tokens");
    }
}

It worked perfectly with my code.