Hydrating a DataTable from an EntityDataReader - part 1

Article
11/27/2007

Okay so about 5 posts back I promised:

"...At this point we have an enumeration of all EntitySets available. In the next post I explain how we can use that…"

Well better late than never...

If you remember last time I showed you this piece of code:

MetadataWorkspace workspace = conn.GetMetadataWorkspace();

var sets = from container in

workspace.GetItems<EntityContainer>(DataSpace.CSpace)

from set in container.BaseEntitySets

where set is EntitySet

select set;

foreach (EntitySet set in sets)

{

...

}

Here we have a list of EntitySets . Lets imagine we need to pick one and run a 'get everything' query against it.

How would you do that?

1) Get the eSQL:

string eSQL = string.Format("SELECT VALUE (E) FROM {0}.{1} AS E",
set.EntityContainer.Name,
set.Name);

2) Run the query:

EntityCommand command = new EntityCommand(eSQL, connection);
using (EntityDataReader reader = command.ExecuteReader(CommandBehavior.SequentialAccess))
{
...
}

In case you are wondering I've explained before why CommandBehavior.SequentialAccess is required.

Still the question is: What do we do now?

Well the how about using the metadata to dump the information to the console...

3) Dump data:
The first step is to get the EntityType found in the EntitySet from this we can get the names of all the properties or columns in the reader:

EntityType type = set.ElementType as EntityType;

while (reader.Read())
{
    bool firstCol = true;
    foreach (EdmProperty prop in type.Properties)
    {
        Console.Write("{2}{0}='{1}'",
prop.Name,
reader[prop.Name].ToString(),
firstCol ? "" : ", ");
        firstCol = false;
    }
    Console.WriteLine();
}

So If you run this against Northwind's Categories you get something like this:

Which is nice in a kind useless and impractical way.

So the real question is: Can we do something really useful? Yes!

Like perhaps dumping the results in a DataTable so we can serialize across a network boundary? Of course!

Now normally hydrating a DataReader into DataTable is really easy, for example:

using (SqlConnection connection = new SqlConnection(connstr))
{
    connection.Open();
    SqlCommand command = new SqlCommand("SELECT * FROM [Categories]", connection);
    SqlDataAdapter adapter = new SqlDataAdapter(command);
    DataTable table = new DataTable();
    adapter.Fill(table);

return table;
}

Under the covers the SqlDataAdapter is opening a SqlDataReader based on the select command. And even further under the covers it uses SqlDataReader.GetSchemaTable() to update the DataTable with the right shape, i.e. adding columns with the correct columns and names.

Unfortunately while EntityDataReader.GetSchemaTable() exists, if you call it you get an exception:

Now there are lots of really technical reason why this didn't get into version one of the Entity Framework, but none of those reasons is any consolation if you want to convert your EntityDataReader into a DataTable today, so I won't go into them here.

Instead I'll show you a work around. Well I will in part 2 anyway...

Hydrating a DataTable from an EntityDataReader - part 1

Additional resources