How to reference ASP.Net Identity User Email property as Foreign Key

Question

Trying to reference the Email property of an ASP.Net Identity User as a Foreign Key but keep getting an error message

using MVC6, EF7

I have an AppAccount which is the Primary model and the ApplicationUser: IdentityUser is the dependant.

I'm trying to set the Email property of the ApplicationUser as a foreign key the AppAccount model

public class AppAccount
{
    public string AppAccountID { get; set; }

    public string AccountType { get; set; }

    public DateTime DateCreated { get; set; }

    public virtual ApplicationUser AppUser { get; set; }

}


public class ApplicationUser : IdentityUser
{

    public string FirstName { get; set; }

    public string Surname { get; set; }

    public DateTime DOB { get; set; }

    public virtual AppAccount AppAccount { get; set; }

}

'Peeking' to the definition of the IdentityUser tells me the Email property is of type string...

public class IdentityUser<TKey> where TKey : IEquatable<TKey>
{
    ...
    //
    // Summary:
    //     Gets or sets the email address for this user.
    public virtual string Email { get; set; }
...
}

I have set the PK of the AppAccount Model to string and made the Email property of the ApplicationUser an Alternate key, then set a One-To-One relationship using fluent API...

builder.Entity<ApplicationUser>(au =>
            {
                au.HasAlternateKey(u => u.Email);
                au.HasAlternateKey(u => u.UserName);    
            });

        builder.Entity<AppAccount>(aa =>
        {
            aa.HasKey(a => a.AppAccountID);
            aa.HasOne(a => a.AppUser)
            .WithOne(u => u.AppAccount)
            .HasPrincipalKey<ApplicationUser>(u => u.Email);  // PK of AppAccount is FK of AppUser
        });

When I run the migration it works ok but when I try to update the database I get the following error

Error Number:1753,State:0,Class:16
Column 'AspNetUsers.Email' is not the same length or scale as 
referencing column 'AppAccount.AppAccountID' 
in foreign key 'FK_AppAccount_ApplicationUser_AppAccountID'. 
Columns participating in a foreign key relationship must 
be defined with the same length and scale.
Could not create constraint or index. See previous errors.

I have tried manually setting the maximum length of the AppAccountID and Email properties to the same limit

builder.Entity<ApplicationUser>(au =>
            {
                ...
                au.Property(u => u.Email).HasMaxLength(100);    
            });

builder.Entity<AppAccount>(aa =>
        {
            ...
            aa.Property(a => a.AppAccountID).HasMaxLength(100);
            ...
        });

I have tried setting both properties to the same type on the server...

builder.Entity<ApplicationUser>(au =>
            {
                ...
                au.Property(u => u.Email).ForSqlServerHasColumnType("nvarchar(100)");    
            });

builder.Entity<AppAccount>(aa =>
        {
            ...
            aa.Property(a => a.AppAccountID).ForSqlServerHasColumnType("nvarchar(100)");
            ...
        });

tried overriding the Email property in the ApplicationUser class to

public override string Email {get ; set ;}

and I tried setting the AppAccountID property of the AppAccount Model to virtual

`public virtual string AppAccountID {get ; set ;}

I think this may be a server issue but checking the database the Email column type is nvarchar, so I dont understand why it doesnt compile?

Answer 1

Apologies all, its amateur hour

Firstly I was writing HasPrincipleKey instead of HasForeignKey as pointed out by @TetsuyaYamamoto (Thankyou).

Secondly, after checking the database for the umpteenth time, the Email property for ApplicationUser is of type NVARCHAR(256), so updating the customisation as below allowed EF to compile the model successfully.

builder.Entity<AppAccount>(aa =>
        {
            aa.HasKey(a => a.AppAccountID);
            aa.Property(a => a.AppAccountID).ForSqlServerHasColumnType("NVARCHAR(256)");
            aa.HasOne(a => a.AppUser)
            .WithOne(u => u.AppAccount)
            .HasForeignKey<ApplicationUser>(u => u.Email);
        });

Thanks all...this is why you don't code at 1 in the morning