Linq to SQL dataContext life-time problem

Question

Hi, I'm using Linq to SQL to write several unit tests. Sometimes I have code something like this:

var products = dataContext.Products;
Assert.That(1, dataContext.ExecuteQuery<int>("select count(*) from product").First()); //this works
Assert.That(1, products.Count()); //this works

dataContext.spCalculateMoreProducts();

Assert.That(2, dataContext.ExecuteQuery<int>("select count(*) from product").First()); //this works
Assert.That(2, products.Count()); // this fails, saying that got 1 instead of 2

spCalculateMoreProducts is a stored procedure that inserts more products outside Linq to SQL. The problem with this is that dataContext.Products is never refreshed. My understanding is that this is how Linq to SQL works. I think it's fine in general, but I would like to somehow force a refresh so I can write my unit tests like above. I can't create a new datacontext, because the whole unit tests runs in a single transaction, which is rolled back at the end. Any ideas?

Thanks

Answer 1

Data-contexts should ideally be a unit of work. It isn't 100% clear to me where your test breaks down, but I don't see why you can't have multiple data-contexts on the same transaction.

If you are using connection-based transactions, then just make sure you give the SqlTransaction to all the contexts. But even easier is just to use TransactionScope; all data-contexts will enlist in the ambient transaction. This makes it a breeze to integration-test code in your DAL without changing the data. For example:

using(var tran = new TransactionScope()) {

    using(var ctx1 = new MyDataContext()) { ... } 

    SomeDal.SomeMethod(1,2,3);

    using(var ctx2 = new MyDataContext()) { ... } 

    using(var ctx3 = new MyDataContext()) { ... } 
}

Here, anything using SqlConnection should (unless it deliberately uses the null-transaction) be in your transaction, which is rolled back because we don't call tran.Complete().