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

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.