Im using visual studio, postgresql database and ado.net entity data model. In the connectionstring, Im unable set MultipleActiveResultSets=True
.
Usually when I connect to sql server with MultipleActiveResultSets=True
, it works fine. but i cannot set the same with postgresql database.
When I use this, I got the following error
There is already an open DataReader associated with this Command which
must be closed first.
How do I solve this problem.
Best Answer
Multiple Active Result Sets (MARS) is a feature introduced in SQL Server 2005 and is not available in other database systems like postgres so you won't be able to turn it on in the connection string.
The error you are facing is an outcome of trying to perform two queries on one open data reader. When using ie Entity Framework this usually happens when you have Lazy Loading turned on and the lazy properties are loaded in the same reader as the parent entites. For example a code similiar to this could produce this error:
In the 1st line no data is fetched as we only have prepared a Linq query. When we start the
foreach
aDataReader
is opened and collection of users that meets the our conditions is queried but the reader is not closed. Then insideforeach
we reach to the Address property of User which is lazy loaded. This lazy load causes a query execution on the same openDataReader
and that's when the exception occurs. If i wanted to get rid of the error i could simply add aToList()
(or anything causing the query to perform) to the end of the line like this:var users = context.Users.Where(u => u.FirstName.StartsWith("Ha")).ToList();
Hope this will help you.