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.

enter image description here

1
5
2/1/2018 7:17:35 PM

Accepted Answer

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.

6
2/2/2018 7:40:41 AM

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



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow