I am trying to access the UserRoles property from my ApplicationUser entity and I keep getting an error message stating Invalid column name 'UserId1'. I am following this Microsoft article (.0#add-all-navigation-properties), but Entity Framework keeps trying to duplicate all of my columns. I'm not sure what's going on.

ApplicationUser:

public class User : IdentityUser
{
    public ICollection<IdentityUserClaim<string>> Claims { get; set; }
    public ICollection<IdentityUserLogin<string>> Logins { get; set; }
    public ICollection<IdentityUserToken<string>> Tokens { get; set; }
    public ICollection<UserRole> UserRoles { get; set; }
}

Context:

public class SqlContext : IdentityDbContext<User, Role, string,
IdentityUserClaim<string>, UserRole, IdentityUserLogin<string>,
IdentityRoleClaim<string>, IdentityUserToken<string>>
{
    public SqlContext(DbContextOptions<SqlContext> options) : base(options)
    {
    }

    protected override void OnModelCreating(ModelBuilder builder)
    {
        builder.Entity<User>(b =>
        {
            // Each User can have many UserClaims
            b.HasMany(e => e.Claims)
                .WithOne()
                .HasForeignKey(uc => uc.UserId)
                .IsRequired();

            // Each User can have many UserLogins
            b.HasMany(e => e.Logins)
                .WithOne()
                .HasForeignKey(ul => ul.UserId)
                .IsRequired();

             // Each User can have many UserTokens
             b.HasMany(e => e.Tokens)
                .WithOne()
                .HasForeignKey(ut => ut.UserId)
                .IsRequired();

            // Each User can have many entries in the UserRole join table
            b.HasMany(e => e.UserRoles)
                .WithOne(e => e.User)
                .HasForeignKey(ur => ur.UserId)
                .IsRequired();
        });

        builder.Entity<Role>(b =>
        {
            // Each Role can have many entries in the UserRole join table
            b.HasMany(e => e.UserRoles)
                .WithOne(e => e.Role)
                .HasForeignKey(ur => ur.RoleId)
                .IsRequired();
        });
        base.OnModelCreating(builder);
    }
}

Program.cs:

builder.Services.AddIdentity<User, Role>()
    .AddEntityFrameworkStores<SqlContext>()
    .AddDefaultTokenProviders()
    .AddClaimsPrincipalFactory<CustomClaimsPrincipalFactory>();

I am trying to access the UserRoles property from my ApplicationUser entity and I keep getting an error message stating Invalid column name 'UserId1'. I am following this Microsoft article (https://learn.microsoft/en-us/aspnet/core/security/authentication/customize-identity-model?view=aspnetcore-8.0#add-all-navigation-properties), but Entity Framework keeps trying to duplicate all of my columns. I'm not sure what's going on.

ApplicationUser:

public class User : IdentityUser
{
    public ICollection<IdentityUserClaim<string>> Claims { get; set; }
    public ICollection<IdentityUserLogin<string>> Logins { get; set; }
    public ICollection<IdentityUserToken<string>> Tokens { get; set; }
    public ICollection<UserRole> UserRoles { get; set; }
}

Context:

public class SqlContext : IdentityDbContext<User, Role, string,
IdentityUserClaim<string>, UserRole, IdentityUserLogin<string>,
IdentityRoleClaim<string>, IdentityUserToken<string>>
{
    public SqlContext(DbContextOptions<SqlContext> options) : base(options)
    {
    }

    protected override void OnModelCreating(ModelBuilder builder)
    {
        builder.Entity<User>(b =>
        {
            // Each User can have many UserClaims
            b.HasMany(e => e.Claims)
                .WithOne()
                .HasForeignKey(uc => uc.UserId)
                .IsRequired();

            // Each User can have many UserLogins
            b.HasMany(e => e.Logins)
                .WithOne()
                .HasForeignKey(ul => ul.UserId)
                .IsRequired();

             // Each User can have many UserTokens
             b.HasMany(e => e.Tokens)
                .WithOne()
                .HasForeignKey(ut => ut.UserId)
                .IsRequired();

            // Each User can have many entries in the UserRole join table
            b.HasMany(e => e.UserRoles)
                .WithOne(e => e.User)
                .HasForeignKey(ur => ur.UserId)
                .IsRequired();
        });

        builder.Entity<Role>(b =>
        {
            // Each Role can have many entries in the UserRole join table
            b.HasMany(e => e.UserRoles)
                .WithOne(e => e.Role)
                .HasForeignKey(ur => ur.RoleId)
                .IsRequired();
        });
        base.OnModelCreating(builder);
    }
}

Program.cs:

builder.Services.AddIdentity<User, Role>()
    .AddEntityFrameworkStores<SqlContext>()
    .AddDefaultTokenProviders()
    .AddClaimsPrincipalFactory<CustomClaimsPrincipalFactory>();

• asp-core
• asp-identity
• asp-core-8

1 Answer 1

I assume you don't delete the previous migration file and reuse the previous table, if you are creating a new identity project and modify it.

I suggest you could consider deleting previous migration file and regenerate the migration by using below command to re-generate the migration file.

dotnet cli:

dotnet ef migrations add InitialCreate

dotnet ef database update

package management console:

Add-Migration InitialCreate
Update-Database

And the generate result for this is as below:

My applicationdbcontext and application user as below:

public class ApplicationDbContext
    : IdentityDbContext<
        ApplicationUser, ApplicationRole, string,
        IdentityUserClaim<string>, ApplicationUserRole, IdentityUserLogin<string>,
        IdentityRoleClaim<string>, IdentityUserToken<string>>
{
    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
        : base(options)
    {
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        modelBuilder.Entity<ApplicationUser>(b =>
        {
            // Each User can have many UserClaims
            b.HasMany(e => e.Claims)
                .WithOne()
                .HasForeignKey(uc => uc.UserId)
                .IsRequired();

            // Each User can have many UserLogins
            b.HasMany(e => e.Logins)
                .WithOne()
                .HasForeignKey(ul => ul.UserId)
                .IsRequired();

            // Each User can have many UserTokens
            b.HasMany(e => e.Tokens)
                .WithOne()
                .HasForeignKey(ut => ut.UserId)
                .IsRequired();

            // Each User can have many entries in the UserRole join table
            b.HasMany(e => e.UserRoles)
                .WithOne(e => e.User)
                .HasForeignKey(ur => ur.UserId)
                .IsRequired();
        });

        modelBuilder.Entity<ApplicationRole>(b =>
        {
            // Each Role can have many entries in the UserRole join table
            b.HasMany(e => e.UserRoles)
                .WithOne(e => e.Role)
                .HasForeignKey(ur => ur.RoleId)
                .IsRequired();
        });

    }
}

Application user:

    public class ApplicationUser : IdentityUser
    {
        public virtual ICollection<IdentityUserClaim<string>> Claims { get; set; }
        public virtual ICollection<IdentityUserLogin<string>> Logins { get; set; }
        public virtual ICollection<IdentityUserToken<string>> Tokens { get; set; }
        public virtual ICollection<ApplicationUserRole> UserRoles { get; set; }
    }

    public class ApplicationRole : IdentityRole
    {
        public virtual ICollection<ApplicationUserRole> UserRoles { get; set; }
    }

    public class ApplicationUserRole : IdentityUserRole<string>
    {
        public virtual ApplicationUser User { get; set; }
        public virtual ApplicationRole Role { get; set; }
    }

Program.cs:

// Add services to the container.
var connectionString = builder.Configuration.GetConnectionString("DefaultConnection") ?? throw new InvalidOperationException("Connection string 'DefaultConnection' not found.");
builder.Services.AddDbContext<ApplicationDbContext>(options =>
    options.UseSqlServer(connectionString));
builder.Services.AddDatabaseDeveloperPageExceptionFilter();

builder.Services.AddDefaultIdentity<ApplicationUser>(options => options.SignIn.RequireConfirmedAccount = true)
    .AddEntityFrameworkStores<ApplicationDbContext>();
builder.Services.AddControllersWithViews();

login partial:

@using Microsoft.AspNetCore.Identity
@using MVCIdentitynavigation.Data
@inject SignInManager<ApplicationUser> SignInManager
@inject UserManager<ApplicationUser> UserManager