MVC Identity - The INSERT statement conflicted wit

问题:

I know this question has already been discussed a couple of times but this one relates to the built in Identity model.

I've created a custom model named "Company". When registering new users I want to select also the company name.

I've added the required data into the RegisterViewModel and the Views and the forms displays as it should. I already seeded 2 companies in the Company table so the value should not be null.

public class RegisterViewModel
{        
   ... Removed some irrelevant code ...

    [Required(ErrorMessage = "You must select a company name.")]
    [Display(Name = "Company")]
    public int CompanyID { get; set; }
    public virtual Company ApplicationUser_Company { get; set; }

The problem arises when I want to insert a new user with the following error:

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_dbo.AspNetUsers_dbo.Companies_CompanyID". The conflict occurred in database "aspnet-ProjectMed-20160327120257", table "dbo.Companies", column 'CompanyID'.
The statement has been terminated. 

Checking the account controller I cannot see any problem:

    // GET: /Account/Register
    [AllowAnonymous]
    public ActionResult Register()
    {
        ViewBag.CompanyID = new SelectList(db.Companies, "CompanyID", "Company_Name");
        return View();
    }

    //
    // POST: /Account/Register
    [HttpPost]
    [AllowAnonymous]
    [ValidateAntiForgeryToken]
    public async Task<ActionResult> Register(RegisterViewModel model)
    {
        if (ModelState.IsValid)
        {
            var user = new ApplicationUser { UserName = model.Email, Email = model.Email, ApplicationUser_FirstName = model.ApplicationUser_FirstName, ApplicationUser_LastName = model.ApplicationUser_LastName, ApplicationUser_Company = model.ApplicationUser_Company };

                var result = await UserManager.CreateAsync(user, model.Password); <<<---- This is where the error occurs <<<---
                if (result.Succeeded)
                {
                    await SignInManager.SignInAsync(user, isPersistent: false, rememberBrowser: false);

                    return RedirectToAction("Index", "Home");
                }
                AddErrors(result);

        }

        // If we got this far, something failed, redisplay form
        return View(model);
    }

I am also copying the SQL tables:

AspNetUsers table:

CREATE TABLE [dbo].[AspNetUsers] (
    [Id]                        NVARCHAR (128) NOT NULL,
    [ApplicationUser_FirstName] NVARCHAR (50)  NOT NULL,
    [ApplicationUser_LastName]  NVARCHAR (50)  NOT NULL,
    [CompanyID]                 INT            NOT NULL,
    [Email]                     NVARCHAR (256) NULL,
    [EmailConfirmed]            BIT            NOT NULL,
    [PasswordHash]              NVARCHAR (MAX) NULL,
    [SecurityStamp]             NVARCHAR (MAX) NULL,
    [PhoneNumber]               NVARCHAR (MAX) NULL,
    [PhoneNumberConfirmed]      BIT            NOT NULL,
    [TwoFactorEnabled]          BIT            NOT NULL,
    [LockoutEndDateUtc]         DATETIME       NULL,
    [LockoutEnabled]            BIT            NOT NULL,
    [AccessFailedCount]         INT            NOT NULL,
    [UserName]                  NVARCHAR (256) NOT NULL,
    CONSTRAINT [PK_dbo.AspNetUsers] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [FK_dbo.AspNetUsers_dbo.Companies_CompanyID] FOREIGN KEY ([CompanyID]) REFERENCES [dbo].[Companies] ([CompanyID]) ON DELETE CASCADE
);

GO
CREATE NONCLUSTERED INDEX [IX_CompanyID]
    ON [dbo].[AspNetUsers]([CompanyID] ASC);

GO
CREATE UNIQUE NONCLUSTERED INDEX [UserNameIndex]
    ON [dbo].[AspNetUsers]([UserName] ASC);

Companies table:

CREATE TABLE [dbo].[Companies] (
    [CompanyID]          INT            IDENTITY (1, 1) NOT NULL,
    [Company_Name]       NVARCHAR (50)  NOT NULL,
    [Company_Code]       NVARCHAR (9)   NOT NULL,
    [Company_Address1]   NVARCHAR (100) NOT NULL,
    [Company_Address2]   NVARCHAR (100) NULL,
    [Company_PostalCode] NVARCHAR (15)  NOT NULL,
    [Company_City]       NVARCHAR (50)  NOT NULL,
    [CountryID]          INT            NOT NULL,
    [Company_CLOG]       BIT            NOT NULL,
    [Company_SubAgent]   BIT            NOT NULL,
    CONSTRAINT [PK_dbo.Companies] PRIMARY KEY CLUSTERED ([CompanyID] ASC),
    CONSTRAINT [FK_dbo.Companies_dbo.Countries_CountryID] FOREIGN KEY ([CountryID]) REFERENCES [dbo].[Countries] ([CountryID]) ON DELETE CASCADE
);


GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_Company_Code]
    ON [dbo].[Companies]([Company_Code] ASC);


GO
CREATE NONCLUSTERED INDEX [IX_CountryID]
    ON [dbo].[Companies]([CountryID] ASC);

Any ideas and what's wrong here?

回答1:

Gut is telling me it's within this line (broken up for clarity/commentary):

var user = new ApplicationUser
{
  UserName = model.Email,
  Email = model.Email,
  ApplicationUser_FirstName = model.ApplicationUser_FirstName,
  ApplicationUser_LastName = model.ApplicationUser_LastName,
  ApplicationUser_Company = model.ApplicationUser_Company      // <--
};

Basically, when passing entity references between form actions, you want to reference IDs (as whole entities don't travel across the wire, nor should they for security purposes (malformed input, hacking, etc.)).

Your ViewModel (in this case RegisterViewModel should really only have CompanyID (which the is the PK of Company entity). That should then be used to populate the new user ApplicationUser (user) by performing a lookup then assignment to user. e.g.

[HttpPost]
public async Task<ActionResult> Register(RegisterViewModel model)
{
  // verify the models' valid
  if (ModelState.IsValid)
  {
    // Perform a lookup to retrieve the selected company
    var company = db.COmpanies.SingleOrDefault(x => x.CompanyID == model.CompanyID);
    if (company != null) // Found it
    {
      // No go on to establish an application user
      var user = new ApplicationUser {
        /* ... */,
        ApplicationUser_Company = company // entity we retrieved
      };

      //
      // SignInManager yatta yata
      //
    }
  }
  return View(model); // Uh-oh fallback.
}

Note though, if your ApplicationUser entity has two properties (most do), one for the ID and one for the foreign relationship; e.g.

public int CompanyID { get; set; } // ID property
[ForeignKey("CompanyID")]
public virtual Company Company { get; set; } // entity property

You only have to assign either/or. In this case, you don't need to perform a lookup before populating ApplicationUser and can simply set user.CompanyID = model.CompanyID;

回答2:

It seems I've found the error thanks to your last suggestion! I was trying to set the value of the foreign key instead if the ID...

The code that worked for me:

[HttpPost]
public async Task<ActionResult> Register(RegisterViewModel model)
{
  // verify the models' valid
  if (ModelState.IsValid)
  {
      var user = new ApplicationUser {
        /* ... */,
        CompanyID = model.CompanyID // entity we retrieved
      };

      //
      // SignInManager yatta yata
      //
  }
  return View(model); // Uh-oh fallback.
}