How to convert from DbDataReader to Task> 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.

Ivan Stoev · Accepted Answer · 2018-02-02T07:40:41.767

6

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.

edited Feb 02 '18 at 07:40

answered Feb 01 '18 at 19:16

Ivan Stoev

195,425
15
312
343

Its giving compilation error. "constraint are not allowed on non-generic declaration" – Vivek Nuna Feb 02 '18 at 05:40
Error "'DbContext.Set()' is a method which is not valid in the given context" – Vivek Nuna Feb 02 '18 at 05:42
Oops, sorry, of course this is part of some generic repository class I guess. And I forgot `()` after `Set`. Fixed. – Ivan Stoev Feb 02 '18 at 07:39

Christoph Fink · Answer 2 · 2018-02-01T15:17:43.230

0

At the time your reader is called it is already closed as the using-block is complete. Also you need to read "row by row". Try the follwoing:

private async Task<IEnumerable<TEntity>> InvokeStoredProcedureAsync(string entityName)
{
    var storedProcedureName = string.Format(CultureInfo.InvariantCulture, "sp_{0}BulkSelect", entityName);
    List<TEntity> temp = new List<TEntity>();
    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 = await cmd.ExecuteReaderAsync())
        {
           while (await reader.ReadAsync())
               temp.Add(reader.Cast<TEntity>()); // ATTENTION: this does not work! (see below)
        }
    }

    return temp;
}

but I think the cast used above does not do what you think. See the following thread for an example how to create an entity out of a reader: Convert rows from a data reader into typed results

edited Feb 01 '18 at 15:17

answered Feb 01 '18 at 15:01

Christoph Fink

22,727
9
68
113

@viveknuna You need to do it inside the `using`-block - did you try that? – Christoph Fink Feb 01 '18 at 15:05
@viveknuna And that exception would be? I guess it's not `Invalid attempt to call FieldCount when reader is closed.` anymore? – Christoph Fink Feb 01 '18 at 15:06
Yes it’s different, I post post it in 15 mins – Vivek Nuna Feb 01 '18 at 15:08
@viveknuna ...already found the problem, thanks to Prateek. Please see my update... – Christoph Fink Feb 01 '18 at 15:20

Prateek · Answer 3 · 2018-02-01T17:55:54.873

0

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

edited Feb 01 '18 at 17:55

answered Feb 01 '18 at 15:06

Prateek

144
3
14

What I need to do here temp.add(//##add code to read perhaps each column ##) ; – Vivek Nuna Feb 01 '18 at 16:12
updated the code. generic way to do reader to object can be done via link provided by @ChrFin - [here](https://stackoverflow.com/questions/1202935/convert-rows-from-a-data-reader-into-typed-results) – Prateek Feb 01 '18 at 17:58
@Prateek Getting compilation error. "Cannot create an instance of the variable type 'TEntity' because it does not have the new() constraint" – Vivek Nuna Feb 01 '18 at 18:17
TEntity - is it abstract class? if yes then you need to inherit this class and initialize the derived class... – Prateek Feb 01 '18 at 18:21
Yes it is, but there are around 20 classes which are inheriting this abstract class. So according to your solution I will have to write multiple if else statements to create different different object, because different classes have different properties – Vivek Nuna Feb 01 '18 at 19:00
well, if you are using procedures to get data you have to do this. You can make generic method use reflection and map property of a class to to column names from procedures. This would only move the mapping logic and few line of repetitive code. – Prateek Feb 01 '18 at 19:30
well @IvanStoev has added the answer. – Prateek Feb 02 '18 at 14:10

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

3 Answers3