EF6 reading data with NOLOCK while using a single connection

83 Views Asked by At

I want one DBContext per API call while allowing me to use NOLOCK on some SELECT calls. I use unity's DI to manage the life cycle of my DBContext.

I built some helper methods to help me with making NOLOCK queries when I select massive amounts of data so I don't lock the table for other processes.

public static class EfExtensions
    {
        public static List<T> ToListWithNoLock<T>(this IQueryable<T> query)
        {
            List<T> result = new List<T>();
            using (var scope = new TransactionScope(TransactionScopeOption.Required, 
                new TransactionOptions() { IsolationLevel = IsolationLevel.ReadUncommitted }, 
                TransactionScopeAsyncFlowOption.Enabled))
            {
                result = query.ToList();
                scope.Complete();
            }
            return result;
        }
}

Then I use it like this

_dashboardDbContext.ImageDownload.Where(x => x.status != ImageDownloadStatuses.failed && x.status != ImageDownloadStatuses.downloaded)
                .OrderBy(a => a.CreatedDateTime)
                .Take(_imageProcessingImagesPerExecution)
                .ToListWithNoLock();

This ToListWithNoLock helper function seems to cause a lot of errors. If I enforce a single DBContext using "ContainerControlledLifetimeManager" (How to configure dependency injection to allow only one instance of a class?) then I get The underlying provider failed on EnlistTransaction. ---> System.InvalidOperationException: The requested operation cannot be completed because the connection has been broken. Uncertain, but I believe this is happening becuse my TransactionScope is trying to create a new DBContext for its own use.

Now if I remove "ContainerControlledLifetimeManager" the restriction of a single DBContext per API call. I see start getting this error. System.InvalidOperationException: An entity object cannot be referenced by multiple instances of IEntityChangeTracker. This also leads me to believe TransactionScope is connecting and reading the entities that I later in the code try to do something with.

I assume that I need to take the current DBContext and somehow provide that to the TransactionScope to use, so it doesn't try to create its own DBContext.

0

There are 0 best solutions below