How to convert from DbDataReader to Task<IEnumerable<TEntity>> in Entity Framework Core 2.0?

Question

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.

Answer 1

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.