SqlDataReader Connection Property Null

Question

I get a strange problem where I can return the results of a call from a stored procedure, but the code fails retrospectively.

public IEnumerable<T> ExecuteStoredProcedure<T>(string storedProcedureName, IDataMapper<T> mapper, IDictionary<string, object> parameters) { using (var connection = new SqlConnection(connectionString)) { using (var cmd = new SqlCommand(storedProcedureName, connection)) { cmd.CommandType = CommandType.StoredProcedure; foreach (var key in parameters.Keys) { cmd.Parameters.AddWithValue(key, parameters[key]); } connection.Open(); SqlDataReader reader = cmd.ExecuteReader(); //return MapRecordsToDTOs(reader, mapper); //let test: IEnumerable<T> result = MapRecordsToDTOs(reader, mapper); var x = (new List<T>(result)).Count; System.Diagnostics.Debug.WriteLine(x); return result; } } } private static IEnumerable<T> MapRecordsToDTOs<T>(SqlDataReader reader, IDataMapper<T> mapper) { if (reader.HasRows) { while (reader.Read()) { System.Diagnostics.Debug.WriteLine(reader["Id"]); //what going on... yield return mapper.MapToDto((IDataRecord)reader); } } } 

Calling this code shows that the variable x always represents the number of lines that I expect to see from the call to my stored procedures.

In addition, my debug output shows the identifier values ​​that I expect to see.

However, after these results are returned, I get the error An exception of type 'System.InvalidOperationException' occurred in System.Data.dll but was not handled in user code from the line if (reader.HasRows) (i.e. already completed). The browser from which I call this request shows HTTP Error 502.3 - Bad Gateway .

I suspect the reason is a system that calculates the ID and x values ​​for debugging separately, as it will return real user output. Thus, it performs a lazy operation to get IEnumerable values ​​at the moment when it should return them; only at this point using statements called the dispose methods, and therefore the connection to the reader is null (this is what I see when I check the properties of the reader variable during debugging).

Has anyone seen this behavior before / is this a bug? or did I just miss something obvious?

---

Additional code:

 public interface IDataMapper<T> { T MapToDto(IDataRecord record); } public class CurrencyMapper: IDataMapper<CurrencyDTO> { const string FieldNameCode = "Code"; const string FieldNameId = "Id"; const string FieldNameName = "Name"; const string FieldNameNum = "Num"; const string FieldNameE = "E"; const string FieldNameSymbol = "Symbol"; public CurrencyMapper() { } public CurrencyDTO MapToDto(IDataRecord record) { var code = record[FieldNameCode] as string; var id = record[FieldNameId] as Guid?; var name = record[FieldNameName] as string; var num = record[FieldNameNum] as string; var e = record[FieldNameE] as int?; var symbol = record[FieldNameSymbol] as char?; return new CurrencyDTO(id, code, num, e, name, symbol); } } public class CurrencyRepository { const string SPReadAll = "usp_CRUD_Currency_ReadAll"; readonly SqlDatabase db; public CurrencyRepository() { db = new SqlDatabase(); //stick to SQL only for the moment for simplicity } public IEnumerable<CurrencyDTO> GetCurrencyCodes() { var mapper = new CurrencyMapper(); return db.ExecuteStoredProcedure(SPReadAll, mapper); } } public class CurrencyDTO { readonly Guid? id; readonly string code; readonly string num; readonly int? e; readonly string name; readonly char? symbol; public CurrencyDTO(Guid? id,string code,string num,int? e,string name, char? symbol) { this.id = id; this.code = code; this.num = num; this.e = e; this.name = name; this.symbol = symbol; } public Guid? Id { get { return id; } } public string Code { get { return code; } } public string Num { get { return num; } } public int? E { get { return e; } } public string Name { get { return name; } } public char? Symbol { get { return symbol; } } } 

Answer 1

I temporarily used a workaround that resolves this issue.

It works:

 private static IEnumerable<T> MapRecordsToDTOs<T>(SqlDataReader reader, IDataMapper<T> mapper) { var list = new List<T>(); //use a list to force eager evaluation if (reader.HasRows) { while (reader.Read()) { list.Add(mapper.MapToDto((IDataRecord)reader)); } } return list.ToArray(); } 

Unlike the original:

 private static IEnumerable<T> MapRecordsToDTOs<T>(SqlDataReader reader, IDataMapper<T> mapper) { if (reader.HasRows) { while (reader.Read()) { yield return mapper.MapToDto((IDataRecord)reader); } } } 

The difference is that I move the code affected by the iterator so that it only iterates over the results in the list; and does not rely on a compiler that intelligently understands the requirements associated with IDisposable objects.

I understand that the compiler should be able to handle this for me (confirmed here: fooobar.com/questions/679645 / ... ), so I suspect this is a bug in the compiler.

Noted here: https://connect.microsoft.com/VisualStudio/feedback/details/3113138

Additional demo code here: https://gist.github.com/JohnLBevan/a910d886df577e442e2f5a9c2dd41293/