Asked  10 Months ago    Answers:  5   Viewed   188 times

I am trying to use Guid's instead of strings for my primary key and have followed the following posts: How to change type of id in Microsoft.AspNet.Identity.EntityFramework.IdentityUser and How to change type of id in Microsoft.AspNet.Identity.EntityFramework.IdentityUser

I updated to the latest prerelease packages of aspnet identity

Microsoft ASP.NET Identity Core 2.0.0-beta1

Microsoft ASP.NET Identity EntityFramework 2.0.0-beta1

and edited my User to allow for Guid's instead of the default string, I then created my own dbContext and usermanager, however every time I try to login I get the following error:

System.Data.SqlClient.SqlException: Operand type clash: uniqueidentifier is incompatible with int

for this line:

var user = await UserManager.FindAsync(model.UserName, model.Password);

I have checked to make sure that all the fields in the database are definitely uniqueidentifiers and I'm not sure what to try next, below is the code I am currently using:

User objects:

public class GuidRole : IdentityRole<Guid, GuidUserRole>
    {
        public GuidRole()
        {
            Id = Guid.NewGuid();
        }
        public GuidRole(string name) : this() { Name = name; }
    }
    public class GuidUserRole : IdentityUserRole<Guid> { }
    public class GuidUserClaim : IdentityUserClaim<Guid> { }
    public class GuidUserLogin : IdentityUserLogin<Guid> { }

    public class User : IdentityUser<Guid, GuidUserLogin, GuidUserRole, GuidUserClaim>
    {
        public User()
        {
            Id = Guid.NewGuid();
        }

        public User(string name) : this() { UserName = name; }

        public string FirstName { get; set; }
        public string LastName { get; set; }
    }

dbContext:

public class newDbContext : IdentityDbContext<User, GuidRole, Guid, GuidUserLogin, GuidUserRole, GuidUserClaim>
{
    public newDbContext()
        : base(nameOrConnectionString: "defaultConnection") { }

    public newDbContext(string connectionString)
        : base(nameOrConnectionString: connectionString) { }

    static newDbContext()
    {
        Database.SetInitializer<newDbContext>(null);
    }
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        // Use singular table names
        base.OnModelCreating(modelBuilder);


        modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();

        modelBuilder.Entity<User>().ToTable("User").Property(p => p.Id).HasColumnName("UserID");
        modelBuilder.Entity<User>().Property(p => p.Email).HasColumnName("EmailAddress");

        modelBuilder.Entity<GuidUserRole>().HasKey(r => new { r.RoleId, r.UserId });
        modelBuilder.Entity<GuidUserRole>().ToTable("UserRole");
        modelBuilder.Entity<GuidUserRole>().Property(r => r.UserId).HasColumnName("UserID");
        modelBuilder.Entity<GuidUserRole>().Property(r => r.RoleId).HasColumnName("RoleID");

        modelBuilder.Entity<GuidUserLogin>().ToTable("UserLogin");
        modelBuilder.Entity<GuidUserLogin>().Property(r => r.UserId).HasColumnName("UserID");

        modelBuilder.Entity<GuidUserClaim>().ToTable("UserClaim");
        modelBuilder.Entity<GuidUserClaim>().Property(r => r.Id).HasColumnName("UserClaimID");

        modelBuilder.Entity<GuidRole>().HasKey<Guid>(r => r.Id);
        modelBuilder.Entity<GuidRole>().ToTable("Role");
        modelBuilder.Entity<GuidRole>().Property(r => r.Id).HasColumnName("RoleID");

        Configuration.ProxyCreationEnabled = false;
        Configuration.LazyLoadingEnabled = false;
    }

}

and finally the user manager:

public class ApplicationUserManager : UserManager<User, Guid>
{
    public ApplicationUserManager(string connectionString)
        : base(new UserStore<User, GuidRole, Guid, GuidUserLogin, GuidUserRole, GuidUserClaim>(new newDbContext()))
    {

        UserValidator = new UserValidator<User, Guid>(this) { AllowOnlyAlphanumericUserNames = false };
    }
}

 Answers

2

Thanks to Hao Kung's comment I individually went through the table and property mappings until I got to the UserClaims table. Turns out I had the field type set to uniqueidentifier in the database, however this still needed to be an int. Changing it fixed the problem!

Saturday, August 14, 2021
 
1

Its because it is trying to load child objects and it may be creating some circular loop that will never ending( a=>b, b=>c, c=>d, d=>a)

you can turn it off only for that particular moment as following.So dbcontext will not load customers child objects unless Include method is called on your object

  db.Configuration.ProxyCreationEnabled = false;
  User ma = db.user.First(x => x.u_id == id);
  return Json(ma, JsonRequestBehavior.AllowGet);
Thursday, August 12, 2021
 
rsk82
 
2

i found the way to create the user manager on the repository layer:

            var roleStore = new RoleStore<IdentityRole>(context);
            var roleManager = new RoleManager<IdentityRole>(roleStore);
            var userStore = new UserStore<ApplicationUser>(context);
            var userManager = new UserManager<ApplicationUser>(userStore);               
            var user = new ApplicationUser { UserName = "sallen" };

            userManager.Create(user, "password");                    
            roleManager.Create(new IdentityRole { Name = "admin" });
            userManager.AddToRole(user.Id, "admin");
Thursday, August 26, 2021
 
anky
 
2

MySql Connector documentation states that from version 5.2 of .NET connector they treat GUID's as BINARY(16) not VARCHAR(40).

Since current MySQL dialect in nhibernate doesn't updated to reflect this change (actually an issue is prepared) you need to manually convert these fields to BINARY(16) after nhibernate generate the schema.

Saturday, October 2, 2021
 
4

I was unable to make Microsoft's AspNet.Identity to work with postgresql, it seems to be designed to work with SQL Server and doesn't acknowledge the presence of a different data context configuration no matter what you do.

I have everything working now with the aid of an external library so I will provide an answer to my question in case someone else runs into this problem.

I followed the instructions present in the following project (credit to vincechan):

  • PostgreSQL.AspNet.Identity.EntityFramework on github.com

Step 2 was a bit problematic. After importing the project into my solution I had to resolve reference issues in it. In the Package Manager Console you can Install the missing packages and/or update the ones that are outdated. My final configuration required using:

  • Npgsql 3.2.2 on my main project (using EntityFramework6.Npgsql)
  • Npgsql 2.2.7 on the downloaded project (using Npgsql.EntityFramework)

Once you have everything ready, add a new migration (Add-Migration <migration-name) and update your database (Update-Database).

Now execute the SQL script that creates Identity related tables, they will not be created automatically like the do with SQL Server. The script is included in the project (file PostgreSQLIdentity.sql).

Everything should work now. Forgive the poor formatting of this answer.

Sunday, October 17, 2021
 
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :  
Share