Intro

If you’re using Entity Framework 4 (EF4) and its ESQL query syntax its more than likely that after executing a query you’ll end up with a collection of DbDataRecord to deal with.
This happens when the query returns an anonymous type.

DbDataRecord object reminded me the DbDataReader from the plain old ADO.net where the result is an abstract item that can have everything in it. To get it, all we have to do is iterate through the DbDataReader and get the data by column name or index.
Remember? Yeah, it’s the same one on EF4!

Environment

I’ll be using:


Querying with ESQL

I’m going to show 3 examples from the easier to the more complex one where we need to convert that DbDataRecord into a custom type we made.

Simple query

Lets just get some typed data, say all the Customers!
Using SELECT VALUE item FROM is like the SELECT * FROM in T-SQL but we’re also saying that the result is of a specific type, in this case, Customer.

using (AdventureWorksLT2008Entities ctx = new AdventureWorksLT2008Entities())
{
 string query = 
    string.Format(
        "SELECT VALUE item FROM {0}.Customers AS item", 
        ctx.DefaultContainerName
    );
 ObjectQuery<customer> customersQuery = 
    new ObjectQuery<customer>(query, ctx);
}

Anonymous Query

Now we want to get all records but only the FirstName, LastName and CompanyName columns.

If we try to use the previous code and just change the query EF4 will give you a nice yellow screen of death saying:

The specified cast from a materialized 'System.Data.Objects.MaterializedDataRecord' type to the 'BlogDemos.EF4DbDatarecord.Website.Customer' type is not valid.


To make this work we have to use ObjectQuery as follows.

using (AdventureWorksLT2008Entities ctx = new AdventureWorksLT2008Entities())
{
 string query = string.Format("SELECT item.FirstName, item.LastName, item.CompanyName FROM {0}.Customers AS item", ctx.DefaultContainerName);
 ObjectQuery<DbDataRecord> customersQuery = new ObjectQuery<DbDataRecord>(query, ctx);
}

Anonymous Query and Type Convertion

Ok, but now we have all the data as an anonymous type not as a Customer. This is good if you only want to display/use that raw data but:

  1. What if we wanted to turn this list of anonymous types into a list of customers?
  2. And if you get a little more ambitious and want to get a list of a custom type of yours that happen to have properties with the same name as the columns you're retrieving on the query?


No problem, just use these extension methods and you’re back on track.

public static class AnonymousTypeConversion
{

 /// <summary>
 /// Converts a single DbDataRwcord object into something else.
 /// The destination type must have a default constructor.
 /// </summary>
 /// <typeparam name="T"></typeparam>
 /// <param name="record"></param>
 /// <returns></returns>
 public static T ConvertTo<T>(this DbDataRecord record)
 {
  T item = Activator.CreateInstance<T>();
  for (int f = 0; f < record.FieldCount; f++)
  {
   PropertyInfo p = item.GetType().GetProperty(record.GetName(f));
   if (p != null &amp;&amp; p.PropertyType == record.GetFieldType(f))
   {
    p.SetValue(item, record.GetValue(f), null);
   }
  }

  return item;
 }

 /// <summary>
 /// Converts a list of DbDataRecord to a list of something else.
 /// </summary>
 /// <typeparam name="T"></typeparam>
 /// <param name="list"></param>
 /// <returns></returns>
 public static List<T> ConvertTo<T>(this List<DbDataRecord> list)
 {
  List<T> result = (List<T>)Activator.CreateInstance<List<T>>();

  list.ForEach(rec =>
  {
   result.Add(rec.ConvertTo<T>());
  });

  return result;
 }

}

Now I’m going to create a custom Customer type, with fewer properties and a new dumb one called FullName that just concatenates the FirstName and the LastaName.

public class TinyCustomer
 {
  public string FirstName { get; set; }
  public string LastName { get; set; }
  public string CompanyName { get; set; }

  public string FullName
  {
   get
   {
    return (string.IsNullOrEmpty(FirstName) ? 
            string.Empty : FirstName) + " " + 
            (string.IsNullOrEmpty(LastName) ? string.Empty : LastName);
   }
  }
 }

Now lets get a list of TinyCustomer from that Anonymous query:

using (AdventureWorksLT2008Entities ctx = new AdventureWorksLT2008Entities())
{
 string query = 
    string.Format(
        "SELECT item.FirstName, item.LastName, item.CompanyName FROM {0}.Customers AS item", 
        ctx.DefaultContainerName
    );
 ObjectQuery<DbDataRecord> customersQuery = new ObjectQuery<DbDataRecord>(query, ctx);

 lblQueryString.Text = query;

 var tinyCustomers = customersQuery.ToList().ConvertTo<DemoTypes.TinyCustomer>();
}

Conclusion

I plan to write about dynamic queries with EF4/ESQL soon where I find this particularly useful.
I did a sample project with all the code presented here, just download the demo project, install the AdventureWorks db on your SQL Server instance and configure the connections string on the web.config.

Downloads

Demo project
AdventureWorks LT 2008 Demo Database

Have fun!