Concurrency issues using DbContext.Database.Connection.CreateCommand

Question

I'm gonna try to explain this without actually mucking through the whole design of the application I'm working on. Cause who cares about that. If it's pertinent, I can give more detail but I think I cover everything below.

main problem:

I've got a few stored procedures that I need to execute periodically. I get the proc names from a database table, so my application has no hard coded knowledge of them. This is by design. I'm currently using the entity framework to do all my data access.

My application is multi threaded -- I grab all the procs that I need to run, then iterate through each one, grab its properties from my configuration tables and queue it up to run on a separate thread in the app's thread pool.

I'm using the Database.Connection.CreateCommand method and executing the procs -- pulling the data into a data table. This works out well when I'm only executing one proc, but when

Problem is that I run into concurrency issues. The specific exception I'm getting is

System.Data.SqlClient.SqlException: New transaction is not allowed because there are other threads running in the session.

I'm assuming this is because I'm trying to execute my procedure while also accessing the properties of another procedure via the entity framework.

---

Potential solutions / trains of thought that occur to me:

• I can get this to work just fine using the DbContext.Database.SqlQuery method if I write a model class that has the fields I need. But what if I don't want to write a model class at all? Is there a way I can do this and let the entity framework actually handle the query?

• I could grab all of the properties before i start executing any procs. That would probably work fine. If I define my properties as ICollections on the model that tells me what procedure to run, is there a way to force them to load when I select said model from the DB instead of when I iterate through them later to read them?

Answer 1

The main problem here is that you are using single database connection (Database.Connection returns the same instance for all your CreateCommand calls) by multiple threads and you are trying to define multiple transactions concurrently. The solution for this scenario is only one - use a new connection per stored procedure / thread. You should be able to get connection string by calling Database.Connection.ConnectionString.

• Your first solution will not solve the problem unless you run a new context in each thread.
• Database.SqlQuery always require type for loading data. Entity framework make easier data access by mapping result sets to types. If you don't have a type you can't take any advantage of EF.
• The way to load relation immediately is called eager loading and is performed by Include method used in the query. You will just say which navigation properties on the mapped entity must be loaded immediately.