MultipleActiveResultSets for postgresql and ado.net entity data model

Question

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.

Answer 1

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:

var users = context.Users.Where(u => u.FirstName.StartsWith("Ha"));
foreach (var user in users)
{
    Console.WriteLine(user.Address.StreetName);
}

In the 1st line no data is fetched as we only have prepared a Linq query. When we start the foreach a DataReader is opened and collection of users that meets the our conditions is queried but the reader is not closed. Then inside foreach we reach to the Address property of User which is lazy loaded. This lazy load causes a query execution on the same open DataReader and that's when the exception occurs. If i wanted to get rid of the error i could simply add a ToList() (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.

Answer 2

Just add preload reader=true in your postgresQL connection string.

  <connectionStrings>

    <add name="PostgresQL Npgsql" connectionString="server=srvubuntu01;user id=postgres;password=postgres;database=WinUnified;preload reader=true" providerName="Npgsql" />

  </connectionStrings>