I'm calling a Stored Procedure in EF Core 2.0 in the following way.
private async Task<IEnumerable<TEntity>> InvokeStoredProcedureAsync(string entityName)
{
var storedProcedureName = string.Format(CultureInfo.InvariantCulture, "sp_{0}BulkSelect", entityName);
dynamic temp;
using (MyDbContext MyDbContext = new MyDbContext(_options))
{
MyDbContext.Database.OpenConnection();
DbCommand cmd = MyDbContext.Database.GetDbConnection().CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = storedProcedureName;
using (var reader = cmd.ExecuteReader())
{
temp = reader.Cast<Task<IEnumerable<TEntity>>>();
}
}
return await temp;
}
I need to convert from DbDataReader
to Task<IEnumerable<TEntity>>
.
But I'm getting this error when trying to expand the temp variable to see its value.
Invalid attempt to call FieldCount when reader is closed.
Please refer the attached screenshot.
Apart from obvious async code issues, you can't materialize DbDataReader
to class by simply calling Cast
. If it was possible, there wouldn't be a need of micro ORMs like Dapper and similar.
EF Core currently does not expose a public way to do that. But if TEntity
is a model entity class, you can simply use the FromSql
method:
private async Task<IEnumerable<TEntity>> InvokeStoredProcedureAsync(string entityName)
{
var storedProcedureName = string.Format(CultureInfo.InvariantCulture, "sp_{0}BulkSelect", entityName);
using (var db = new MyDbContext(_options))
{
var result = await db.Set<TEntity>().FromSql(storedProcedureName).ToListAsync();
return result;
}
}
Make sure the SP returns all expected columns by the TEntity
mapping.
In async method, no need to cast to task. That will happen by itself. reader object has single row. It should be read each row at a time.
var temp = new List<TEntity>();
using (var reader = await cmd.ExecuteReaderAsync()) {
while(await reader.ReadAsync()) {
temp.add(CreateEntity(reader)) ;
}
}
return temp;
// this is tightly coupled to object. Generic methods can be made
// to convert reader to object
public TEntity CreateEntity(SqlDataReader reader) => new TEntity()
{
Id = reader.GetInt32("id"),
Col1 = reader.GetString("col1"),
Col2 = reader.GetString("col2"),
Col3 = reader.GetString("col3")
};