The ConnectionString property has not been initialized

Question

I am using entity framework to make 2 queries, one after the another, the first one always works fine, but the second one always returns me: The ConnectionString property has not been initialized.

If I change the order of the methods, the same thing happen, the first one works fine, the second one throws this exception.

the code I use in the page is as follows:

>  var count = RequestBaseBL.GetGenericResultsCount(query.QuerySql);    
> 
>                     var datatable = RequestBaseBL.GetGenericResults(query.QuerySql, 0);

and in my DAL

public int  GetGenericResultsCount(string strsql)
            {
                using (var connection = (SqlConnection)_context.Database.Connection)
                {
                    var adapter = new SqlDataAdapter(strsql, connection);
                    var results = new DataSet();
                    adapter.Fill(results, "Results");
                    return results.Tables["Results"].Rows.Count;
                }
            }


        public DataTable GetGenericResults(string strsql, int pageIndex)
            {
                StringBuilder sb = new StringBuilder();
                sb.Append("WITH MyPagedData as ( ");
                int indexFrom = strsql.IndexOf("from");
                sb.Append(strsql.Substring(0, indexFrom));
                sb.Append(", ");
                sb.Append("ROW_NUMBER() OVER(ORDER BY RequestBaseId DESC) as RowNum ");
                sb.Append(strsql.Substring(indexFrom));
                sb.Append(") ");
                sb.Append("SELECT * from MyPagedData where RowNum between @StartIndex and @StartIndex + 10");


                using(var connection = (SqlConnection)_context.Database.Connection)
                {
                    var adapter = new SqlDataAdapter(sb.ToString(), connection);
                    adapter.SelectCommand.Parameters.Add("@StartIndex", SqlDbType.Int).Value = pageIndex;
                    var results = new DataSet();
                    adapter.Fill(results, "Results");
                    return results.Tables["Results"];
                }
            }

Answer 1

In general, the using construct should be used when you create an object assigned to the variable, either with the new operator or though a factory method. The Connection property of Database in DbContext creates the connection only if it does not exist already; in all other cases, the property simply returns an existing one.

In your code, the first invocation gets a "live" connection, operates on it, and then closes it through the call of Dispose implicitly performed by using. At this point, the Database of DbContext has a reference to a released object. The second invocation picks up that released object, and tries to use it, triggering an error.

To fix this, simply replace using with assignments:

var connection = (SqlConnection)_context.Database.Connection;