c# - How can I get two units of work under a single transaction -
i have 1 ms sql server containing 2 databases. in c# console application have created 2 different entity data models (edmx) (ef6); 1 databasea , 1 databaseb; applying repository , unit of work patterns both. separately, work well. no problems. not able figure out how bring both under single 'transaction'.
before ef, create sqlconnection , sqltransaction, modify relevant tables in either database within transaction, commit or rollback, appropriate. doesn't seem have analog in ef.
unitofworkfordatabasea.commit(); unitofworkfordatabaseb.commit(); //if fails, both should rollback
but doesn't seem possible 2 separate units of work each own objectcontext.
do need surround them both in transactionscope? or maybe design superunitofwork?
either use transactionscope , distributed transaction (warning requires msdtc), or use single sqlconnection both dbcontext instances. have manually switch database context first database second calling
use otherdatabasename
to make work easiest way use transactionscope (it not promoted dtc transaction since you're using single sqlconnection).
eg
using system; using system.collections.generic; using system.componentmodel.dataannotations.schema; using system.data.common; using system.data.entity; using system.data.entity.modelconfiguration; using system.data.sqlclient; using system.linq; using system.transactions; namespace consoleapp8 { public class { public int aid { get; set; } public string name { get; set; } } public class b { public int bid { get; set; } public string name { get; set; } } class dba : dbcontext { public dba(): base() { } public dba(dbconnection con) : base(con,false) { } public dbset<a> { get; set; } } class dbb : dbcontext { public dbb() : base() { } public dbb(dbconnection con) : base(con, false) { } public dbset<b> b { get; set; } } class program { static void main(string[] args) { database.setinitializer(new createdatabaseifnotexists<dba>()); database.setinitializer(new createdatabaseifnotexists<dbb>()); string databasenamea, databasenameb; using (var db = new dba()) { db.database.initialize(false); databasenamea = db.database.connection.database; } using (var db = new dbb()) { db.database.initialize(false); databasenameb = db.database.connection.database; } var opts = new transactionoptions() { isolationlevel = isolationlevel.readcommitted }; using (var dba = new dba()) using (var tran = new transactionscope(transactionscopeoption.required, opts)) { var = dba.a.create(); a.name = "somea"; dba.a.add(a); dba.savechanges(); dba.database.executesqlcommand($"use [{databasenameb}]"); using (var dbb = new dbb(dba.database.connection)) { var b = dbb.b.create(); b.name = "someb"; dbb.b.add(b); dbb.savechanges(); } tran.dispose(); } using (var dba = new dba()) { dba.database.connection.open(); //lock connection open if not using transaction console.writeline($"count of a: {dba.a.count()}"); dba.database.executesqlcommand($"use [{databasenameb}]"); using (var dbb = new dbb(dba.database.connection)) { console.writeline($"count of b: {dbb.b.count()}"); } } console.writeline("hit key exit"); console.readkey(); } } }
outputs
count of a: 0 count of b: 0 hit key exit
the reason have use transactionscope here savechanges otherwise use sqltransaction internally. fun fact: sqltransaction pretty badly broken. requires manual sqlcommand enlistment, , doesn't support nested transactions (which calls "parallel transactions"). way dates .net 1.0, , can't changed. made work system.transactions when appeared in .net 2.0, though.
Comments
Post a Comment