Database connection error from Azure site

Question

I have started creating a new aspnet core 1 application and got enough done to deploy to azure. I was able to copy my local db to a azure sql db and deploy my app. When I run my app locally (VS2015) against the azure sql database, the app works fine. When I run the app from azure against the azure sql db, i get the following error after logging in to the app.

A database operation failed while processing the request.

InvalidOperationException: There is already an open DataReader associated with this Command which must be closed first. There are pending model changes for ApplicationDbContext In Visual Studio, use the Package Manager Console to scaffold a new migration for these changes and apply them to the database:

PM> Add-Migration [migration name] PM> Update-Database Alternatively, you can scaffold a new migration and apply it from a command prompt at your project directory:

dotnet ef migrations add [migration name] dotnet ef database update

Running Update-Database for each of my contexts completes successfully, but does not fix the issue.

I am happy to share any additional information that is needed.

Edit #1: Added code example

public class CompaniesController : Controller
{
    private readonly SEESContext _context;
    private readonly IEmailSender _emailSender;
    private readonly UserManager<ApplicationUser> _userManager;

    public CompaniesController(
        SEESContext context,
        IEmailSender emailSender,
        UserManager<ApplicationUser> userManager)
    {
        _context = context;
        _emailSender = emailSender;
        _userManager = userManager;
    }
// GET: Companies/MyCompany/
    [Authorize(Roles = RoleNames.CompanyAdministrator)]
    public async Task<IActionResult> MyCompany(string sortOrder, string searchEmployee)
    {
        var company = await _context.Companies.SingleOrDefaultAsync(m => m.CompanyID == Convert.ToInt32(User.Identity.GetCompanyId()));

        var model = new MyCompaniesModel(company);

        model = await PopulateEmployeeData(sortOrder, searchEmployee, company, model);

        model = PopulateJobData(company, model);

        return View(model);
    }
private async Task<MyCompaniesModel> PopulateEmployeeData(string sortOrder, string searchEmployee, Company company, MyCompaniesModel model)
    {
        ViewBag.NameSortParm = string.IsNullOrEmpty(sortOrder) ? "name_desc" : "";
        ViewBag.RoleSortParm = sortOrder == "role_asc" ? "role_desc" : "role_asc";

        var employeeViewModels = new List<EmployeeViewModel>();

        var users = _userManager.Users.Where(x => x.CompanyID == company.CompanyID);

        foreach (var user in users)
        {
            var roles = await _userManager.GetRolesAsync(user);

            employeeViewModels.Add(new EmployeeViewModel { Id = user.Id, Name = user.FullName, Role = roles[0] });
        }

        if (!string.IsNullOrEmpty(searchEmployee))
        {
            employeeViewModels = employeeViewModels.Where(s => s.Name.Contains(searchEmployee)).ToList();
        }

        switch (sortOrder)
        {
            case "name_desc":
                employeeViewModels = employeeViewModels.OrderByDescending(x => x.Name).ToList();
                break;
            case "role_desc":
                employeeViewModels = employeeViewModels.OrderByDescending(x => x.Role).ToList();
                break;
            case "role_asc":
                employeeViewModels = employeeViewModels.OrderBy(x => x.Role).ToList();
                break;
            default:
                employeeViewModels = employeeViewModels.OrderBy(s => s.Name).ToList();
                break;
        }

        model.Employees = employeeViewModels.ToList();

        return model;
    }

    private MyCompaniesModel PopulateJobData(Company company, MyCompaniesModel model)
    {
        var jobs = _context.Jobs.Where(x => x.CompanyId == company.CompanyID && x.IsActive).OrderBy(x => x.Name).ToList();

        var jobViewModels = jobs.Select(job => new JobViewModel { Id = job.JobId, Name = job.Name }).ToList();

        model.Jobs = jobViewModels.ToList();

        return model;
    }

What library are you using to access DB? Are you using Entity Framework or similar ORM, or is it plain SqlConnection? Multiple Active Result Sets will definitely help, but this failure usually means you are doing something wrong with the SqlConnection. — seva titov, Jul 20 '16 at 19:50

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

0

Try enabling Multiple Active Result Sets (MARS) as suggested here.
Try injecting same DbContext instance for all database queries
Try using Include if issue arising out of lazy loading

edited May 23 '17 at 12:14

Community

1
1

answered Jul 20 '16 at 19:44

Ankit

2,448
3
20
33

MARS is turned on! – Michael Wheeler Jul 20 '16 at 19:49
Then, i would try by disposing the additional DbContext instances in function calls with using blocks. Injecting same DbContext instance in all repositories and saving at one place, has also helped me in my scenarios. – Ankit Jul 20 '16 at 20:10
Since I am using asp.net core, the injection is taking place in the controller for me. I will edit the question to show code that is called after login which is not working on azure but is locally. – Michael Wheeler Jul 21 '16 at 15:47
I had added the MARS to the connection string in the appsettings.json file, but just noticed that the connection string on the Azure website was not getting updated. I updated it manually and it now works! – Michael Wheeler Jul 21 '16 at 21:10

Database connection error from Azure site

1 Answers1