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.

score 3 · Accepted Answer · answered Jul 18 '15 at 15:13

3

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.

answered Jul 18 '15 at 15:13

Gert Arnold

105,341
31
202
291

Thanks. *"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."* Indeed I am. :-) That would be spooky (but possibly *spooky cool*). – T.J. Crowder Jul 18 '15 at 16:19
Re closing: I'd do it after my non-Linq-to-SQL code, even if it was likely that subsequent Linq-to-SQL code was going to need a connection? (E.g., should I think of each Linq-to-SQL statement as open/do/close, and do the same?) – T.J. Crowder Jul 18 '15 at 16:21
I would always close the connection if you open it yourself, just as a housekeeping principle. But anyway, yes a L2S statement that accesses the database opens/closes the connection. There may be a slight performance gain to leave the connection open if you know L2S is going to need it subsequently (just speculating). – Gert Arnold Jul 18 '15 at 17:59
Thanks again. Yeah, that ("as ye open, so shall ye close") was why I was asking, seemed like it would be odd to leave it. V. much appreciated. – T.J. Crowder Jul 18 '15 at 18:41

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

1 Answers1