Why is my MVC action occasionally giving me a "Violation of PRIMARY KEY constraint" error?

Question

I have an Index action in my MVC project that accepts an integer as a parameter which represents the ID of a movie title in my SQL Server database. If the ID is already in the database, a ViewModel is populated with the movie title's data and passed back to the view. If the ID is a new one, a new movie Title object is created, populated, added to the context, and saved to the database. The ID is not automatically assigned by the database. Action code below (some code removed/omitted for brevity and clarity):

    [HttpGet]
    public async Task<ActionResult> Index(int? i)
    {
        if (i.HasValue)
        {
            var movieViewModel = new MovieViewModel();
            using (var context = new someEntities())
            {
                //check if this id already exists in the DB
                var matchedTitle = await context.Titles.FindAsync(i.Value);
                if (matchedTitle != null && matchedTitle.Category == "movie")
                {                     
                    //matchedTitle already in the DB.. 
                    //..so populate movieViewModel with matchedTitle
                }
                else
                {
                    //matchedTitle not in the DB, generate a new Title item
                    var newTitle = TitleHelper.GenerateNewTitle(i.Value, /* .. */);                     
                    //add and save
                    context.Titles.Add(newTitle);                        
                    await context.SaveChangesAsync();                        
                    //code omitted for brevity
                }
            }
            Response.StatusCode = (int)HttpStatusCode.OK;
            return View(movieViewModel);
        }
        else
        {
            return View();
        }
    }

This works fine but will occasionally produce the following error during high traffic:

Violation of PRIMARY KEY constraint 'PK_Title_Title_Id'. Cannot insert duplicate key in object 'dbo.Title'. The duplicate key value is (0147852). The statement has been terminated.

I have not been able to reproduce the error on purpose. If I hit a refresh on that view, the error goes away and the view loads normally. Am I missing a check or is this a race condition scenario? I would have thought that EF would be able to handle this.

Update:

Some additional info as requested in the comments:

• How do I generate IDs?

  They IDs are generated based on a movie's respective IMDb ID.

• How do I generate a new title?

  public static Title GenerateNewTitle(int id, //other properties)
  {           
      Title newTitle = new Title
      {
          //property population here
      }
      return newTitle;
  }       
  

Answer 1

This was solved a while ago but seeing that no answer has been provided as of yet I provide a solution below for future reference.

The problem was, as suggested by Damien_The_Unbeliever in the comments of the OP, a typical race condition due to semi-complete, untested code of a quick'n'dirty solution (error 2627), albeit only occurring during high traffic. Checking if the object already exists before inserting is not a solution, unless you lock the table prior to the check. Otherwise, someone might insert the object after the check and before the insertion.

I'm currently overriding OnException of System.Web.Mvc.Controller to handle my exceptions, but "JFDI" is a quick good way to go about this:

using System.Data.Entity.Infrastructure;

try
{
    await context.SaveChangesAsync();
}
catch(DbUpdateException ex)
{
    var sqlException = ex.InnerException.InnerException as SqlException;
    if (sqlException != null && sqlException.Number == 2627)
    {
        //violation of primary key constraint has occurred here, act accordingly
        //e.g. pass the populated viewmodel back to the view and return
    }
}

Answer 2

In-case of edit in where category = show, will throw an error because var matchedTitle = await context.Titles.FindAsync(i.Value) will find a record for i.value id, but matchedTitle.Category == "movie" won't match as this is a type of show category, and TitleHelper.GenerateNewTitle(/* .. */) this function must be assigning PK ID from i.value only and hence it will throw PK violation error.

Try bellow code

    var newTitle = TitleHelper.GenerateNewTitle(i.Value, /* .. */);
    if(newTitle.Title_Id > 0)
{
 // Query to update your record
}
else
{
  //add and save
  context.Titles.Add(newTitle);                        
  await context.SaveChangesAsync();
}