I am trying to run a below query in my ASP.NET MVC project, but I am getting an error "there is already an open datareader associated with this command which much be closed first" in C#, how can I resolve it?
Here is the C# code, which I am using
using (var context = new bwavenueEntities())
{
context.Database.CommandTimeout = 10000;
var CompanyDetails = context.CompanyDetailsTables.Where(a => a.CompanyDetailsTableTenantId == TenantId && a.IsActive == true).OrderByDescending(a => a.Id).FirstOrDefault();
if (CompanyDetails == null)
{
var CheckIfPreviousCompanyExistsWithTenant = context.CompanyDetailsTables.Where(a => a.CompanyDetailsTableTenantId == TenantId).OrderByDescending(a => a.Id).FirstOrDefault();
if (CheckIfPreviousCompanyExistsWithTenant != null)
{
id = CheckIfPreviousCompanyExistsWithTenant.Id;
}
}
else
{
id = CompanyDetails.Id;
}
GetAddressCheck = context.CompanyAddresses.Include(a => a.CompanyUser)
.Where(a => a.AddressLine1.ToLower().Contains(searchTerm.ToLower())
|| a.AddressLine2.ToLower().Contains(searchTerm.ToLower())
|| a.City.ToLower().Contains(searchTerm.ToLower())
|| a.CompanyUser.MainCompanyName.ToLower().Contains(searchTerm.ToLower())
&& a.IsActive != false && a.CompanyUser.IsActive != false && a.CompanyUser.MainUserid == id )
.AsEnumerable();
GetAddressCheck = GetAddressCheck.Where(a => a.CompanyUser.CompanyDetailsTable.CompanyDetailsTableTenantId == TenantId).ToList();
}Not sure what is wrong, anyhelp is appreciated, thanks
Answered by:- vikas_jk
This can happen if you execute a query while iterating over the results from another query. In your case, you need to change your C# query
GetAddressCheck = context.CompanyAddresses.Include(a => a.CompanyUser)
.Where(a => a.AddressLine1.ToLower().Contains(searchTerm.ToLower())
|| a.AddressLine2.ToLower().Contains(searchTerm.ToLower())
|| a.City.ToLower().Contains(searchTerm.ToLower())
|| a.CompanyUser.MainCompanyName.ToLower().Contains(searchTerm.ToLower())
&& a.IsActive != false && a.CompanyUser.IsActive != false && a.CompanyUser.MainUserid == id && a.CompanyUser.CompanyDetailsTable.CompanyDetailsTableTenantId == TenantId)
.ToList();//change it to list and combine both queryAs you were trying to using .Where() condition in .AsEnumerable(), I have updated your query code above.
Usually, adding .ToList() after your.Include().Where() will likely resolve this error.
In other cases, you can resolve this by adding MultipleActiveResultSets=true to the provider part of your connection string, something like this
<connectionStrings>
<add name="IdentityConnection" connectionString="Data Source=(LocalDb)\v11.0;AttachDbFilename=|DataDirectory|\IdentityDb.mdf;Integrated Security=True;MultipleActiveResultSets=true;" providerName="System.Data.SqlClient" />
</connectionStrings>but it is not recommended solution to make MultipleActiveResultSets=true, you should always improve your query, as this error is result of Bad written query.
Answered by:- pika
After .AsEnumerable(), Simply use .ToList() so to convert object read from db to list to avoid re-read of data.
OR
Simply Enabling MARS in web.config will work which is a workaround, but NOT a solution to the problem
Connection String will look like this
<add name="MainConnection"
connectionString="Data Source=(LocalDb)\v11.0;AttachDbFilename=|DataDirectory|\IdentityDb.mdf;Integrated Security=True;
MultipleActiveResultSets=true;"
providerName="System.Data.SqlClient" />Hope it helps.
Subscribe to our weekly Newsletter & Keep getting latest article/questions in your inbox weekly
Buy us a coffee
Become a Patron