locked
how to update two databases using two seprate edmx one for each database in one transation RRS feed

  • Question

  • User2102072086 posted

    hi, 

       Problem is we have two databases(on the same instance), and we are using entity framework(5), so whenever I start a transaction scope and make instance of one <g class="gr_ gr_18 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling" id="18" data-gr-id="18">edmx</g> and call save changes it goes well, but when I create insistence of other <g class="gr_ gr_19 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling" id="19" data-gr-id="19">edmx</g> and open connection it asks for Microsoft distributed transaction to be opened. and we do not want to use it.

           so is there any way exists, so that I can update two databases and commit or rollback together <g class="gr_ gr_24 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" id="24" data-gr-id="24">usng</g> <g class="gr_ gr_104 gr-alert gr_gramm gr_inline_cards gr_run_anim Style multiReplace" id="104" data-gr-id="104"><g class="gr_ gr_32 gr-alert gr_spell gr_inline_cards gr_disable_anim_appear ContextualSpelling" id="32" data-gr-id="32">edmx</g> <g class="gr_ gr_103 gr-alert gr_gramm gr_inline_cards gr_run_anim Style replaceWithoutSep" id="103" data-gr-id="103">,</g></g><g class="gr_ gr_103 gr-alert gr_gramm gr_inline_cards gr_disable_anim_appear Style replaceWithoutSep" id="103" data-gr-id="103">one</g> for each database in one transaction.

    using a stored procedure and three-part name we can do it, but we want a solution using <g class="gr_ gr_25 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling" id="25" data-gr-id="25">edmx</g>?

    yours sincerely

    Thursday, September 27, 2018 11:27 AM

All replies

  • User475983607 posted

    I'm not sure what EF version you are using but the concept is covered in the following link. 

    https://docs.microsoft.com/en-us/ef/ef6/saving/transactions

    Thursday, September 27, 2018 11:47 AM
  • User2102072086 posted

    <g class="gr_ gr_173 gr-alert gr_tiny gr_spell gr_inline_cards gr_run_anim ContextualSpelling multiReplace" id="173" data-gr-id="173">i</g> am using <g class="gr_ gr_6 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del" id="6" data-gr-id="6">ef</g> 5

    and the link says about using the same connection for two queries but for one database. <g class="gr_ gr_161 gr-alert gr_tiny gr_spell gr_inline_cards gr_run_anim ContextualSpelling multiReplace" id="161" data-gr-id="161">i</g> have two <g class="gr_ gr_204 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling" id="204" data-gr-id="204">edmx</g> which goes to two databases.

    Friday, September 28, 2018 11:46 AM
  • User475983607 posted

    The link explains how transaction API works.   It's up to you to use the information...

    One option presented in the link  is passing a transaction to a context. There is also begin, commit, and rollback transactions in using blocks.

    Friday, September 28, 2018 1:38 PM
  • User-271186128 posted

    Hi rajemessage,

    how to update two databases using two seprate edmx one for each database in one transation

    You could use the UseTransaction() method to use an existing transaction. Code as below:

    var options = new DbContextOptionsBuilder<BloggingContext>()
        .UseSqlServer(new SqlConnection(connectionString))
        .Options;
    
    using (var context1 = new BloggingContext(options))
    {
        using (var transaction = context1.Database.BeginTransaction())
        {
            try
            {
                context1.Blogs.Add(new Blog { Url = "http://blogs.msdn.com/dotnet" });
                context1.SaveChanges();
    
                using (var context2 = new BloggingContext(options))
                {
                    context2.Database.UseTransaction(transaction.GetDbTransaction());
    
                    var blogs = context2.Blogs
                        .OrderBy(b => b.Url)
                        .ToList();
                }
    
                // Commit transaction if all commands succeed, transaction will auto-rollback
                // when disposed if either commands fails
                transaction.Commit();
            }
            catch (Exception)
            {
                // TODO: Handle failure
            }
        }
    }

    More details, please refer to the following links:

    https://docs.microsoft.com/en-us/ef/core/saving/transactions

    http://www.entityframeworktutorial.net/EntityFramework6/transaction-in-entity-framework.aspx

    Best regards,
    Dillion

    Thursday, October 4, 2018 7:34 AM