Hydrating a DataTable from an EntityDataReader - Part 2

Article
11/28/2007

See yesterday's post for some context...

So we can't rely on EntityDataReader.GetSchemaTable() and a DataAdapter to do the filling of our DataTable for us. We have to do it ourselves.

As with any problem the first step is to define the steps:

Get a DataTable with a shape that matches the EntityDataReader.
Enumerate the reader adding each row in turn to the DataTable.

I find that the best way to do this sort of thing is to just write the function you want to see, filling in the missing bits as you go:

public static DataTable LoadTable(EntityDataReader reader)
{
    DataTable table = LoadSchema(reader.DataRecordInfo);
    table.FillWith(reader.Enumerate());
    return table;
}

Okay so obviously LoadSchema(..) takes care of getting the right shape and the second line is using a couple of extension method to actually flush the reader into the DataTable.

The key to the first line is that EntityDataReader implements IExtendedDataRecord which gives you access to metadata about the contents of the reader via the DataRecordInfo.

So lets dig in and implement the LoadSchema(..) method:

public static DataTable LoadSchema(DataRecordInfo info)
{
    DataTable table = new DataTable(info.RecordType.EdmType.FullName);
    var columns = info.FieldMetadata
                .Select(field => ConvertToColumn(field))
                .Where(column => column != null);
    foreach (DataColumn column in columns)
        table.Columns.Add(column);

return table;
}

Okay so we create an empty DataTable with a name matching the EdmType's name.

Then we enumerate through the FieldMetadatas in the DataRecordInfo.FieldMetadata collection converting them each to a DataColumn with a ConvertToColumn(...) function.

NB: I'm cheating a little here because I know I want ConvertToColumn(...) function to handle things like Associations , ComplexTypes etc by returning null. Which means I need to filter the null columns out before adding them to the DataTable . Indeed it is this sort of situation that highlights the flexibility of using Sequence operators directly rather than LINQ, you can chain as many commands together as you want, and unlike LINQ Select is not necessary the end of the sequence.

Okay so seems like this method dodges the tricky bullet, i.e. creating a column. So lets look at how we do that:

protected static DataColumn ConvertToColumn(FieldMetadata field)
{
    //Work out the CLR type associated with the EdmType
    EdmType type = field.FieldType.TypeUsage.EdmType;
    var clrTypes = from p in oc.GetPrimitiveTypes()
                   where p.GetEdmPrimitiveType().FullName == type.FullName
                   select p.ClrEquivalentType;

Type clrType = clrTypes.FirstOrDefault();

This bit of code is the key to the whole thing, we simply need to find a primitive type with the same name as at the EdmType in the current field. Once we have the Primitive type we can get the corresponding CLR type, which is the type of DataColumn we will create. So what then is oc? Well this is its declaration:

/// <summary>
/// We hold onto an ObjectItemCollection because this
/// gives us access to the EF's primitive types
/// </summary>
readonly static ObjectItemCollection oc = new ObjectItemCollection();

So now we have a clrType, or null if we are looking at a column with a sub record or sub reader in it (i.e. we have an column with a ComplexType or an Association)

The rest of the function is pretty simple, we simply return null if we don't know an appropriate CLR type or we create a DataColumn with that type and the fields name.

//This is to ensure we don't attempt to lift row(s). At least not in this version.
if (clrType == null) return null;

//Construct a column
return new DataColumn(field.FieldType.Name, clrType);
}

So now we have the DataTable, we simply need to fill it, I do that by using a couple of general purpose extension methods:

If you remember I wrote:

table.FillWith(reader.Enumerate());

The first method thing to look at is reader.Enumerate() which is an extension method to make enumerating through an IDataReader nicer:

public static IEnumerable<IDataRecord> Enumerate(this IDataReader reader)
{
    using (reader)
    {
        while (reader.Read())
            yield return reader;
    }
}

Finally the actually filling is really simple, you simply loop through all the IDataRecordcreating a new DataRow and copying data from IDataRecordinto the row by looping through the columns of the table:

public static void FillWith(this DataTable table, IEnumerable<IDataRecord> records)
{
    foreach (IDataRecord record in records)
    {
        DataRow row = table.NewRow();
        foreach (DataColumn column in table.Columns)
        {
            row[column.ColumnName] = record[column.ColumnName];
        }
        table.Rows.Add(row);
    }
}

And tada we are done.

Hydrating a DataTable from an EntityDataReader - Part 2

Additional resources