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

Question

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

asp.net-core c# ef-core-2.0 entity-framework entity-framework-core

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.

vivek nuna

Popular Answer

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")
    };

async - msdn

Prateek

View more on Stack Overflow

Ivan Stoev · Accepted Answer · 2/2/2018 7:40:41 AM

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.

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

Question

Fastest Entity Framework Extensions

Accepted Answer

Popular Answer

Performance

Expression Evaluator

Related