Can I safely intermix Linq-to-SQL queries and non-Linq-to-SQL queries on the same DataContext.Connection?

Question

Suppose I have code in this form:

using (var dc = new MyDataContext()) {
    // ...code that may or may not look up objects via Linq-to-SQL...

    // ...code that may or may not *update* objects via Linq-to-SQL...
    // and call SubmitChanges...

    // Non-Linq-to-SQL code:
    dc.Connection.Open(); // <== I believe I need to do this
    using (SqlCommand cmd = dc.Connection.CreateCommand()) {
        // ...set up the command...
        using (SqlDataReader reader = cmd.ExecuteReader()) {
            // ...use the reader here...
        }
    }

    // ...more code that may or may not look up objects via Linq-to-SQL...

    // ...more code that may or may not *update* objects via Linq-to-SQL...
    // and call SubmitChanges...
}

Is that safe, e.g., am I allowed to co-opt the connection like that? Am I correct that I have to call Open in case the code above hasn't had to do any DB calls?

This MSDN page seems to say that this is fine:

The DataContext is the main conduit by which you connect to a database, retrieve objects from it, and submit changes back to it. You use the DataContext just as you would use an ADO.NET SqlConnection.

(Yes, this is all in the context of a single unit of work, in keeping with the concept of DataContext. It's just that it's a complex unit of work.)

If it matters, I'm stuck in .Net 3.5 for this project.

Answer 1

Yes, it's perfectly sane to do this. And yes, you have to open the connection before using it to access the database, because LINQ-to-SQL closes the connection after each database interaction. Likewise, it's also good practice to close the connection after you're done using it.

If the things you do with and without LINQ-to-SQL should be transactional, you may want to wrap the whole code snippet in a using TransactionScope.

I do similar things with Entity Framework contexts, when I have to make new and legacy code work together. I assume you're aware of the fact that LINQ-to-SQL won't track any of the reads/updates you do through the connection yourself.