The specified data type in the EF modelBuilder doesn't correspond to the one that is created

Question

I created database tables using EF and wanted to create foreign keys between FriendInvites.SenderUserId FriendInvites.cs:

public class FriendInvites
{
    [Key]
    public int Id { get; set; }

    //Foreign key to UserInfo userId
    public string SenderUserId { get; set; } = string.Empty;

    //Navigation property
    public UserInfo SenderId { get; set; }

    public string TargetUserId { get; set; } = string.Empty;

    public UserInfo TargetId { get; set; }
}

and UserInfo.UserId UserInfo.cs:

public class UserInfo
{
    [Key]
    public string UserId { get; set; } = string.Empty;

    [Required]
    public string UserName { get; set; } = string.Empty;


    //Navigation property to GroupInvites
    public ICollection<GroupInvites> GroupInvites;

    //Navigation property to GroupsCreatorsList
    public ICollection<GroupsCreatorsList> CreatorOfGroups;

    //Navigation property to GroupMemberList
    public ICollection<GroupMemberList> GroupMembers;

    public ICollection<FriendInvites> SenderUsers;

    public ICollection<FriendInvites> TargetUsers;
}

However, it didn't work as expected. I tried to create this foreign key in SQL Server Management Studio, but it also failed. Then I discovered that this was due to the data type mismatch; UserInfo.UserId was defined as nvarchar(450), while FriendInvites.SenderUserId was nvarchar(max). I changed the data type in SQL Server, which allowed me to create the foreign key successfully.

Subsequently, I attempted to explicitly specify the data type in the OnModelCreating() method:

modelBuilder.Entity<FriendInvites>()
    .Property(e => e.SenderUserId).HasColumnType("nvarchar(450)");

modelBuilder.Entity<FriendInvites>()
    .Property(e => e.TargetUserId).HasColumnType("nvarchar(450)");

But the column continued to be created as nvarchar(max). I'm unsure why this is happening, especially since everything worked perfectly before (I had created foreign keys in different tables that referenced the UserInfo.UserId primary key)

Here is example of other class that have foreign key to UserInfo.UserId column:

public class GroupsCreatorsList
{
    [Key]
    public Guid GroupId { get; set; }

    [Required]
    public string GroupName { get; set; } = string.Empty;

    //Foreighn key to UserInfo.UserId
    public string CreatorId { get; set; } = string.Empty;

    //Navigation property to UserInfo
    public UserInfo Creator { get; set; }

    //Navigation property to GroupInvites
    public ICollection<GroupInvites> GroupInvites { get; set; }

    //Navigation property to GroupMemberList
    public ICollection<GroupMemberList> GroupMembers { get; set; }
}

and here is setup of foreign key:

modelBuilder.Entity<UserInfo>()
    .HasMany(e => e.CreatorOfGroups)
    .WithOne(e => e.Creator)
    .HasForeignKey(e => e.CreatorId)
    .IsRequired();

here is how i set up foreign key FriendInvites.SenderUserId and FriendInvites.TargetUserId:

modelBuilder.Entity<FriendInvites>()
    .HasOne(e => e.SenderId)
    .WithMany(e => e.SenderUsers)
    .HasForeignKey(e => e.SenderUserId)
    .IsRequired();

modelBuilder.Entity<FriendInvites>()
    .HasOne(e => e.TargetId)
    .WithMany(e => e.TargetUsers)
    .HasForeignKey(e => e.TargetUserId)
    .IsRequired();

I tried .HasMaxLength(), .HasColumnType() methods, and also [Column(TypeName = "varchar(450)")], [MaxLength(450)] attributes, All of this didn't work

What you recommend me to do?

Answer 1

First if you are curious about why EF chose NVARCHAR(450), it's about max index length!

I faced this interesting issue before and I created an issue on EF core repo, you can check it here:

https://github.com/dotnet/efcore/issues/31167

In short, because for SQL Server max length for an index is 900 characters EF decides to set half of it as 450 chars, EF thinks maybe there is another composite index with this column. But you can change the length programmatically by using HasMaxLength.

You mentioned that you changed the database schema manually to set ForeignKey for table which is bad and will affect EF for handling migrations.

My Suggestion is, if possible, to revert recent migrations regarding these tables and try to create migration again by setting HasMaxLength on both Primary and Foreign keys.

Still, I don't like to set a string property as a Primary key from the performance point of view. so you can just set another long column as ID with IDENTITY(1,1) and just put a unique index on the username. again if it's possible based on your logic.