Strange Bug With Enumerator of an IQueryable

Question

I ran into this bug while trying to refresh an entity and it would throw an InvalidOperationException with the message "There is already an open DataReader associated with this Command which must be closed first." I have looked around the internet for reasons why that message would occur, but none of the answers really come close to this scenario.

I created an Enumerator from a LINQ to SQL query, so that i could enumerate over the results (there is a very specific reason for using the enumerator, which is critical to the algorithm that i am using, so i cannot change that part in case people feel like suggesting alternate ways of iterating).  When i ran across the problem i couldn't really understand why the exception was being thrown, but i initially thought it was something else i was doing in the code. I eventually narrowed things down and created a very simple unit test that showcases the issue.  I've simplified the unit test code a little, but mostly just to make it easier to read.

using (var ctx = new MyDataContext())
{
    ctx.MyTable.InsertOnSubmit(new MyTable()
    {
        ID = 1,
        Name = "asdf"
    });
    ctx.SubmitChanges();
    var entities = ctx.MyTable.Where(x => x.ID == 1);

    var enumerator = entities.GetEnumerator();
    var hasData = enumerator.MoveNext();
    System.Diagnostics.Debug.WriteLine("ID/Name = {0}/{1}", enumerator.Current.ID, enumerator.Current.Name);

    try
    {
        ctx.Refresh(RefreshMode.KeepChanges, enumerator.Current);
    }
    catch (InvalidOperationException)
    {
        // why???
    }

    // how does this fix it???
    entities.Any();

    ctx.Refresh(RefreshMode.KeepChanges, enumerator.Current);

    // no exception???
}

my comments in that snippet should explain why i am confused about thsi issue. if i comment out the entities.Any() line then i will get the exact same exception on the second call to refresh as i did in the first. It seems that creating an Enumerator from an IQueryable doesn't do something critical to LINQ to SQL that causes the refresh to fail.  The enumerator clearly works because i can access the fields on the entity and i can even make changes and submit them. but for some reason this code above fails...

So... any thoughts on this?

Answer 1

Hi PatSissons,

Welcome to MSDN Forum.

I have tested the code you posted, but no exception is thrown. Is there any other places you opened a DataReader?

Below is the code I tested, there's no difference with yours.

class Program
    {
        static void Main(string[] args)
        {
            using (DataClasses1DataContext ctx = new DataClasses1DataContext())
            {
                ctx.Peoples.InsertOnSubmit(new People
                {
                    Name = "Test"
                });
                ctx.SubmitChanges();
                var entity = ctx.Peoples.Where(x => x.Id == 1);
                var enumerator = entity.GetEnumerator();
                var hasData = enumerator.MoveNext();
                if (hasData)
                {
                    Console.WriteLine(enumerator.Current.Id + enumerator.Current.Name);
                }

                try
                {
                    ctx.Refresh(System.Data.Linq.RefreshMode.KeepChanges, enumerator.Current);
                }
                catch (InvalidOperationException e)
                {
                    Console.WriteLine(e.ToString());
                }

                entity.Any();
                ctx.Refresh(System.Data.Linq.RefreshMode.KeepChanges, enumerator.Current);
                Console.Read();
            }
        }
    }

Best Regards

---

Allen Li [MSFT]
MSDN Community Support | Feedback to us

Answer 2

Hi PatSissons,

Have you solved the issue? I look forward to hearing from you. If you need further help, please feel free to let me know, I will be more than happy to be of assistance.

Best Regards

---

Allen Li [MSFT]
MSDN Community Support | Feedback to us

Answer 3

This is my code verbatim:

class Program
{
    static void Main(string[] args)
    {
        using (DataClasses1DataContext ctx = new DataClasses1DataContext())
        {
            ctx.Tests.InsertOnSubmit(new Test
            {
                Name = "Test1"
            });
            ctx.Tests.InsertOnSubmit(new Test
            {
                Name = "Test2"
            });
            ctx.SubmitChanges();
            var entity = ctx.Tests.Where(x => true);
            var enumerator = entity.GetEnumerator();
            var hasData = enumerator.MoveNext();
            if (hasData)
            {
                Console.WriteLine(enumerator.Current.TestID + enumerator.Current.Name);
            }

            try
            {
                ctx.Refresh(System.Data.Linq.RefreshMode.KeepChanges, enumerator.Current);
            }
            catch (InvalidOperationException e)
            {
                Console.WriteLine(e.ToString());
            }

            entity.Any();
            ctx.Refresh(System.Data.Linq.RefreshMode.KeepChanges, enumerator.Current);
            Console.Read();
        }
    }
}

My table DDL looks like this:

CREATE TABLE [dbo].[Test](
	[TestID] [bigint] IDENTITY(1,1) NOT NULL PRIMARY KEY,
	[Name] [nvarchar](50) NOT NULL,
	[Description] [nvarchar](MAX) NULL
)

(Description field doesn't really matter for the test case, but i was just trying to be thorough)

.NET 4.0 Client Profile Console App

SQL Server 2012 (version 11.0.2100.60)

When i run the code, the exception still occurs.

Answer 4

OK after being very confused for a short while, i figured out why you didn't experience the exception. I too started (what seemed to be) randomly seeing no exception in the code. But as it turns out the refresh doesn't throw an error if there is only one item in the enumeration (which is the case when we filter by PK). But using my corrected code and ensuring the enumeration has more than one item, the exception should always occur.

Answer 5

Hi Pat,

The entity object in your codes is IQuerable.  When calling GetEnumerator, we get IEnumerator object.  However, if changes are made to the collection, such as adding, modifying or deleting, we see the InvalidOperationException.  

Here we call Refresh method which will update the entity object in the collection.  I believe that's why the exception is thrown. 

Good day!

Thanks

---

Michael Sun [MSFT]
MSDN Community Support | Feedback to us

Answer 6

There are no modifications being made the collection itself, only to the objects contained within the collection, which should not affect the enumerable.  The call to refresh simply overwrites the contents of the db backed properties in the entity being refreshed. Furthermore, there is still no explanation why the call to Any() fixes the exception from being called.

Answer 7

Hi,

I agree that it's a little strange.  But I think from the exception message "There is already an open DataReader associated with this Command which must be closed first.", LINQ to SQL still keeps the connection and the open DataReader on the IQueryable query "entity".   Without using the IEnumerator object, we can directly repro it through these codes:

                  foreach (var en in entity)
                        ctx.Refresh(System.Data.Linq.RefreshMode.KeepChanges, en);

And also, I don't think entity.Any() can fix the issue, because these codes still raise the same exception:

                    entity.Any();

                    foreach (var en in entity)
                        ctx.Refresh(System.Data.Linq.RefreshMode.KeepChanges, en);

If the DataReader is still open, LINQ to SQL uses it to do the Reflesh operation, and that's why the exception is thrown.   You may use a foreach loop to let the entity query be finished, and they call the Reflesh method.   If you think this issue make huge impact on your app, I would recommend you open a bug ticket in http://connect.microsoft.com .  However, I personally don't think it's a big issue.  :)

Good day!

Thanks

---

Michael Sun [MSFT]
MSDN Community Support | Feedback to us

Answer 8

The Any() call in my test code above does fix this issue (i'm still confused why that is though...) so there is no pressing issue to fix this bug on my end. I am unable to change the way i do things (i.e. i need to continue using the IEnumerator and performing Refresh calls as I enumerate) because there isn't really an alternative. In my actual code, i am calling stored procs through the data context, and these stored procs may or may not modify some fields of the entity in the current context of the enumerator. The stored procs will inform me whether or not something changed, but i can't easily determine what precisely changed. Therefore, i must perform the refresh to force retrieval of the updated fields into the current enumerator entity.

My only guess on the Any() fix is that perhaps a call to Any() forces an additional connection so that the subsequent call to Refresh uses fresh connection (rather than erroneously trying to share the enumerator's connection), as i assume the call to Refresh() involves using a DataReader.  Just a guess.

Answer 9

Hi Pat,

Not sure why Any() method can fix the issue in your codes, it may need to dig into the LINQ to SQL source to check.   However, how about calling entity.toList() to let all the entity objects be loaded into the memory at once, and then I think you can use the Refresh method.

Good day!

Thanks

---

Michael Sun [MSFT]
MSDN Community Support | Feedback to us

Answer 10

not possible, the db query could be massive, that's why having the enumerator is non-negotiable. 

Answer 11

Hi Pat,

If you think it cause huge impact on your project, could you please open a bug ticket in http://connect.microsoft.com?   But I don't think this issue can be fixed quickly because it may need to be weighted by the product team.  

Besides, could you please share us with some source codes of your real app?  I don't quite understand why we must use the enumerator.  Maybe we can figure out some other workarounds after we understanding your detailed scenario clearly.

Have a nice weekend.

Thanks

---

Michael Sun [MSFT]
MSDN Community Support | Feedback to us

Answer 12

There isn't much code to share. Just imagine that the table i am querying could have 100 million rows. Memory constraints (and good programming practices) don't allow me to load every row into memory, so i must enumerate. Normally i would use a foreach, but in this case i need to use an enumerator because i am comparing two datasets side by side, so sometimes one enumerator moves the cursor forward, sometimes the other (depending on the comparison between both dataset PK values).

I don't really have time to fill out and monitor a bug report. I posted the issue here just to raise awareness and see if anyone else had any thoughts. Like i said earlier, the issue is resolved through the Any() hack, so as far as I am concerned there is no more issue (since Any() doesn't cost much in performance).

Answer 13

Hi,

I can understand the situation now.  If Any() can help you work around the issue, I would recommend you temporarily use this method. 

Have a nice weekend.

Thanks

---

Michael Sun [MSFT]
MSDN Community Support | Feedback to us