Click here to Skip to main content
Click here to Skip to main content
Technical Blog

Tagged as

EF4 DbDataRecord ConvertTo

, 1 Feb 2011 CPOL
Rate this:
Please Sign up or sign in to vote.
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.

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:

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.

public static class AnonymousTypeConversion
{
 /// <span class="code-SummaryComment"><summary>
</span> /// Converts a single DbDataRwcord object into something else.
 /// The destination type must have a default constructor.
 /// <span class="code-SummaryComment"></summary>
</span> /// <span class="code-SummaryComment"><typeparam name="T"></typeparam>
</span> /// <span class="code-SummaryComment"><param name="record"></param>
</span> /// <span class="code-SummaryComment"><returns></returns>
</span> 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;
 }

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

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.

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:

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

License

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

Share

About the Author

AlexCode
Architect
Switzerland Switzerland
Senior IT Consultant working in Switzerland as Senior Software Engineer.
 
Find more at on my blog.

Comments and Discussions

 
QuestionBroken link PinmemberClaire Streb10-Jul-12 1:40 
AnswerRe: Broken link PinmemberAlexCode10-Jul-12 3:33 
GeneralRe: Broken link PinmemberClaire Streb26-Jul-12 7:39 

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

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

| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.141223.1 | Last Updated 1 Feb 2011
Article Copyright 2011 by AlexCode
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid