I have a project that i want create an separation of concepts, related with db, with reuse propose. I use EF, with mysql(devart) connector, and I want to create a transaction, that creates a registry in some table and link this registry to other table (rollback if something else happens)
I will provide a dummy scenario, to explain the process:
Generic.edmx:
Person {Id, Name, Address, ...}
MyApp.edmx (contains my app database, who uses Generic tables)
Person {Id, Name, Address, ...}
Schedule{ScheduleId, Date, PersonId(FK), ...}
Some key points:
- Generic, was already some implemented some CRUD methods to deal with tables of this edmx
- MyApp, has my database, that have multiple tables, and has generic tables inside.
- All database use same connectionString, but different DbContext.
- The propose of generic, is reuse tables and this CRUD in other projects.
In my method createSchedule, and I need to create a person and link this person to schedule. I have a method already created createPerson(...), which return this Id. I need to use this Id to link to new schedule registry, so I want this transactional operation, so I create a transaction scope, with two contexts, first one to create person, and second to create schedule. All contexts ends with ctx.saveChanges(), but in second context, I'm facing an exception (mysql timeout) related with lock table/registry person. I understand this new registry isn't committed, so, in second ctx, this registry don't exists.
I already tried Isolation Level= ReadUncommited.
public static int createSchedule(...){
using (TransactionScope transaction = new TransactionScope(
TransactionScopeOption.RequiresNew,
new TransactionOptions() {
I solationLevel = IsolationLevel.ReadUncommitted }))
{
var myId = createPerson(...);
using (var ctx = new MyAppDbEntity(MyConnStr))
{
Schedule sc = new Schedule{
Id = 1,
...
PersonId = myId,
...
};
//Logic code
ctx.Schedule.Add(sc);
ctx.saveChanges(); //Exception raised Here
transaction.Complete();
}
}
}
public static int createPerson(...){
using (var ctx = new GenericDbEntity(MyConnStr))
{
Person p = new Person{
Id = 10,
...
};
//Logic code
ctx.Person.Add(p);
ctx.saveChanges();
return p.Id;
}
}
Any solution to solve my problem?
