How do I keep an IQueryable<> within a transaction using the repository pattern?

Question

According to NHProf, the use of implicit transactions is discouraged:

http://nhprof.com/Learn/Alerts/DoNotUseImplicitTransactions

However, NHibernate LINQ returns an IQueryable<> when reading objects from the database, and this is lazily evaluated. I have this method in a repository:

public IQueryable<T> GetAll<T>()
{
    using (var transaction = _session.BeginTransaction())
    {
        var data = _session.Linq<T>();
        transaction.Commit();
        return data;
    }
}

The issue here is that the method will commit the transaction before data is evaluated. Is there a way to use the repository pattern and keep the IQueryable<> in an explicit transaction? Or is it acceptable for read operations to use implicit transactions?

Answer 1

I'd refactor this to allow external transaction control. The Repository cannot know the scope of the unit of work that various read/write calls are a part of unless the code that makes the calls tells it. Consider setting up a "unit of work" pattern: without revealing specific details of the data store implementation, allow objects that depend on Repository to specify that they are beginning, aborting or completing a "unit of work".

public interface IRepository
{
   public UnitOfWork BeginUnitOfWork()

   public void CommitUOW(UnitOfWork unit)

   public void AbortUOW(UnitOfWork unit)

   public IQueryable<T> GetAll<T>(UnitOfWork unit)

   public List<T> GetAll<T>()

   public void Store<T>(T theObject, UnitOfWork unit)

   public void Store<T>(T theObject)
}

Your Repository would probably implement this by maintaining a private Dictionary of SQL transactions, each keyed to a UnitOfWork object (this can be as simple as an empty instantiable class, or it can provide framework-agnostic information about state or metrics). When performing a DB operation, your callers will first ask to begin a UoW, and they will be given a token that they will use to identify the context within which they are making a DB call. The object that gets the token can pass it to other classes that need to perform DB operations in the same operational context. The unit of work will remain open until the dependent class tells the Repository that it is finished, allowing lazy-loads and atomic multi-operation procedures.

Notice that there are overloads that do not require units of work. It is possible, and perhaps necessary, to make simple calls without explicitly starting a unit of work. In these cases, your Repository can create an internal UOW, perform the requested operation, and return the results. However, lazy-loading will be difficult or impossible in these cases; you will have to retrieve the entire result set into a List before ending the internal UoW.

Answer 2

The repository should not create a transaction. That's a responsibility of a separate layer (which depends on the application type).

Answer 3

I'm with Diego on this one - repository can't know the transaction scope.

I also have a concern about returning IQueryable - as I understand it, it has a ton of extra query methods that might be very difficult to unit test. I prefer returning IEnumerable and encapsulate more complex queries in repository methods. Otherwise, you'll have to unit test all kinds of variations of queries against the output of GetAll().