There is already an open datareader associated - error in C#


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?

datareader-alread-open-C-sharp-min.png

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


Asked by:- neena
0
: 194 At:- 12/16/2019 8:32:54 AM
C# c# sql datareader already open c# sqldatareader already open






1 Answers
profileImage 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 query

As 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.

1
At:- 12/19/2019 11:11:25 AM
My issue was badly written query, updated it as pointed by you and it worked, thanks 0
By : neena - at :- 12/23/2019 6:09:50 AM






Login/Register to answer
Or
Register directly by posting answer/details

Full Name *

Email *




By posting your answer you agree on privacy policy & terms of use

Subscribe Now

Subscribe to our weekly Newsletter & Keep getting latest article/questions in your inbox weekly