Threads and linq data context

Question

This is my code:

DatabaseDataContext context = new DatabaseDataContext();

var sourceList = (from q in context.Table1 where col1< 2000 select q).ToList();

foreach( Type x in sourceList)
{
    var task = Task.Factory.StartNew(() => FetchData(x));
}

FetchData(x)
{
    SomeBO obj = new SomeBO();

    obj.Prop1 = x.Table2.col;
    obj.Prop2 = x.Table3.col;
}

When I execute this code, I get an exception 'There is already an open DataReader associated with this Command which must be closed first'. How could I make the method 'FetchData' to execute in parallel for all the list item in 'sourceList'?

score 1 · Answer 1 · answered May 24 '13 at 13:49

You are using one context instance in multiple threads. But a context is not thread safe. Switching on MARS won't help, the tasks will keep competing for the connections.

The solution is to create a new context for each task. Maybe something like

FetchData(x)
{
    SomeBO obj = new SomeBO();
    using(var context = new DatabaseDataContext())
    {
        obj.Prop1 = context.Table2s.Where(t2 => t2.Id == x.Id)
                           .FirstOrDefault().col;
        ...
    }
}

but I have to guess, because I don't know the structure of your classes.

score 0 · Answer 2 · edited May 23 '17 at 10:27

0

'There is already an open DataReader associated with this Command which must be closed first'

You could have a look at a link Entity Framework - There is already an open data reader associated with this command

How could I make the method 'FetchData' to execute in parallel for all the list item in 'sourceList'?

I think you want Parallel.ForEach

edited May 23 '17 at 10:27

Community

1
1

answered May 24 '13 at 09:15

cat916

1,363
10
18

How would it solve the problem `There is already an open DataReader associated with this Command which must be closed first` – I4V May 24 '13 at 09:20
As @I4V said, it didn't solve the problem. still the exception is thrown – Chindu Krishna May 24 '13 at 09:33
Plz, have a look at link I gave you. And then you can use Parallel.ForEach(). – cat916 May 24 '13 at 09:36
@ckr you *need* to add [MARS](http://stackoverflow.com/a/16731616/1244816) to the connextionstring. – Jens Kloster May 24 '13 at 10:46

score 0 · Answer 3 · answered May 24 '13 at 09:23

You properly need to include MARS in your connectionstring.
Like this:

 var sqlBuilder = new SqlConnectionStringBuilder
          {
            DataSource = "myServer",
            InitialCatalog = "mydatabase",
            IntegratedSecurity = false,
            UserID = "user",
            Password = "pass",
            MultipleActiveResultSets = true //<-- here
          };

and then

   var efConnString = new EntityConnectionStringBuilder
          {
            ProviderConnectionString = sqlBuilder.ToString()
           //other properties omitted
          }

Or you could do it the old-fashion-way:

string connectionString = "Data Source=MSSQL1;" + 
    "Initial Catalog=AdventureWorks;Integrated Security=SSPI" +
    "MultipleActiveResultSets=True";

Threads and linq data context

3 Answers3