How to handle nested datacontext in the BL?

Question

public class TestBL
{
    public static void AddFolder(string folderName)
    {
        using (var ts = new TransactionScope())
        {
            using (var dc = new TestDataContext())
            {
                var folder = new Folder { FolderName = folderName };

                dc.Folders.InsertOnSubmit(folder);
                dc.SubmitChanges();

                AddFile("test1.xyz", folder.Id);
                AddFile("test2.xyz", folder.Id);
                AddFile("test3.xyz", folder.Id);

                dc.SubmitChanges();
            }

            ts.Complete();
        }
    }

    public static void AddFile(string filename, int folderId)
    {
        using (var dc = new TestDataContext())
        {
            dc.Files.InsertOnSubmit(
                new File { Filename = filename, FolderId = folderId });

            dc.SubmitChanges();
        }
    }
}

This is an example of nested DataContext (untested). The problem starts when a TransactionScope is added to our little experiment (as shown above). The first AddFile at the AddFolder function will escalate the transaction to DTC (which is bad by all means), because AddFile initializes new DataContext, thus opening a second connection to the DB.

1. How can I use nested DataContext that will not occur a DTC usage?
2. Is this all just plain wrong? Should I use the DataContext differently?

Answer 1

No doubt escalating to DTC should be avoided where possible. When I first read your question, my gut said your transaction would not escalate to DTC because you are using the same connection string in both data contexts. However, according to this article, I was wrong.

You are not alone in the confusion over best practices with data contexts. If you search the web for this, there are answers all over the map. In your example, you could pass the data context into the AddFile method. Or, you could refactor this data access into a class that maintains the lifetime of the data context until the folder and files are all saved. Rick Strahl posted an article on several techniques.

Still, none of the answers I've seen around LINQ to SQL seem very satisfactory. Have you considered avoiding the management of your data layer by using an ORM? I have used NetTiers with great success, but I hear good things about PLINQO. These both require CodeSmith, but there are many alternatives.

Answer 2

I've came up with a way of handling such situations.

Sample base class for a BL entity (the entity will inherit this class)

abstract public class TestControllerBase : IDisposable
{
    public TestDataContext CurrentDataContext { get; private set; }

    protected TestControllerBase()
    {
        CurrentDataContext = new TestDataContext();
    }

    protected TestControllerBase(TestDataContext dataContext)
    {
        CurrentDataContext = dataContext;
    }

    protected void ClearDataContext()
    {
        CurrentDataContext.Dispose();
        CurrentDataContext = new TestDataContext();
    }

    public void Dispose()
    {
        CurrentDataContext.Dispose();
    }
}

Implemented controller

public sealed class BLTestController : TestControllerBase
{
    public BLTestController() { }

    public BLTestController(TestDataContext dataContext)
        : base(dataContext) { }

    //  The entity functions will be implemented here using CurrentDataContext
}

Simple use of an implemented controller

var testController = new BLTestControllerA();

testController.DeleteById(1);

More complex use of an implemented controller (2 controllers on the same DataContext)

var testControllerA = new BLTestControllerA();
var testControllerB = new BLTestControllerB(testControllerA.CurrentDataContext);

testControllerA.DeleteById(1);
testControllerB.DeleteById(1);

I'd like to see more ideas about solving this riddle and comments about the code above.

Answer 3

You don't need to do 2 or more roundtrips for this transaction. I believe LINQ-DataContext is smart to recognize that those files belong to the folder object and will insert the folder row first and files after that (everything in the context of a transaction, e.g. BEGIN TRAN/COMMIT). However you need to do:

dc.Files.InsertOnSubmit(
                new File { Filename = filename, Folder = folder });

instead of FolderId. Something like this:

public class TestBL
{
    public static void AddFolder(string folderName)
    {
        using (var ts = new TransactionScope())
        {
            using (var dc = new TestDataContext())
            {
                var folder = new Folder { FolderName = folderName };

                AddFile(dc, "test1.xyz", folder);
                AddFile(dc, "test2.xyz", folder);
                AddFile(dc, "test3.xyz", folder);

                dc.SubmitChanges();
            }

            ts.Complete();
        }
    }

    private static void AddFile(DataContext dc, string filename, Folder folder)
    {
            dc.Files.InsertOnSubmit(
                new File { Filename = filename, Folder = folder });
    }

    public static void AddFile(string filename, int folderId)
    {
        using (var dc = new TestDataContext())
        {
            var folder = new Folder { FolderId = folderId };
            dc.Attach(folder, false);
            AddFile(dc, filename, folder);

            dc.SubmitChanges();
        }
    }
}

To your question regarding DTC: I don't think it's possible to avoid DTC here due to 2 open connections. I believe they made some changes in this area recently, see here, but the scenario described there is a bit different (2 connections opened and closed one after another as opposed to 2 connections open simultaneously).

Answer 4

Aside from passing the DataContext to AddFiles as a parameter, you could also pass one DataContext's Connection value to another DataContext. That would guarantee that the other DataContext has the same connection.

Each DataContext also has a Transaction property, too, which you could probably set and pass around instead of using the TransactionScope object.