Searching For A Record After A LINQ Query Exception

Question

I'm confused to why this is happening. I'm new to LINQ so I'm clearly missing something here, that is probably pretty easy. I've looked up help on the topic, but I don't really know what to ask so I haven't found any answers that really address my question.

This doesn't work

It throws an EntityCommandExecutionException when the FirstOrDefault method is executed.

var query = from band in context.BandsEntitySet
            where band.ID == 12345
            select band;

foreach (var item in query)
{
    string venueName = item.VenueName;

    var venue = context.VenueEntitySet.FirstOrDefault(r => r.Venue.Equals(venueName));

    if(venue != null)
    {
        Debug.WriteLine(item.Name + " is playing in " + venueName + " on the " + item.PlayDate);
        Debug.WriteLine("The address of " + venueName + " is " + venue.Address);
    }
}

This works

var query = from band in context.BandsEntitySet
                    where band.ID == 12345
                    select band;

var bandList = query.toList();

foreach (var item in bandList)
{
    string venueName = item.VenueName;

    var venue = context.VenueEntitySet.FirstOrDefault(r => r.Venue.Equals(venueName));

    if(venue != null)
    {
        Debug.WriteLine(item.Name + " is playing in " + venueName + " on the " + item.PlayDate);
        Debug.WriteLine("The address of " + venueName + " is " + venue.Address);
    }
}

My question is simple: Why is the exception being thrown? And why does creating a list from the query allow me to use the FirstOrDefault method?

Exception Message: A first chance exception of type 'System.Data.EntityCommandExecutionException' occurred in System.Data.Entity.dll

I guess I am wrong in my assumption that query is a list? Then what is it exactly?

Here is the stack trace

A first chance exception of type 'System.Data.EntityCommandExecutionException' occurred in System.Data.Entity.dll
   at System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
   at System.Data.Objects.Internal.ObjectQueryExecutionPlan.Execute[TResultType](ObjectContext context, ObjectParameterCollection parameterValues)
   at System.Data.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)
   at System.Data.Objects.ObjectQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
   at System.Linq.Enumerable.FirstOrDefault[TSource](IEnumerable`1 source)
   at System.Data.Objects.ELinq.ObjectQueryProvider.<GetElementFunction>b__1[TResult](IEnumerable`1 sequence)
   at System.Data.Objects.ELinq.ObjectQueryProvider.ExecuteSingle[TResult](IEnumerable`1 query, Expression queryRoot)
   at System.Data.Objects.ELinq.ObjectQueryProvider.System.Linq.IQueryProvider.Execute[S](Expression expression)
   at System.Data.Entity.Internal.Linq.DbQueryProvider.Execute[TResult](Expression expression)
   at System.Linq.Queryable.FirstOrDefault[TSource](IQueryable`1 source, Expression`1 predicate)
   at BandManagementProject.AutoUpdate.Dev() in c:\BandManagementProject\AutoUpdate.cs:line 99
   at BandManagementProject.AutoUpdate.Main(String[] args) in c:\BandManagementProject\AutoUpdate.cs:line 41

Inner Exception

MySql.Data.MySqlClient.MySqlException (0x80004005): There is already an open DataReader associated with this Connection which must be closed first.
   at MySql.Data.MySqlClient.ExceptionInterceptor.Throw(Exception exception)
   at MySql.Data.MySqlClient.MySqlConnection.Throw(Exception ex)
   at MySql.Data.MySqlClient.MySqlCommand.Throw(Exception ex)
   at MySql.Data.MySqlClient.MySqlCommand.CheckState()
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
   at MySql.Data.Entity.EFMySqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)

Conclusion I didn't close my LINQ query before executing additional queries. Didn't realize I needed to do that. I appreciate all the help!

thanks, Justin

@GrantThomas This is just an example. When I try to search for a record in another entity set it throws an exception. — Justin, Nov 11 '13 at 15:24
Posted my answer then re-read your question. It makes no sense if you are not using the item. I suspect your example is missing some crucial information. can you post something real that you are having a problem with? — Colin, Nov 11 '13 at 15:33
And is there any more information in the stack trace? Is there an inner exception with more details? — Colin, Nov 11 '13 at 15:41
It must have something to do with executing a second query against the context whilst the first is still active - remember that until you materialise (`ToList()`, etc) the connection on the context is still open. I bet if you check the `InnerException` property of that error you will get a pretty clear error message from the underlying DB Provider. — Stephen Byrne, Nov 11 '13 at 16:14
Can you check if there is anything attached to the `InnerException` property when the exception gets thrown (use the debugger, not just the stack trace) as that should hold more details about the underlying cause. — Stephen Byrne, Nov 11 '13 at 16:25
@StephenByrne yup that was it! Are you saying to simply print out the InnerException? Or is there a way to check it while the code is running? Sorry, new to Visual Studio. — Justin, Nov 11 '13 at 16:30
I'm not sure you do need to close your connection unless you are triggering lazy loading. Is Venue marked virtual? http://stackoverflow.com/a/4868569/150342 — Colin, Nov 11 '13 at 16:38
@Justin - delighted you got it working! You can wrap code like that in a `try/catch` block, so when you catch the exception you can check if `InnerException` isn't null, and print it out or log it along with the rest of the exception. (You can of also check in VS during debugging, click the "View Details..." link when the exception notification pops up) Very generally speaking, if the framework you're invoking involves an abstraction of any kind (`Entity Framework`, abstracting `MySqlClient` in this case) then checking for `InnerException` to get the "real" error is always a good idea. — Stephen Byrne, Nov 11 '13 at 16:40
@Colin - the problem here is that, afaik, the `MySqlClient` doesn't support `MARS (Multiple Active Result Sets)` so therefore what might have worked if he were using full-blooded `SqlClient` will not necessarily work for him in this case. That's a slight leak in the EF abstraction :) — Stephen Byrne, Nov 11 '13 at 16:44
@Colin No, I don't think it is. I just loaded the database and all the tables into my project using the Entity Data Model and didn't touch anything. — Justin, Nov 11 '13 at 16:47
@StephenByrne if you write up your comment as an answer, I'll accept it! — Justin, Nov 11 '13 at 16:54
@StephenByrne so iterating through an `IQueryable` and running another query inside the iteration will cause the exception even if you are not triggering a lazy load? (At least while `MultipleActiveResultSets=false` - and it's always effectively false with `MySqlClient`)... This seems to suggest otherwise: http://stackoverflow.com/a/5438064/150342 ? Sorry if am I guilty of hijacking this question ;-) — Colin, Nov 11 '13 at 16:55
@Colin - in that case, using the `.Include` loads all data using a single operation, so there is still only one active result set, it's just a lot bigger, because it contains all of the relevant data (hence will be slower, take up more memory, etc). And that is a really good question! — Stephen Byrne, Nov 11 '13 at 17:06
@Colin - actually I would say it's better to have Colin update his answer and then accept that, it keeps the problem and solution nice and clear. — Stephen Byrne, Nov 12 '13 at 08:14

score 2 · Accepted Answer · edited May 23 '17 at 12:12

2

The first query is using Linq-To-Entities. Linq-To-Entities methods build an expression tree that is translated to sql when you enumerate. If you include something it can't translate to sql you will get an exception. ToString() is an example. LINQ to Entities does not recognize the method 'System.String ToString()'

Calling ToList() makes the enumeration occur. So the sql is run, it doesn't include anything that can't be translated, and the data is moved into memory. Now you are using Linq-To-Objects, and standard C# method calls are recognised

Try this:

var venue = context.VenueEntitySet.FirstOrDefault(r => r.Venue == venueName))

EDIT OK we know that wasn't the problem now - but another idea to consider is getting the data in one call to the database. Something like this:

        var query = from band in context.BandsEntitySet
                    //not sure the join makes sense. How come every band has a VenueName?
                    //join venue in context.VenueEntitySet 
                    //on band.VenueName equals venue.Name
                    //surely there should be a navigation property
                    from venue in band.Venues //using a navigation property
                    where band.ID == 12345
                    select new { 
                                   BandName = band.Name, 
                                   VenueName = venue.Name, 
                                   PlayDate = venue.PlayDate, 
                                   Address = venue.Address 
                               };

        foreach (var item in query)
        {                
            Debug.WriteLine(item.BandName + " is playing in " 
                           + item.VenueName + " on the " + item.PlayDate);
            Debug.WriteLine("The address of " + item.VenueName + " is " + item.Address);

        }

And that should also avoid the problem with multiple open DataReaders

edited May 23 '17 at 12:12

Community

1
1

answered Nov 11 '13 at 15:23

Colin

22,328
17
103
197

Thanks, but that throws the same exception. Any other ideas? I see what you are saying though. – Justin Nov 11 '13 at 15:34
I just tested the solution from Colin on AdventureWorks2008R2, with the `DimAccounts` table and used `var venue = bandList.FirstOrDefault (l => l.AccountKey.Equals(15));` Totally worked. – Marco Nov 11 '13 at 15:42
@Serv yes my bad. I saw that ToList() solved the problem and thought it was the common problem with `LINQ to Entities does not recognize the method ` There is something else going on.. – Colin Nov 11 '13 at 15:45
+1 for doing in in a single operation with a `join` - that's using yer noggin. – Stephen Byrne Nov 11 '13 at 17:27
This is interesting, and would greatly improve the readability and efficiency of my code, but by issue is that there can be multiple venues per band. When I run your code, it prints out every VenueName, PlayDate and Address even if it isn't found in the Bands Table. Does that make sense? – Justin Nov 11 '13 at 18:14
@Justin - you could use `GroupBy` to group by Band, and then loop through that group and list off each venue per band. Let us know via comments if you are stuck. – Stephen Byrne Nov 11 '13 at 20:19
@Justin yes the linq as written wasn't joining properly. I have re-written it with a proper join - but the join doesn't make sense to me so I've commented it out. I usually find that using navigatiuon properties rather than joins makes more sense to me so I've assumed that you can add one named "Venues" – Colin Nov 12 '13 at 08:59
Thanks so much for everyone's help! This has been invaluable and now everything is working properly. :-) – Justin Nov 12 '13 at 12:44

Searching For A Record After A LINQ Query Exception

1 Answers1