Click here to Skip to main content
15,867,568 members
Articles / Programming Languages / C#

EF4 DbDataRecord ConvertTo

Rate me:
Please Sign up or sign in to vote.
5.00/5 (1 vote)
1 Feb 2011CPOL2 min read 30.5K   1   3
EF4 DbDataRecord ConvertTo

Introduction

If you're using Entity Framework 4 (EF4) and its ESQL query syntax, it's 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 of 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

Let's 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.

C#
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<DbDataRecord> as follows:

C#
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 Conversion

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.

C#
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 && 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 LastName.

C#
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 let's get a list of TinyCustomer from that Anonymous query:

C#
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

Have fun!
Alex

This article was originally posted at http://www.instanceofanobject.com/feeds/posts/default

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Architect
Switzerland Switzerland
Senior IT Consultant working in Switzerland as Senior Software Engineer.

Find more at on my blog.

Comments and Discussions

 
QuestionBroken link Pin
Claire Streb10-Jul-12 0:40
Claire Streb10-Jul-12 0:40 
The link to downloading the demo project is broken. Any chance you can upload the .zip file to this website?
Claire Streb
http://clairenstreb.brinkster.net

AnswerRe: Broken link Pin
AlexCode10-Jul-12 2:33
professionalAlexCode10-Jul-12 2:33 
GeneralRe: Broken link Pin
Claire Streb26-Jul-12 6:39
Claire Streb26-Jul-12 6:39 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.