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.