ASP.NET Core Identity - illegal variable name/number when updating with UserManager

[Danny Nieruch]

• .NET & EF Core 2.2
• Devart.Data.Oracle.EFCore: 9.6.675

Hi,

I'm using ASP.NET Core Identity with devart dotConnect for Oracle. I'm getting the Oracle exception "ORA-01036: illegal variable name/number" exception when I try to update a user with Core Identity UserManager and I'm not sure how to track down the problem.

I have a custom application user:

Code: Select all

public class ApplicationUser : IdentityUser<long>
{
    [Column("NAME")]
    [StringLength(50)]
    public string FullName { get; set; }
}

And customized the DbContexts OnModelCreating:

Code: Select all

protected override void OnModelCreating(ModelBuilder builder)
{
    base.OnModelCreating(builder);
    [...]
    builder.Entity<ApplicationUser>(userConfig =>
    {
        userConfig.ToTable("USERS");

        userConfig.Ignore(u => u.LockoutEnabled);
        userConfig.Ignore(u => u.PhoneNumber);
        userConfig.Ignore(u => u.PhoneNumberConfirmed);
        userConfig.Ignore(u => u.AccessFailedCount);
        userConfig.Ignore(u => u.LockoutEnd);
        userConfig.Ignore(u => u.TwoFactorEnabled);
        userConfig.Property(u => u.Id).HasColumnName("ID");
        userConfig.Property(u => u.Email).HasColumnName("EMAIL").HasMaxLength(50);
        [...]

Whenever I use the UserManager from ASP.NET Core Identity to update a user (for example .UpdateAsync() or .AddPasswordAsync()), I get the "illegal variable name/number" ORA exception. For example:

Code: Select all

private readonly UserManager<ApplicationUser> _userManager;
[...]
public async void Test()
{
    [...]
    ApplicationUser user = await _userManager.FindByIdAsync(123);
    user.EmailConfirmed = true;
    user.SecurityStamp = Guid.NewGuid().ToString("D");
    [...]
    await _userManager.UpdateAsync(user);
    //await _userManager.AddPasswordAsync() does not work too
}

I've checked Visual Studios output and see the problem:

Code: Select all

Microsoft.EntityFrameworkCore.Database.Command:Error: Failed executing DbCommand (69ms)
[Parameters=[p9='?' (DbType = Decimal), p0='?' (Size = 36), p10='?', p1='?' (Size = 25), p2='?' (DbType = Decimal), p3='?' (Size = 12), p4='?' (Size = 25), p5='?' (Size = 2), p6='?' (Size = 84), p7='?' (Size = 36), p8='?' (Size = 2)], CommandType='Text', CommandTimeout='0']
UPDATE USERS SET CONCURRENCYSTAMP = :p0, EMAIL = :p1, EXTERN = :p2, NAME = :p3, EMAIL_NORMALIZED = :p4, NAME_NORMALIZED = :p5, PASSWORDHASH = :p6, SECURITYSTAMP = :p7, DBNAME = :p8
WHERE ID = :p9 AND CONCURRENCYSTAMP IS NULL

There are 11 parameters generated (p10 seems to be null) but the update query only uses 10 parameters. Of course this causes an ORA "illegal variable name/number" exception. But why?

I've also checked the DB query with Devarts dbMonitor:

Same result... I can see the 11 parameters but I don't know why p10 gets generated (and is NULL).

I can not find the root cause. Is something wrong with my code? Could this be a bug in the Devart .NET Core Package?
How can I find the cause for the not used parameter in the generated query?

[Danny Nieruch]

Okay I found the problem.

The root cause of the problem is the IdentityUsers ConcurrencyStamp property.
The exception "ORA-01036: illegal variable name/number" gets thrown when you try to update a user where the ConcurrencyStamp is NULL.

Updating a user creates a DB query likes this:

Code: Select all

UPDATE USERS SET [...] WHERE ID = :p9 AND CONCURRENCYSTAMP = :p10

The last parameter gets replaced with the current ConcurrenyStamp. When ConcurrenyStamp is null, you get the following query:

Code: Select all

UPDATE USERS SET [...] WHERE ID = :p9 AND CONCURRENCYSTAMP IS NULL

So the CONCURRENCYSTAMP p10 parameter gets generated in the background with a NULL value and is not used by the query, what causes the parameter mismatch.

TL;DR:
CONCURRENCYSTAMP in your IdentityUser table must have a value when users/rows are not created with the Identity UserManager.

[Shalex]

Thank you for your report and described workaround. We have reproduced the issue and will notify you about the results of our investigation.

[Shalex]

The bug with updating a null value of the concurrency check column with some non-null value in EF Core is fixed: viewtopic.php?f=1&t=38378.

[Danny Nieruch]

Thanks for your support/bugfix and the new build!