Can you enable linq-to-sql changes and ADO.NET table adapter updates in one transaction?

Here are the relevant technologies I am working with:

• Devart dot Connect for Oracle (to facilitate Linq-to-Sql for Oracle).
• Strongly typed ADO.NET datasets.
• Oracle database.

Here's the problem:

• My old code introduces database updates with ADO.NET datasets and table adapters.
• I would like to start converting this code to Linq-to-Sql, but I would like to do it piece by piece to minimize code rejection and risk.

Here's my proof of the concept diagram:

Parent table

• Parent.Id
• Parent.Name

Children table

• Child.Id
• Child.ParentId
• Child.Name

Here's my code proof of concept:

using System;
using System.Data.Common;
using DevArtTry1.DataSet1TableAdapters;

namespace DevArtTry1
{
    class Program
    {
        static void Main(string[] args)
        {
            using (DataContext1 dc = new DataContext1())
            {
                dc.Connection.Open();
                using (DbTransaction transaction = dc.Connection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted))
                {
                    dc.Transaction = transaction;

                    Parent parent = new Parent();
                    parent.Id = 1;
                    parent.Name = "Parent 1";
                    dc.Parents.InsertOnSubmit(parent);
                    dc.SubmitChanges(); // By virtue of the Parent.Id -> Child.ParentId (M:N) foreign key, this statement will impose a write lock on the child table.

                    DataSet1.CHILDDataTable dt = new DataSet1.CHILDDataTable();
                    DataSet1.CHILDRow row = dt.NewCHILDRow();
                    row.ID = 1;
                    row.PARENTID = 1;
                    row.NAME = "Child 1";
                    dt.AddCHILDRow(row);

                    CHILDTableAdapter cta = new CHILDTableAdapter();
                     // cta.Transaction = transaction;  Not allowed because you can't convert source type 'System.Data.Common.DbTransaction to target type 'System.Data.OracleClient.OracleTransaction.
                    cta.Update(dt); // The thread will encounter a deadlock here, waiting for a write lock on the Child table.
                    transaction.Commit();
                }
            }

            Console.WriteLine("Successfully inserted parent and child rows.");
            Console.ReadLine();
        }
    }
}

      

        
        
        
      

    

• As you can see from the comments, the thread will stop indefinitely on the call to update the child adapter as it will wait indefinitely to lock the record in the Children table. [Note the foreign key relationship: Parent.Id → Child.ParentId (M: N)]

Here's my question:

• I want to wrap an entire block of code in a transaction.
• Can I do it? Considering that:
  • I want to commit an update to a parent table using the Linq-to-Sql SubmitChanges method ...
  • And I want to commit to update the Children table using the ADO.NET dataset adapter table .

Here are two interesting footnotes:

• All this works in reverse. That is, if I wanted to make changes to the parent table with the data adapter and changes to the child table with linq-to-sql ... this would work.

• I tried to explicitly bind the transaction to the dataadapter, but the compiler won't resolve it because it is a different type of transaction.

                  CHILDTableAdapter cta = new CHILDTableAdapter();
              cta.Transaction = transaction; // Not allowed because you can't convert source type 'System.Data.Common.DbTransaction' to target type 'System.Data.OracleClient.OracleTransaction'.
              cta.Update(dt);
              transaction.Commit();