Base solution for your next web application
Open Closed

Catching duplicate key exception with UnitOfWork #2633

User avatar
0
pkut created

Hello,

I'm trying to insert some database record and catch duplicate key exception. Actually it is not primary key but unique index defined for the table. The example of code looks like this:

public void DoSomething()
{
    ...
    var record=new MyRecord {....};
    try
    {
        repository.InsertAsync(record);
    }
    catch (DbUpdateException e) when ((e?.InnerException?.InnerException as System.Data.SqlClient.SqlException)?.Number == 2601){
        record=repository.FirstOrDefault(...some condition here...);
    }
    --Some more database code comes here and finally I'm saving another peace of data.
}

And if my code contains some more data changes later, it cannot be saved. It looks like faulty MyRecord instance still waiting in the DbContext and even I've catched the exception, it will try to save it again on the next SaveChanges attempt. How should I correctly manage this situation? Should I manually define two unit of work - one to insertion attempt and the secod for the rest of the code? Thank you in advance.

Best regards Pavel.

Go to accepted answer
6 Answer(s)
  • User Avatar
    0
    ismcagdas created
    Support Team

    Hi,

    Is this method DoSomething in an app service ? If so, it is unit of work by default and all changes will be saved at the end of your method, this is not under your control by default.

    But for this specific case, you can disable UoW for this method , see <a class="postlink" href="http://aspnetboilerplate.com/Pages/Documents/Unit-Of-Work#DocDisablingUow">http://aspnetboilerplate.com/Pages/Docu ... sablingUow</a>. Then start a unit of work in your method by injecting IUnitOfWorkManager and then call SaveChanges on current unitofwork (probably _unitOfWorkManager.Current.SaveChanges()) right after this line

    repository.InsertAsync(record);

    .

    In this way, you should be able to catch the exception like you want.

  • User Avatar
    0
    pkut created

    Hi.

    We did exactly as you said, injected IUnitOfWorkManager and manually started UnitOfWork. But the problem is not directly with UOW, the problem actually will be the same if you will manually work with DbContext. When you need to add new record you do:

    var myNewRecord=new MyRecord {  ....fill some data....}
context.MyRecordsTable.Add(myNewRecord);
try{
    context.SaveChanges();
}
catch (...database exception to handle failure....)
{
    //Catching unique constraint failure
}

    When you fail on saving changes the myNewRecord will still be in the context and if you need to add another items to the database, you have to remove it someway - recreate DbContext or detach myNewRecord from context.

    Now in AspNetBoilerplate actual DbContext is hidden, so how should I resolve the same situation ?

  • User Avatar
    0
    hikalkan created
    Support Team

    Hi,

    As you already see, this is a common problem with DbContext. So, you should change the code pattern you are using. There may be different solutions.

    I would manually check database if given key does exists (with a single additional query). But if you want that should be done by database constraint, then the most simple way can be creating an inner unit of work (which creates an inner dbcontext). So, when you have exception then you can re-create a new unit of work. Example code:

    public void DoSomething()
{
    var record=new MyRecord {....};
    try
    {
        SaveRecord(record); //calls inner UOW method
    }
    catch (DbUpdateException e) when ((e?.InnerException?.InnerException as System.Data.SqlClient.SqlException)?.Number == 2601){
        record=repository.FirstOrDefault(...some condition here...);
    }
    --Some more database code comes here and finally I'm saving another peace of data.
}

[UnitOfWork(TransactionScopeOption.RequiresNew)]
protected virtual SaveRecord(Record record) {
 repository.InsertAsync(record); //no need to save changes since it will be automatic
}

    This may work, but we strictly suggest you to query database first and don't rely on try-cactch and database level unique check.

  • User Avatar
    0
    pkut created

    Hi,

    Yes, we querying database first, but sometimes it's not enough and insert attempt fail on database constraint. So creating inner unit of work seems to be a solution, but - as far as I remember, documentation says that inner UOW uses the same connection and other DB related stuff as outer UOW. Looks like my understanding is a bit wrong here, please explain the details of how inner UOW works and how can I be sure that we have new DbContext.

    Best regards Pavel

  • User Avatar
    0
    tteoh created

    <cite>pkut: </cite> Hi,

    Yes, we querying database first, but sometimes it's not enough and insert attempt fail on database constraint. So creating inner unit of work seems to be a solution, but - as far as I remember, documentation says that inner UOW uses the same connection and other DB related stuff as outer UOW. Looks like my understanding is a bit wrong here, please explain the details of how inner UOW works and how can I be sure that we have new DbContext.

    Best regards Pavel

    Hi Support,

    I have the same understanding as Pavel. Could you please kindly clarify his question on inner UoW.

    Thanks. /tommy

  • User Avatar
    0
    hikalkan created
    Support Team

    You can use TransactionScopeOption.RequiresNew while creating a new UOW using IUnitOfWorkManager:

    using (var uow = _unitOfWorkManager.Begin(TransactionScopeOption.RequiresNew))
{
    //use repositories here. they will use a different (new) dbcontext

    uow.Complete();
}
Assignee
No one
Labels
Non yet