.NET Core 2保存已存在Organization时触发SqlException错误求助

Hey there, let's get this sorted out! That SqlException is popping up because Entity Framework is trying to re-insert an existing Organization record when you associate it with a new user. Since the Recno column is an identity (auto-increment) field, SQL Server blocks explicit inserts when IDENTITY_INSERT is off (which is the default behavior).

What's Going Wrong in Your Code? #

When you fetch an existing Organization with existingOrg.First(), you pull the record into your context—but when you assign it to user.Organization and call _userManager.CreateAsync(user), EF gets confused. It thinks you're trying to add a new Organization along with the user, so it attempts to insert the existing record's Recno value explicitly, triggering the error.

For new organizations, this doesn't happen because EF recognizes the Organization is a fresh entity (you created it with new Organization()), so it lets SQL Server generate the identity value automatically.

Solution 1: Use Foreign Key Directly (Recommended) #

If your ApplicationUser class has a foreign key property for Organization (like OrganizationRecno, matching the Recno column in Organization), skip assigning the navigation property and just set the foreign key value. This tells EF exactly to link to the existing organization without trying to reinsert it.

Modify your code like this:

[HttpPost]
[AllowAnonymous]
[ValidateAntiForgeryToken]
public async Task<IActionResult> Register(RegisterViewModel model, string returnUrl = null)
{
    ViewData["ReturnUrl"] = returnUrl;
    if (ModelState.IsValid)
    {
        int? existingOrgRecno = null;
        var existingOrg = _context.Organization.Where(m => m.OrganizationName == model.Organization);
        if (existingOrg.Any())
        {
            existingOrgRecno = existingOrg.First().Recno;
        }

        var user = new ApplicationUser 
        {
            UserName = model.Email,
            Email = model.Email,
            FirstName = model.FirstName,
            LastName = model.LastName,
            AddToConstantContact = model.AddToConstantContact,
            OrganizationRecno = existingOrgRecno, // Use the foreign key here
            CityId = "1"
        };

        // Create new organization if it doesn't exist
        if (existingOrgRecno == null)
        {
            var newOrg = new Organization { OrganizationName = model.Organization };
            _context.Organization.Add(newOrg);
            await _context.SaveChangesAsync();
            user.OrganizationRecno = newOrg.Recno;
        }

        var result = await _userManager.CreateAsync(user, model.Password);
        if (result.Succeeded)
        {
            _logger.LogInformation("User created a new account with password.");
            var code = await _userManager.GenerateEmailConfirmationTokenAsync(user);
            var callbackUrl = Url.EmailConfirmationLink(user.Id.ToString(), code, Request.Scheme);
            await _emailSender.SendEmailConfirmationAsync(model.Email, callbackUrl);
            await _signInManager.SignInAsync(user, isPersistent: false);
            _logger.LogInformation("User signed in after creating account.");
            return RedirectToLocal(returnUrl);
        }
        AddErrors(result);
    }
    // If we got this far, something failed, redisplay form
    return View(model);
}

Solution 2: Tell EF the Organization Is Already Saved #

If you prefer to keep using the navigation property, explicitly inform EF that the existing Organization is already in the database (so it shouldn't try to insert it):

[HttpPost]
[AllowAnonymous]
[ValidateAntiForgeryToken]
public async Task<IActionResult> Register(RegisterViewModel model, string returnUrl = null)
{
    ViewData["ReturnUrl"] = returnUrl;
    if (ModelState.IsValid)
    {
        Organization currentOrg = null;
        var existingOrg = _context.Organization.Where(m => m.OrganizationName == model.Organization);
        if (existingOrg.Any())
        {
            currentOrg = existingOrg.First();
            // Explicitly mark the organization as unchanged in the context
            _context.Entry(currentOrg).State = EntityState.Unchanged;
        }
        else
        {
            currentOrg = new Organization { OrganizationName = model.Organization };
        }

        var user = new ApplicationUser 
        {
            UserName = model.Email,
            Email = model.Email,
            FirstName = model.FirstName,
            LastName = model.LastName,
            AddToConstantContact = model.AddToConstantContact,
            Organization = currentOrg,
            CityId = "1"
        };

        var result = await _userManager.CreateAsync(user, model.Password);
        if (result.Succeeded)
        {
            _logger.LogInformation("User created a new account with password.");
            var code = await _userManager.GenerateEmailConfirmationTokenAsync(user);
            var callbackUrl = Url.EmailConfirmationLink(user.Id.ToString(), code, Request.Scheme);
            await _emailSender.SendEmailConfirmationAsync(model.Email, callbackUrl);
            await _signInManager.SignInAsync(user, isPersistent: false);
            _logger.LogInformation("User signed in after creating account.");
            return RedirectToLocal(returnUrl);
        }
        AddErrors(result);
    }
    // If we got this far, something failed, redisplay form
    return View(model);
}

Why These Work #

• Solution 1 avoids confusion by using the foreign key directly—EF knows to only link the user to the existing organization without modifying the Organization table.
• Solution 2 explicitly tells EF the Organization entity is already persisted, so it only creates the user and their association, not a duplicate organization.

内容的提问来源于stack exchange，提问作者James Huang