Converting MySqlDataReader records to ObservableCollection

Question

I am trying to convert records from MySqlDataReader to ObservableCollection<T> but I always end up with exception - Unable to cast object of type 'System.Data.Common.DataRecordInternal' to type 'System.Data.IDataReader'

From Jon Skeet's answer over here and referring SLaks comment in the same answer, I ended up writing below code but still I don't find how am getting that exception.

ObservableCollection<ColumnItems> DataList;
using (MySqlCommand cmd = new MySqlCommand(_query))
{
       await Global.currentConnection.OpenAsync();
       cmd.Connection = Global.currentConnection;
       MySqlDataReader reader = cmd.ExecuteReader();
       var dataDetails = reader.Cast<IDataReader>().Select(x => new ColumnItems
       {
             col1 = x["col1"] is DBNull ? null : x["col1"].ToString(),
             col2 = x["col2"] is DBNull ? null : x["col2"].ToString(),
             col3 = x["col3"] is DBNull ? null : x["col3"].ToString(),
             col4 = x["col4"] is DBNull ? null : x["col4"].ToString()
       });
       DataList = new ObservableCollection<ColumnItems>(dataDetails);
       //Exception while assigning data
       await Global.currentConnection.CloseAsync();
}

I also tried replacing var dataDetails with ObservableCollection<ColumnItems> dataDetails but again failed with no luck. I ended up finding very few resources for above exception and couldn't gather much knowledge to solve this exception. How can I fill data into my ObservableCollection<T>?

Nope @Steve.. No exception there but `DataList = new ObservableCollection(dataDetails);` is throwing exception.. — Guruprasad J Rao, Dec 07 '16 at 14:14
its because linq has delayed execution. it is not executed until its used. You can try reader.Cast().ToList() and it should throw the same exception that you are seeing — Steve, Dec 07 '16 at 14:26
@GuruprasadRao Steve is right: IDataReader just is not IEnumerable. You can fill empty DataTable instead (if your table is not very big). `DataTable dt = new DataTable(); dt.Load(reader); var dataDetails=dt.AsEnumerable().Select(...` — vitalygolub, Dec 07 '16 at 14:48
@Steve.. You are absolutely correct. It was throwing exception when I used `ToList()` too.. — Guruprasad J Rao, Dec 07 '16 at 17:50
@vitalygolub.. Seems promising. Let me try this and let you know.. :) — Guruprasad J Rao, Dec 07 '16 at 17:50
@vitalygolub.. Perfect.. That worked as expected. Could you please add it as answer and I shall accept it.. :) — Guruprasad J Rao, Dec 08 '16 at 03:31

score 2 · Accepted Answer · answered Dec 08 '16 at 09:06

2

Just for karma :-) IDataReader is not IEnumerable and has no method to produce IEnumerable Fortunately, DataTable is collection of rows and can be converted to enumerable If your table is not very big

DataTable dt = new DataTable(); 
dt.Load(reader); 
var dataDetails=dt.AsEnumerable().Select(...

answered Dec 08 '16 at 09:06

vitalygolub

735
3
16

Oh.. So what's the approach if the table is very big?? Around 5200 records.. :o – Guruprasad J Rao Dec 08 '16 at 09:42
@GuruprasadRao use the reader to read row by row. TBH 5200 is not big – Steve Dec 12 '16 at 22:41
Sorry for slowness: 5200 is definitely small. It is opinion based, dependent of memory you can use to store the table. If it can be for example 52000 you can still process DataReader one by one and preserve List , but if can become 52000000, may be it is time to implement Ienumerator from reader (lack reset() method, need to requery) or inherit DBcontext instead (actualy never used MySql.Data.Entity, am not sure) – vitalygolub Dec 13 '16 at 10:04

score 1 · Answer 2 · answered Sep 12 '22 at 09:40

Sorry for answering a very old post but I happen to have this same issue/scenario with my code. I agree with the comments above that if the expected result set is small, just load the results to a DataTable and query against that instead. But if somehow you need to use LINQ directly to a Reader (aka an explicit mapper), what you need to do is to make the Reader implement IEnumerable to make it compatible with LINQ.

I used Jon Skeet's extension that I got from his answer in this post:

public static IEnumerable<T> Select<T>(this IDataReader reader,
                                       Func<IDataReader, T> projection)
{
    while (reader.Read())
    {
        yield return projection(reader);
    }
}

Converting MySqlDataReader records to ObservableCollection

2 Answers2