"ConnectionString has not been initialized" error when calling two different repositories

Question

I am using ASP.NET 5 / ASP.NET Core with EF7 / EF Core

I have the following repository class that uses ASP.NET 5's dependency injection

using Microsoft.Data.Entity;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace MyNamespace
{
    public class PersonRepository :IPersonRepository
    {
        private MyDbContext _db;

        public MyRepository(MyDbContext db) 
        {
            _db = db;
        }

        public async Task<IEnumerable<Person>> Search()
        {
            var table = from p in _db.Persons
                        select p;

            return await table.ToListAsync();
        }            
    }
    public interface IPersonRepository
    {
        Task<IEnumerable<Person>> Search();
    }
}

Calling from my ASP.NET MVC controller I can call search successfully and get the data.

public class PersonController : Controller
{
    private IPersonRepository _personRepository;

    public PersonController (IPersonRepository personRepository){
        _personRepository= personRepository;
    }

    public async Task<IActionResult> PersonAction()
    {
        var result = await _personRepository.Search();
    }
}

However something odd happens when I call another repository method call of a stored proc in another repository before this working repository call.

Stored proc

public async Task<List<sp_GetList_Result>> sp_GetList()        
{  
    const string query = "exec [dbo].[sp_GetList];";
    var list = new List<sp_GetList_Result>();

    using (var connection = (SqlConnection)_db.Database.GetDbConnection())
    {
        using (var command = new SqlCommand(query, connection))
        {         
            connection.Open();
            using (var reader = await command.ExecuteReaderAsync())
            {
                while (reader.Read())
                {
                    var row = new sp_GetList_Result();
                    //populate row

                    list.Add(row);
                }
            }
            connection.Close();               
        }
    }
    return list;   
}

Action Method:

public async Task<IActionResult> PersonAction()
{
    var list = await _anotherRepository.GetList();
    var result = await _personRepository.Search();
}

Suddenly await _personRepository.Search(); throws an exception

InnerException = {"The ConnectionString property has not been initialized."}

I explicitly comment out connection.Close(); line but it still gives me the same connectionstring property has not been initialized error message.

Answer 1

The problem does not lie with calling connection.Close(), but rather with the connection.Dispose() call, which is called when exiting the using block. In my case, replacing using (var connection = (SqlConnection)_db.Database.GetDbConnection()) with

var connection = (SqlConnection)_db.Database.GetDbConnection();
try {
  // db stuff
}
finally {
  connection.Close();
}

worked fine, but I am still to understand what actually happens. Looks like the dispose creates an incosistent state for Database object internals that are used for getting the DbConnection.

Answer 2

In my code above I changed

using (var connection = (SqlConnection)_db.Database.GetDbConnection()) 

to

var connection = (SqlConnection)_db.Database.GetDbConnection();

and then I don't close the connection which the using end bracket would do.