0

I have an EF Core model defined as:

namespace TestApp.DataAccess.Models {
    public class Candidate
    {
        public int CandidateId { get; set; }
        public Guid UniqueKey { get; set; }
        public string Name { get; set; }
        public virtual List<Job> Jobs { get; set; }
    }

    public class Job
    {
        public int JobId { get; set; }
        public Guid UniqueKey { get; set; }

        public int CandidateId { get; set; }
        public virtual Candidate Candidate { get; set; }

        public string Title { get; set; }
    }
}

Where CandidateId and JobId are primary keys.

Both entities also have a UniqueKey property which is a Guid. This is generated by our client and posted to our API in the request body. We should never end up with more than one Job with the same UniqueKey property.

Ideally this should be enforced by the database with a unique constraint but this isn't currently in place.

Instead, in our controller we check if the UniqueKey already exists. If it doesn't we create a new Job. If it does, then we update the existing record:

foreach (var jobModel in model.Jobs) {
    //Check if the job already exists for the entity
    var jobEntity = candidate.Jobs.FirstOrDefault(x => x.UniqueKey == jobModel.UniqueKey);

    //If not, then create it
    if (jobEntity == null) {
        jobEntity = new Job { UniqueKey = jobModel.UniqueKey };
        candidate.Jobs.Add(jobEntity);
    }

    jobEntity.Title = jobModel.Title;
    //...
}

Recently, I've started to see duplicated Jobs:

JobId         CandidateId         Title         UniqueKey
201           100                 Teacher       4177b6da-7a4c-4032-b13d-8e3e2d2aeaca
202           100                 Teacher       4177b6da-7a4c-4032-b13d-8e3e2d2aeaca

This is not something I thought possible with the above code. Before applying the unique constraint to the underlying SQL database, I'm trying to understand how this is happening to ensure I'm not simply plastering over a bigger issue.

What could cause these two records to be created? Could it be something to do with duplicated requests hitting our API endpoint at the same time?

This is the complete code sample:

namespace TestApp.DataAccess.Models {
    public class Candidate
    {
        public int CandidateId { get; set; }
        public Guid UniqueKey { get; set; }
        public string Name { get; set; }
        public virtual List<Job> Jobs { get; set; }
    }

    public class Job
    {
        public int JobId { get; set; }
        public Guid UniqueKey { get; set; }

        public int CandidateId { get; set; }
        public virtual Candidate Candidate { get; set; }

        public string Title { get; set; }
    }
}

namespace TestApp.Api.Controllers {
    [Route("api/[controller]")]
    public class CandidateController : BaseController {
        public ServerApplicationContext _context { get; set; }

        public CandidateController(ServerApplicationContext context) {
            _context = context;
        }

        public async Task<Candidate> findEntityOrDefault(Guid key) {

            if(entity == null) {
                return null;
            }


        }
        [HttpPost]
        public async Task<IActionResult> Post([FromBody]CandidateViewModel model) {

            //Load the existing entity
            var candidate = await _context.Candidates.FirstOrDefaultAsync(x => x.UniqueKey == key);

            //If we don't find a candidate then create one
            if(entity == null) {
                //...
                //candidate = new Candidate { ... }
                //...

            } else {
                //Else load in child properties for the existing candidate

                candidate.Jobs = await _context.Jobs.Where(x => x.CandidateId == entity.CandidateId).ToListAsync();

                return entity;
            }

            //Add jobs from the model to our entity
            foreach (var jobModel in model.Jobs) {
                //Check if the job already exists for the entity
                var jobEntity = candidate.Jobs.FirstOrDefault(x => x.UniqueKey == jobModel.UniqueKey);

                //If not, then create it
                if (jobEntity == null) {
                    jobEntity = new Job { UniqueKey = jobModel.UniqueKey };
                    candidate.Jobs.Add(jobEntity);
                }

                jobEntity.Title = jobModel.Title;
                //...
            }


            //Add or update the candidate to the DB
            if (candidate.CandidateId == 0)
                _context.Add(candidate);
            else
                _context.Update(candidate);

            //Commit changes
            await _context.SaveChangesAsync();

            var viewModel = Mapper.Map<CandidateViewModel>(candidate);
            return new ObjectResult(viewModel);
        }        
    }
}
Joseph
  • 2,706
  • 6
  • 42
  • 80
  • Sounds like as you've speculated - might be that multiple requests are hitting the controller at the same time. The thing is, if you put a unique constraint in at least the controller will throw and you can see the conditions of the exception. – Charleh Jun 17 '19 at 15:21
  • @Charleh Thanks. How does it handle multiple requests hitting the controller? Are the requests async? And therefore is EF not thread safe across requests? – Joseph Jun 17 '19 at 15:26
  • @joseph Yes EF is not thread safe. These are few links that are really helpful in your case. https://stackoverflow.com/questions/19754368/thread-safe-entity-framework-6, https://stackoverflow.com/questions/6126616/is-dbcontext-thread-safe – Aparna Gadgil Jun 18 '19 at 11:03

0 Answers0