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);
}
}
}