AddToRoleAsync is throwing strange SQL error

Question

In my ASP.NET MVC Core 1.1 app, the AddToRoleAsync call in the last line of the following code is throwing the error: The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION. This is kind of odd since I don't have any other transaction running on SQL Server or anywhere else. I'm aware of this T-SQL error in general. But I'm not clear why the AddToRoleAsync call below is throwing this error. Please note the following code did successfully create a user before it threw the error in the next line of code.

List<String> usersList = GetAllUsers();

foreach (string s in usersList)
{
    var user = new ApplicationUser { UserName = s, UserRole = "TestRole" };
    var result = await _userManager.CreateAsync(user, "testpassword");

    if (result.Succeeded)
    {
       var result_1 = await _userManager.AddToRoleAsync(user, "TestRole");
    }
}

Did you ever figure this out? I'm experiencing the same thing. What's interesting is I'm only getting this error on my Azure deployment - I get no error in my local dev environment. — ih303, Mar 31 '17 at 15:14
@ih303 If I recall correctly after closing and restarting the project it started working. — nam, Mar 31 '17 at 18:02

score 0 · Answer 1 · answered Apr 08 '17 at 22:03

Microsoft changed the structure of [AspNetRoles] and others when using .Net Core apps. Now when you adding a new role statement to the [AspNetRoles], you should declare the Role NormalizedName as well. For instance:

var role = "Manager";

var newRole = new IdentityRole()
{
    Name = role,
    NormalizedName = role.ToUpper() // Mandatory.
};

//! Adding the new Role into database and saving
await _apiDbContext.Roles.AddAsync(newRole);
await _apiDbContext.SaveChangesAsync();

Setting the NormalizedName role variable to upper-case, will let you using: AddToRoleAsync without any strange errs.

var currentUser = await _userManager.FindByEmailAsync(user.Email);
await _userManager.AddToRoleAsync(currentUser, "Manager");

_userManager belongs to UserManager<YourIdentityUser>

AddToRoleAsync is throwing strange SQL error

1 Answers1