Can I reuse the connection from DataContext in Linq to Sql?

Question

DataContext has Connection property of type DbConnection and I was wondering if I could re-use it. I've tried to use it creating a command using CreateCommand and using a reader off of it but I've gotten errors saying there is a pending transaction or something similar.

Basically I'm trying to find out if there is a best practice or guidance on re-using that connection object.

The connection string itself doesn't really help since I can't create a new connection object with the abstract type and I don't want the code to know the specific provider type either.

One alternative approach I'm thinking of doing is having my DataContext derived type to have members that provide a factory method to create a new connection using the same connection information.

FYI, this inquiry stems from the need to invoke an ad-hoc stored procedure through DataContext, not the design-time ExecuteMethodCall variety. I didn't know about ExecuteQuery and for now that suffices. But for other situations where ExecuteQuery is inadequate, I'd need the low-level data access using connection/command etc.

Answer 1

I can't say definitely, but a connection is a connection. Until it's been closed (or returned to the connection pool), it should be viable to use, whether via LINQ or other means.

Answer 2

Why not turn it around, and instead supply connections to the datacontext by using the constructor that takes a connection as a parameter? That way you can control when connections are created and disposed, opened and closed, and can reuse them for other purposes without having to worry about the internal behavior of the L2S datacontext...