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

Ankit Ankit · Accepted Answer · 2016-07-20T19:44:18

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

Database connection error from Azure site

1 Answers