creating ExecuteQuery method

[Andrus]

I need to create ExecuteQuery() method with signature:

System.Collections.Generic.IEnumerable<TEntityt>
ExecuteQuery<TEntity>(string selectCommand)

selectCommand is sql SELECT statement like "SELECT Name, Id, City FROM
Customers"


This function should use Data Reader to get data from SQL server database.
If returned columns name are the same as TEntity entity property names it
should returns those columns values as entity properties.
Where to find any sample C# 3.5 implementation which can be used for this ?

Andrus.

 

[Marc Gravell]

You just love to set challenges ;-p

How about the following (which should work on 2.0)... if you are doing
this lots, then look for HyperDescriptor to get significantly better
reflection performance (~100 x faster) - the only thing you'd need to
change in the code would be to add the
TypeDescriptionProviderAttribute, or call
HyperTypeDescriptionProvider.Add... the latter works well if added in
a static ctor (perhaps as part of Read<T>)

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;

class Employee
{
public string FirstName { get; set; }
public string LastName { get; set; }
public int EmployeeID { get; set; }
public DateTime BirthDate { get; set; }
}
static class Program
{
[STAThread]
static void Main()
{
foreach (Employee emp in Read<Employee>("SELECT FirstName,
LastName FROM Employees"))
{
Console.WriteLine("{0} {1}", emp.FirstName, emp.LastName);
}
}
const string CS = @"Data Source=datachange;Initial
Catalog=Northwind;Integrated Security=True";

static IEnumerable<T> Read<T>(string command) where T : new()
{
using (SqlConnection conn = new SqlConnection(CS))
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = command;
conn.Open();
using (SqlDataReader reader =
cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
if (reader.Read())
{
// prepare a buffer and look at the properties
object[] values = new object[reader.FieldCount];
PropertyDescriptor[] props = new
PropertyDescriptor[values.Length];
PropertyDescriptorCollection allProps =
TypeDescriptor.GetProperties(typeof(T));
for (int i = 0; i < props.Length; i++)
{
props = allProps.Find(reader.GetName(i),
true);
}
do
{ // walk the data
reader.GetValues(values);
T t = new T();
for (int i = 0; i < props.Length; i++)
{
if (props != null) props.SetValue(t,
values);
}

yield return t;
} while (reader.Read());
}
while (reader.NextResult()) { } // ensure any trailing
errors caught
}
}
}

}

 

[Marc Gravell]

Oh - while I think of it, you could probably "or"
CommandBehavior.SingleResult into ExecuteReader as a micro-
optimisation.

Additionally - some code to illustrate more optimisation of the
properties (i.e. a single call to TypeDescriptor.GetProperties(),
etc):

change the "allProps" line to:
PropertyDescriptorCollection allProps =
PropertyHelper<T>.GetProperties();

with supporting class:

static class PropertyHelper<T>
{
private static readonly PropertyDescriptorCollection
properties;
public static PropertyDescriptorCollection GetProperties()
{
return properties;
}
static PropertyHelper()
{
// add HyperDescriptor (optional) and get the properties
HyperTypeDescriptionProvider.Add(typeof(T));
properties = TypeDescriptor.GetProperties(typeof(T));
// ensure we have a readonly collection
PropertyDescriptor[] propArray = new
PropertyDescriptor[properties.Count];
properties.CopyTo(propArray, 0);
properties = new PropertyDescriptorCollection(propArray,
true);
}
}

 

[Andrus]

Marc,

Oh - while I think of it, you could probably "or"
CommandBehavior.SingleResult into ExecuteReader as a micro-
optimisation.

Additionally - some code to illustrate more optimisation of the
properties (i.e. a single call to TypeDescriptor.GetProperties(),
etc):

thank you very much. Excellent.

Can I commit your sample in DbLinq source code as ExecuteQuery
implementation (http://code.google.com/p/dblinq2007/issues/detail?id=50) ?
I modified it to work with any ADO .NET provider.

In DLinq (i.e database) applications reflection speed seems to be
unimportant. Data access speed over internet is a magnitude lower and
increasing reflection speed even 100x does not increase application speed at
all.
Using HyperDescriptor requires including additional class file in DLinq and
testing.

For this reason HyperDescriptor is turned off by defalt. I maked
HyperDescriptor conditional using #if HyperDescriptor and added link to your
article to CodeProject in code
if someone wants to play with it.
Will the patch attached to link above look OK ?

How to modify this code so that if poperty returned by query does not exist
in type, it will be added to created entity for data binding in DataGridView
?
Or is it better to use MS Dynamic Linq library to create new object from
scratch in this case ?

Andrus.

 

[Marc Gravell]

Can I commit your sample in DbLinq source code as ExecuteQuery

implementation

If you like...

I modified it to work with any ADO .NET provider.

And (ahem) took full credit I notice (except for HyperDescriptor, fair
enough)

In DLinq (i.e database) applications reflection speed seems to be
unimportant.

Fine; then don't use the extra stuff (which you haven't); that is why I like
this implementation - it is trivial to connect/disconnect it ;-p

Will the patch attached to link above look OK ?

Hard to tell without a working test rig; looks reasonable though...

How to modify this code so that if poperty returned by query does not
exist in type, it will be added to created entity for data binding in
DataGridView

This takes me back to some conversations (with you) in October? November?
You can weite runtime-extensible objects using TypeDescriptionProvider etc
without too much heartache, but you need somewhere to put the data...
ideally inside the record object so that it gets collected (GC) with the
record.
You could do something like this with a common base class for extensible
objects, but it isn't trivial.

Or is it better to use MS Dynamic Linq library to create new object from
scratch in this case ?

I guess this would be possible - but you'd have to pass this in as the <T>,
so you would have to use reflection to invoke the method. In this case,
personally I'd recommend getting the columns right, or using something
pre-rolled like DataTable.

Marc

 

[Andrus]

Marc,

And (ahem) took full credit I notice (except for HyperDescriptor, fair
enough)

I'm sorry, this is mistake. I add you as author of this code.

This takes me back to some conversations (with you) in October? November?
You can weite runtime-extensible objects using TypeDescriptionProvider etc
without too much heartache, but you need somewhere to put the data...
ideally inside the record object so that it gets collected (GC) with the
record.
You could do something like this with a common base class for extensible
objects, but it isn't trivial.

As understand from this discussion that TypeDescriptor can be used to extend
object to show columns in DataGridView easily.

I hoped that HyperDescriptor allows to add new properties for DataGridView
binding in the fly.

I'd like easily to visualise any SELECT command results for quick design in
scripts in customer sites like

var result = db.ExecuteQuery<object>("SELECT * FROM Customers, Orders WHERE
Order.ID=Customer.ID");
myDataGridView.DataSource = result;

and then grid automagically visualizes the result.

I guess this would be possible - but you'd have to pass this in as the
<T>, so you would have to use reflection to invoke the method. In this
case, personally I'd recommend getting the columns right, or using
something pre-rolled like DataTable.

For debugging and for quick result visualization/verification dynamic
entities whould be handy.

I'm not sure what solution to try for this:

Your extensible TypeDescriptor sample code which you posted previous year

or

MS Dynamic Linq library CreateClass() method:

1. create datareader and retrieve data

2. Create property list from DataReader data DynamicProperty[] props =
.....

3. Create dynamic type using Dynamic Linq library

Type type = DynamicExpression.CreateClass(props);

4. Create and return list of entities based on this type

Andrus.

P.S. Sorry again for credit mistake, I will fix it.

Do you want to see the result? I think you are the first person in the
world who created OpenSource ExecuteQuery<Tresult>() implementation.
Since DbLinq license is very liberal, all commerical DLinq driver writers
like Frans and open source programmers can use your implementation as
template.
So this code must show best coding style possible.

 

[Marc Gravell]

As understand from this discussion that TypeDescriptor can be used to

extend object to show columns in DataGridView easily.

Yes it can be done; but I wouldn't say easily. I have posted several
extensible object examples, but I'm not sure this is the best way to go
here. It introduces unnecessary complexity for something that can be already
be done in other ways.

I hoped that HyperDescriptor allows to add new properties for DataGridView
binding in the fly.

No; that isn't what it does. It replaces the standard reflection-based
(PropertyInfo) implementation

I'd like easily to visualise any SELECT command results for quick design
in scripts in customer sites like <snip>

Sounds like DataTable would do this job perfectly well. I'm a pragmatist...

Do you want to see the result?

If it is somewhere easily accessible I might take a look... I'm not planning
on jumping through any hoops...

Marc

 

[Andrus]

MArc,

Sounds like DataTable would do this job perfectly well. I'm a
pragmatist... why make life hard?

DLinq uses only DataReader. There are no any DataTable objects.

So this requires to switch back to DataTable and ADO .NET FillDataSet()
methods.
Entity methods require POCO type objects. DataTable rows do not fill into
this category: row columns are not properties. They do not have getters and
setters and cannot instantiated without DataTable.

So DataRow cannot used as replacement of entity POCO type.

So only way is to create type dynamically.

At the end of the day, <T> implies a known data structure (of type T) -
not "make it up yourself"...

Yes, it is not reasonable to use ExecuteQuery<TResult>() method.

For this we must create non-generic ExecuteQuery() method which returns
ArrayList of entity objects.
This also fixes the lack of covariant generic types in C#.

I.e in C# 4:

ArrayList Orders = ExecuteQuery("SELECT * FROM Customers JOIN Orders USING
(Id)");
dynamic {
Orders.Amout += 10;
}

in 3.5 we can use reflection instead of dynamic.

Andrus.

 

[Andrus]

Marc,

If it is somewhere easily accessible I might take a look... I'm not
planning on jumping through any hoops...

Here is your code committed:

http://dblinq2007.googlecode.com/svn/trunk/DbLinq/Vendor/Implementation/Vendor.cs


public virtual IEnumerable<TResult>
ExecuteQuery<TResult>(DbLinq.Linq.DataContext context, string sql, params
object[] parameters)
where
TResult : new() {
using (IDbCommand command =
context.DatabaseContext.CreateCommand()) {
string sql2 = ExecuteCommand_PrepareParams(command, sql,
parameters);
command.CommandText = sql2;
command.Connection.Open();
using (IDataReader reader = command.ExecuteReader(
CommandBehavior.CloseConnection |
CommandBehavior.SingleResult)) {
if (reader.Read()) {
// prepare a buffer and look at the properties
object[] values = new object[reader.FieldCount];
PropertyDescriptor[] props = new
PropertyDescriptor[values.Length];
#if HyperDescriptor
// Using Marc Gravell HyperDescriptor gets significantly better
reflection performance (~100 x faster)
// http://www.codeproject.com/KB/cs/HyperPropertyDescriptor.aspx
PropertyDescriptorCollection allProps =
PropertyHelper<TResult>.GetProperties();
#else
PropertyDescriptorCollection allProps =
TypeDescriptor.GetProperties(typeof(TResult));
#endif
for (int i = 0; i < props.Length; i++) {
string name = reader.GetName(i);
props = allProps.Find(name, true);
}
do { // walk the data
reader.GetValues(values);
TResult t = new TResult();
for (int i = 0; i < props.Length; i++) {
// TODO: use char type conversion delegate.
if (props != null) props.SetValue(t, values);
}
yield return t;
} while (reader.Read());
}
while (reader.NextResult()) { } // ensure any trailing
errors caught
}
}
}


1. I'm not sure about connection closing issue. Is the connection closed
immediately if yield returns last entity ?
Or should this code modified so that if last entity is returned, connection
is closed before final yield return is executed ?

2. I dont understand this:

while (reader.NextResult()) { } // ensure any trailing errors caught

Why this is required? Why to read other result sets, maybe to ignore them
silently ?
Can this line safely removed ?

Andrus.

 

[Marc Gravell]

1. I'm not sure about connection closing issue. Is the connection closed

immediately if yield returns last entity ?

Yes; and also closed because of "using".

2. I dont understand this:
...
Can this line safely removed ?

Up to you. I got bit (hard) once when a SQL error *followed* the first
grid. Because of the way TDS streams work, and since I closed it after
the first grid, my code never reported an error. Hence I am now
paranoid, and I always follow a TDS stream to its conclusion with the
code as posted.

Marc

 

[Marc Gravell]

Other thoughts:

* might want to think about "struct" T, or discount it with T : class;
as it stands it will be boxed repeatedly and changed discared. Would
need "object foo = t" after create, and use foo (not t) in SetValue
* if you don't use T : class, might want to think about T =
Nullable<TSomethingElse>; here new(T) = null...
* might want to ignore readonly properties
* might want to think about a mapping attribute between SQL column
name and the object property name

Who said ORM was easy? ;-p

 

[Andrus]

Marc,

* might want to think about "struct" T, or discount it with T : class;
as it stands it will be boxed repeatedly and changed discared. Would
need "object foo = t" after create, and use foo (not t) in SetValue
* if you don't use T : class, might want to think about T =
Nullable<TSomethingElse>; here new(T) = null...

I looked into msdn doc and find that its method signature does not have
new() constraint.
So they use some other way, no idea how.
They also allow to maps results directly to fields.
I don't understand how to implement your suggestions to change the code.

* might want to ignore readonly properties

MS doc does not describe RO property behaviour.
So I'm not sure maybe ro property can considered as programmer error. In
this case it is not reasonable to ignore it silently.

* might want to think about a mapping attribute between SQL column
name and the object property name

Yes this will match more closely to Linq-SQL.
I do'nt now how to implement it, so this remains unresolved issue by me.

Andrus.

 

[Marc Gravell]

So they use some other way, no idea how.
A simple approach would be an initializer Expression created at
runtime. Activator.CreateInstance would work but would be slower.
Alternatively, simply use reflection to invoke a private metod that
*does* have the constraint (when the public method doesn't) - then it
only gets checked once.

They also allow to maps results directly to fields.

Read your OP; you asked about properties... I maintain that the sample
did most of what you asked!
Again - an initializer Expression would be a simple fix here. This
would also address the issue with value-types, since inside the
Expression (once compiled to a delegate) it would be using direct
member access, not Reflection nor ComponentModel. Also much faster.

MS doc does not describe RO property behaviour.
So I'm not sure maybe ro property can considered as programmer error. In
this case it is not reasonable to ignore it silently.

Fine - but a more deliberate attempt to check up-front an raise a
specific error would be good practice.

Yes this will match more closely to Linq-SQL.
I do'nt now how to implement it, so this remains unresolved issue by me.

I don't know about the mapping process that the open-source code uses,
so I can't advise. However the actual process is simple.

I'll tell you what. I'm having a bit of a family weeked (4 day weekend
here ;-p) - but on Tuesday I'll rewrite my sample using
System.Expression to show the alternative construction. How's that?

Note that Expression *absolutely* precludes dynamic object models. The
two concepts are not compatible (I'm sure we've discussed this
before).

Marc

 

[Marc Gravell]

Et voila; note this still wouldn't be considered complete; there are
lots of things that it should do (consider nulls, perhaps return T? as
a fully intialized T, etc). But this gives the idea:

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq.Expressions;
using System.Reflection;


class Employee
{
public string Forename { get { return FirstName; } set { FirstName
= value; } }
private string FirstName;
public string LastName { get; set; }
private int EmloyeeID { get; set; }
public DateTime BirthDate { get; set; }
}


static class Program
{
[STAThread]
static void Main()
{
foreach (Employee emp in Read<Employee>("SELECT FirstName,
LastName FROM Employees"))
{
Console.WriteLine("{0} {1}", emp.Forename, emp.LastName);
}
}
const string CS = @"Data Source=WO51950201XPLAP\SQLEXPRESS;Initial
Catalog=Northwind;Integrated Security=True";

static Func<IDataReader, T> CreateInitializer<T>(IDataReader
template)
{
if (template == null) throw new
ArgumentNullException("template");
var readerParam = Expression.Parameter(typeof(IDataReader),
"reader");
Type entityType = typeof(T), readerType = typeof(IDataRecord);
List<MemberBinding> bindings = new List<MemberBinding>();

Type[] byOrdinal = {typeof(int)};
MethodInfo defaultMethod = readerType.GetMethod("GetValue",
byOrdinal);
NewExpression ctor = Expression.New(entityType); // try this
first...
for (int ordinal = 0; ordinal < template.FieldCount; ordinal+
+)
{
string name = template.GetName(ordinal);
// TODO: apply mapping here (via attribute?)

// get the lhs of a binding
const BindingFlags FLAGS = BindingFlags.Instance |
BindingFlags.Public | BindingFlags.NonPublic;
MemberInfo member = (MemberInfo)
entityType.GetProperty(name, FLAGS) ??
(MemberInfo)entityType.GetField(name, FLAGS);
if (member == null) continue; // doesn't exist
Type valueType;
switch (member.MemberType)
{
case MemberTypes.Field:
valueType = ((FieldInfo)member).FieldType;
break;
case MemberTypes.Property:
if (!((PropertyInfo)member).CanWrite) continue; //
read only
valueType = ((PropertyInfo)member).PropertyType;
break;
default:
throw new
NotSupportedException(string.Format("Unexpected member-type: {0}",
member.MemberType));
}

// get the rhs of a binding
MethodInfo method = readerType.GetMethod("Get" +
valueType.Name, byOrdinal);
Expression rhs;
if (method != null && method.ReturnType == valueType)
{
rhs = Expression.Call(readerParam, method,
Expression.Constant(ordinal, typeof(int)));
}
else
{
rhs = Expression.Convert(Expression.Call(readerParam,
defaultMethod, Expression.Constant(ordinal, typeof(int))), valueType);
}
bindings.Add(Expression.Bind(member, rhs));
}
return Expression.Lambda<Func<IDataReader, T>>(
Expression.MemberInit(ctor, bindings),
readerParam).Compile();
}
static IEnumerable<T> Read<T>(string command) where T : new()
{
using (SqlConnection conn = new SqlConnection(CS))
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = command;
conn.Open();
using (SqlDataReader reader =
cmd.ExecuteReader(CommandBehavior.CloseConnection |
CommandBehavior.SingleResult))
{
if (reader.Read())
{
Func<IDataReader, T> objInit =
CreateInitializer<T>(reader);
do
{ // walk the data
yield return objInit(reader);
} while (reader.Read());
}
while (reader.NextResult()) { } // ensure any trailing
errors caught
}
}
}


}

 

[Andrus]

Marc,

Et voila; note this still wouldn't be considered complete; there are
lots of things that it should do (consider nulls, perhaps return T? as
a fully intialized T, etc). But this gives the idea:

Thank you. I made two minor changes:

1. Removed new() constraint.
2. Added BindingFlags.IgnoreCase.

Tested and find that it works.
May I commit it to DbLinq ?

Andrus.

 

[Marc Gravell]

Yeah - I remembered about new after posting, but I guesed you'd figure
it was no longer needed ;-p

The ignore case looks handy - but personally I'd perfer metadata here
- i.e. perhaps some kind of [DbField(...)] against either the field or
property. But if it works...

I also think the following are essential:
* nulls (conditional ternary with the IDataReader "is null" method as
the first argument)
* caching of the delegate against the T/columns - otherwise it may
leak

I'll hopefully post something on Tuesday with more of this in it...

Marc

 

[Andrus]

Marc,

Yeah - I remembered about new after posting, but I guesed you'd figure
it was no longer needed ;-p

Will it work with struct also really (havent tried) ?

The ignore case looks handy - but personally I'd perfer metadata here
- i.e. perhaps some kind of [DbField(...)] against either the field or
property. But if it works...

PostgreSQL returns always normally all column names is lower case.
I can use quoted column names to return case sensitive "CustomerID" as
column name probably.
DbLinq uses usual GetAttribute() functions to retrieve attributes. I can
probably post methods which can be used to retrieve attributes if you want.

E.q. for creater performance, if Storage= attribute is present in property,
value should be stored to field specified in this attribute like regular
DLinq does.

I also think the following are essential:
* nulls (conditional ternary with the IDataReader "is null" method as
the first argument)
* caching of the delegate against the T/columns - otherwise it may
leak

I'll hopefully post something on Tuesday with more of this in it...

I looked into DbLinq code and found that it "normal" RowEnumerator compiler
already uses
compiled delegates. It compiles every member getter probably separately.
So my commit duplicates essential part of code.
Merging your code with existing RowenumeratorComplier code allows to
implement the following features which are not present in ExecuteQuery<>:

1. Using mapping attribute
2. Enabling object tracking
3. Alwing to use global conversion delegate.

However I do'nt know DbLinq code enough to perform such big merge.
Maybe this is too complicated and it may happen that current solution is
optimal.
So this code duplication remains.

Andrus.

 

[Andrus]

The ignore case looks handy - but personally I'd perfer metadata here

- i.e. perhaps some kind of [DbField(...)] against either the field or
property. But if it works...

MS ExecuteQuery<T>() doc describes that it performs two-pass match:
first case sensitive and if this fails then case insensitive.

Andrus.

 

[Marc Gravell]

Will it work with struct also really (havent tried) ?

It should do.

it performs two-pass match: first case
sensitive and if this fails then case insensitive

Easy enough.

 

[Marc Gravell]

Here's another version
* does 2-pass match as described
* handles null reference-type values (string, byte[])
* handles null Nullable<T> value-type values (int? etc)
* handles (for entity T) class, struct and Nullable<struct>
* caches and re-uses compiled delegates (thread-safe)

I'm not going to advise on the metadata aspect since I don't know (and don't
wish to know) enough about the exact model used by this project.

Probably my last long code-dump on this topic (but feel free to ask follow
ups etc) - I don't want to turn this forum into a repo change-log ;-p

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Linq.Expressions;
using System.Reflection;

class Employee
{
public string Forename { get { return FirstName; } set { FirstName =
value; } }
private string FirstName;
public string LastName { get; set; }
private int EmloyeeID { get; set; }
public DateTime BirthDate { get; set; }
public int? Foo { get; set; }
public string bar { get; set; }
}
/// <summary>
/// Compares arrays of objects using the supplied comparer (or default is
none supplied)
/// </summary>
class ArrayComparer<T> : IEqualityComparer<T[]> {
private readonly IEqualityComparer<T> comparer;
public ArrayComparer() : this(null) { }
public ArrayComparer(IEqualityComparer<T> comparer)
{
this.comparer = comparer ?? EqualityComparer<T>.Default;
}
public int GetHashCode(T[] values)
{
if (values == null) return 0;
int hashCode = 1;
for (int i = 0; i < values.Length; i++)
{
hashCode = (hashCode * 13) + comparer.GetHashCode(values);
}
return hashCode;
}
public bool Equals(T[] lhs, T[] rhs)
{
if (ReferenceEquals(lhs, rhs)) return true;
if (lhs == null || rhs == null || lhs.Length != rhs.Length) return
false;
for (int i = 0; i < lhs.Length; i++)
{
if (!comparer.Equals(lhs, rhs)) return false;
}
return true;
}
}
/// <summary>
/// Responsible for creating and caching reader-delegates for compatible
/// column sets; thread safe.
/// </summary>
static class InitializerCache<T>
{
static readonly Dictionary<string[], Func<IDataReader, T>> readers
= new Dictionary<string[], Func<IDataReader, T>>(
new ArrayComparer<string>(StringComparer.InvariantCulture));

public static Func<IDataReader, T> GetInitializer(string[] names)
{
if (names == null) throw new ArgumentNullException();
Func<IDataReader, T> initializer;
lock (readers)
{
if (!readers.TryGetValue(names, out initializer))
{
initializer = CreateInitializer(names);
readers.Add((string[])names.Clone(), initializer);
}
}
return initializer;
}

private static Func<IDataReader, T> CreateInitializer(string[] names)
{
Trace.WriteLine("Creating initializer for: " + typeof(T).Name);
if (names == null) throw new ArgumentNullException("names");

var readerParam = Expression.Parameter(typeof(IDataReader),
"reader");
Type entityType = typeof(T),
underlyingEntityType = Nullable.GetUnderlyingType(entityType) ??
entityType,
readerType = typeof(IDataRecord);
List<MemberBinding> bindings = new List<MemberBinding>();

Type[] byOrdinal = { typeof(int) };
MethodInfo defaultMethod = readerType.GetMethod("GetValue",
byOrdinal),
isNullMethod = readerType.GetMethod("IsDBNull", byOrdinal);
NewExpression ctor = Expression.New(underlyingEntityType); // try
this first...
for (int ordinal = 0; ordinal < names.Length; ordinal++)
{
string name = names[ordinal];
// TODO: apply mapping here (via attribute?)

// get the lhs of a binding
const BindingFlags FLAGS = BindingFlags.Instance |
BindingFlags.Public | BindingFlags.NonPublic;
MemberInfo member =
(MemberInfo)underlyingEntityType.GetProperty(name, FLAGS) ??
(MemberInfo)underlyingEntityType.GetField(name, FLAGS) ??
(MemberInfo)underlyingEntityType.GetProperty(name, FLAGS |
BindingFlags.IgnoreCase) ??
(MemberInfo)underlyingEntityType.GetField(name, FLAGS |
BindingFlags.IgnoreCase);

if (member == null) continue; // doesn't exist
Type valueType;
switch (member.MemberType)
{
case MemberTypes.Field:
valueType = ((FieldInfo)member).FieldType;
break;
case MemberTypes.Property:
if (!((PropertyInfo)member).CanWrite) continue; // read
only
valueType = ((PropertyInfo)member).PropertyType;
break;
default:
throw new
NotSupportedException(string.Format("Unexpected member-type: {0}",
member.MemberType));
}
Type underlyingType = Nullable.GetUnderlyingType(valueType) ??
valueType;

// get the rhs of a binding
MethodInfo method = readerType.GetMethod("Get" +
underlyingType.Name, byOrdinal);
Expression rhs;
if (method != null && method.ReturnType == underlyingType)
{
rhs = Expression.Call(readerParam, method,
Expression.Constant(ordinal, typeof(int)));
}
else
{
rhs = Expression.Convert(Expression.Call(readerParam,
defaultMethod, Expression.Constant(ordinal, typeof(int))), underlyingType);
}

if (underlyingType != valueType)
{ // Nullable<T>; convert underlying T to T?
rhs = Expression.Convert(rhs, valueType);
}

if (underlyingType.IsClass || underlyingType != valueType)
{ // reference-type of Nullable<T>; check for null
// (conditional ternary operator)
rhs = Expression.Condition(
Expression.Call(readerParam, isNullMethod,
Expression.Constant(ordinal, typeof(int))),
Expression.Constant(null, valueType), rhs);
}
bindings.Add(Expression.Bind(member, rhs));
}
Expression body = Expression.MemberInit(ctor, bindings);
if (entityType != underlyingEntityType)
{ // entity itself was T? - so convert
body = Expression.Convert(body, entityType);
}
return Expression.Lambda<Func<IDataReader, T>>(body,
readerParam).Compile();
}
}

static class Program
{
[STAThread]
static void Main()
{
Go();
Go();
Go();
}
static void Go()
{
foreach (Employee emp in Read<Employee>("SELECT FirstName, LastName,
NULL AS Foo, NULL AS bar FROM Employees"))
{
Console.WriteLine("{0} {1}", emp.Forename, emp.LastName);
}
}
const string CS = "TODO"; // northwind


static IEnumerable<T> Read<T>(string command)
{
using (SqlConnection conn = new SqlConnection(CS))
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = command;
conn.Open();
using (SqlDataReader reader =
cmd.ExecuteReader(CommandBehavior.CloseConnection |
CommandBehavior.SingleResult))
{
if (reader.Read())
{
string[] names = new string[reader.FieldCount];
for(int i = 0 ; i < names.Length ; i++) {
names = reader.GetName(i);
}
Func<IDataReader, T> objInit =
InitializerCache<T>.GetInitializer(names);
do
{ // walk the data
yield return objInit(reader);
} while (reader.Read());
}
while (reader.NextResult()) { } // ensure any trailing
errors caught
}
}
}


}