CODEWITHSUNDEEP
c#sql-serverentity-framework
joseph.c
joseph.c

Reputation: 156

Avoid duplicate insert to database

I have the following code that inserts an entity with random generated value "hawb" as one of the column. My idea was since the hawb is a unique column in the table, the try-catch block will catch the exception when duplicate value is being inserted. The workflow works fine until I start testing it and get some weird error.

[Code]
hawb = "0402135505536";
while (!uniqueHawb)  //insert new hawb
{
    //hawb = $"{DateTime.Now:MMddHHmmss}{RandomHelper.GetRandomNumber(0, 999):000}";

    var entity = new HawbAsset { HAWB = hawb, HawbStatus = "Allocated", AllocatedDateTime = DateTime.Now, AllocationReference = reference };

    try
    {
        _repository.Insert(entity);
        uniqueHawb = true;
    }
    catch (Exception e)
    {
        ;
    }
    hawb = $"{DateTime.Now:MMddHHmmss}{RandomHelper.GetRandomNumber(0, 999):000}";
}

[Model]
    public class HawbAsset : BaseEntity
    {
        [Required(AllowEmptyStrings = false)]
        [Index(IsUnique = true)]
        [StringLength(15)]
        public string HAWB { get; set; }

        [Required(AllowEmptyStrings = false)]
        public string HawbStatus { get; set; }

        public DateTime? AllocatedDateTime { get; set; }

        [Required(AllowEmptyStrings = false)]
        public string AllocationReference { get; set; }

        public DateTime? ConfirmedUsageDateTime { get; set; }

        public DateTime? RecycledDateTime { get; set; }

        public string Owner { get; set; }
    }

I have hardcoded a duplicate value 0402135505536 so it will catch the "Duplicate Key Exception" the first time, which is expected. However, when the hawb been randomly generated, and I have make sure the value is different, it still catches the same "Duplicate Key Exception". Can anyone tell me what is going on and how to achieve my goal? Thanks!

Upvotes: 0

Views: 546

Answers (3)

Khaled Sameer
Khaled Sameer

Reputation: 306

Maybe you can use nanoseconds to make big distance between records to reduce possibility of duplication

entity.HAWB = $"{DateTime.Now.Ticks}";
// 636898603227146583

DateTime.Ticks resolution is 100 nanoseconds

Upvotes: 1

Steve Py
Steve Py

Reputation: 34653

The issue you are seeing is because you tried inserting one entity with the duplicate ID and that failed. On the retry, you are creating a 2nd entity and trying to insert that.. The first entity is still associated to the context and will still try to save. You need to detach it from the context before saving the new replacement, or update the existing entity. (below)

hawb = "0402135505536";
var entity = new HawbAsset { HAWB = hawb, HawbStatus = "Allocated", AllocatedDateTime = DateTime.Now, AllocationReference = reference };
while (!uniqueHawb)  //insert new hawb
{
    //hawb = $"{DateTime.Now:MMddHHmmss}{RandomHelper.GetRandomNumber(0, 999):000}";

    try
    {
        _repository.Insert(entity);
        uniqueHawb = true;
    }
    catch (Exception e)
    {
        ;
    }
    entity.HAWB = $"{DateTime.Now:MMddHHmmss}{RandomHelper.GetRandomNumber(0, 999):000}";
}

To detach an entity you need to use context.Entity(entity).State = EntityState.Detached; which would need to operate through your repository boundary.

Ideally though, it would be better to check the uniqueness of the HAWB before attempting the insert, but still handling the exception for those very, very rare cases that an entry gets saved between the check and the save:

int retryCount = 0
while (retryCount < 5)
{
  try
  {
    bool isUnique = false;
    string hawb = null;
    while(!isUnique)
    { 
      hawb = generateHawb();
      isUnique = context.HawbAssets.Any(x => x.HAWB == hawb);
    }
    entity.HAWB = hawb; // this hawb should be unique, so set and insert.
    _repository.Insert(entity);
  }
  catch(UpdateException)
  {
  // log that this has happened, check inner exception for duplicate key and retry, though limit retry attempts if there are deeper issues that might lock up the system in a retry loop.
    retryCount++;
  }
}

Upvotes: 3

Programnik
Programnik

Reputation: 1543

Its 1 line of extra code to check if the entity exists. Do that first and dont rely on error handling to do the work:

if ( _repository.HAWBAssets.FirstOrDefault(i => i.HAWB == hawb)== null)
    {
     _repository.Insert(entity);
        uniqueHawb = true;
    }

Upvotes: 1

Related Questions