Base solution for your next web application
Open Closed

Insert a record into the database and read them in the same transaction #9783

User avatar
0
Leonardo.Willrich created

Hi,

I am trying to import records from a file and I do have separated files for each entity. So, first I want to insert all master records, and next insert all details record, however, when I try to read the master from the DB using EF, it is not working.

Basically, I would like it be working:

_typeRepository.Insert(new Type()
{
    Type = "test",
});

var typed = _typeRepository.GetAll().Where(a => a.Type == "test").FirstOrDefault();

In this example, typed is always null.

In the SQL Server Management Studio, If I create a transaction, insert a new record and perform a select, the record will be there.

How can I use the same transaction to performance the GetAll command?

Thanks in advance!

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

    Hi @Leonardo.Willrich

    You can call await CurrentUnitOfWork.SaveChangesAsync(); if you are doing this in a Controller or in an AppService. Otherwise, you can inject IUnitOfWorkManager and call await _unitOfWorkManager.Current.SaveChangesAsync();

  • User Avatar
    0
    Leonardo.Willrich created

    Hi @ismcagdas,

    I am aware about that. But, the point is that I don't want to commit my changes into the database yet. If something is wrong next, I want to rollback my transaction (raising an exception in this case). If I call SaveChanges, it will be persisted straightaway in the database and there is no way to revert that, only doing it manually by delete command instead of using the rollback on the transaction created by the CurrentUnitOfWork.

    Any other solution for that?

    The most strange thing is, in another routine where I am importing a record from a spreadsheet, I have a loop that create the entity, assign the fields and insert the new entity and get the ID for that. In the begin, I have a validation to check if the name already exist in the database just to avoid duplicated records. And in this method it is working like I wish. Has the function InsertAndGetId() a different behaviour?

    e.g.:

    name = spreadsheet[row, colName];
While (name != '')
{
    var person = _personRepository.GetAll().Where(p => p.name == name).FirstOrDefault();
    if (person != null)
    {
        AddError("Person already exists: " + name);
    }

    var record = new person();
    record.name = name;
    
    record.Id = _personRepository.InsertAndGetId(person);
    
    row++;
    name = spreadsheet[row, colName];
}

    So, in this example above, it seems to be reading the person insert previously, even though I haven't used SaveChanges to commit the records inserted.

    Thank you in advanced!

  • User Avatar
    0
    ismcagdas created
    Support Team

    Hi @Leonardo.Willrich

    No, if there is an error, the transaction must be rolled back even if you call SaveChanges. Have you trid it that way ? If so, could you share your entire AppService method ?

  • User Avatar
    0
    BigCDogCrew created

    The code sample above looks like it may have a logical error. If person is not null, then you are trying to insert that person into the repository again? And if person is null, then you are trying to insert a null into the repository? I don't understand.

    Maybe this line below is supposed to insert the new record rather than old person?

    record.Id = _personRepository.InsertAndGetId(person);

    should be

    record.Id = _personRepository.InsertAndGetId(record);

    If that's the case, I think you only need to capture the output if you intend to use the new Id immediately for some cross-referenced object. You don't have to reset record.Id = record.Id. For example...

    var newId = _personRepository.InsertAndGetId(record);
callLog.PersonId = newId;
_callLogRepository.Update(callLog);
  • User Avatar
    0
    Leonardo.Willrich created

    Hi BigCDogCrew,

    The method AddError throws an exception and the code doesn't carry-on. Sorry, maybe that is not so clear.

    I've resolved my issue creating a transaction and committing that manually using the methods:

    using (var transaction = UnitOfWorkManager.Begin(TransactionScopeOption.RequiresNew)) { transaction.Complete(); }

Assignee
No one
Labels
Non yet