creating ExecuteQuery method

481 views
Skip to first unread message

Andrus

unread,
Mar 18, 2008, 5:22:10 PM3/18/08
to
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

unread,
Mar 18, 2008, 5:50:55 PM3/18/08
to
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[i] = 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[i] != null) props[i].SetValue(t,
values[i]);
}

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

}

Marc Gravell

unread,
Mar 18, 2008, 6:03:35 PM3/18/08
to
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

unread,
Mar 20, 2008, 9:48:33 AM3/20/08
to
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

unread,
Mar 20, 2008, 10:50:53 AM3/20/08
to
> 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

unread,
Mar 20, 2008, 11:54:54 AM3/20/08
to
Marc,

>> I modified it to work with any ADO .NET provider.
> 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.

>> 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.

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

unread,
Mar 20, 2008, 12:07:09 PM3/20/08
to
> 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...
why make life hard? At the end of the day, <T> implies a known data
structure (of type T) - not "make it up yourself"...

> 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

unread,
Mar 20, 2008, 12:51:18 PM3/20/08
to
MArc,

>> 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... 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

unread,
Mar 20, 2008, 2:06:58 PM3/20/08
to
Marc,

>> 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...

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[i] = 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[i] != null) props[i].SetValue(t, values[i]);
}
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

unread,
Mar 20, 2008, 8:20:56 PM3/20/08
to
> 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

unread,
Mar 20, 2008, 8:23:45 PM3/20/08
to
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

unread,
Mar 21, 2008, 1:20:19 PM3/21/08
to
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

unread,
Mar 22, 2008, 5:31:13 AM3/22/08
to
> 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

unread,
Mar 22, 2008, 10:03:39 AM3/22/08
to
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);

Andrus

unread,
Mar 22, 2008, 6:57:46 PM3/22/08
to
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

unread,
Mar 23, 2008, 6:28:29 AM3/23/08
to
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

unread,
Mar 23, 2008, 7:19:27 PM3/23/08
to
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

unread,
Mar 24, 2008, 5:37:49 AM3/24/08
to
> 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

unread,
Mar 24, 2008, 5:46:54 AM3/24/08
to
> 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

unread,
Mar 25, 2008, 4:11:42 AM3/25/08
to
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[i]);
}
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[i], rhs[i])) 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[i] = reader.GetName(i);


}
Func<IDataReader, T> objInit =

InitializerCache<T>.GetInitializer(names);

Marc Gravell

unread,
Mar 25, 2008, 4:13:19 PM3/25/08
to
For info I have e-mailed a version that uses SqlMetal's
ColumnAttribute to demonstrate mapping. If anybody else is interested
e-mail me ;-p

Crude benchmarks show it slightly faster than raw LINQ-to-SQL, but it
is doing a lot less:
* no change tracking
* no composability
* only homogeneous sets supported
etc

It made an interesting diversion, but I can't think of many cases when
I would use this instead of a data-context (ideally EF when RTM).

Marc

Andrus

unread,
Mar 25, 2008, 4:14:03 PM3/25/08
to
Marc,

> 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)

Thank you very much.
I did minor testing and commited your code.

Andrus.


Andrus

unread,
Mar 26, 2008, 1:12:00 PM3/26/08
to
Marc,

> For info I have e-mailed a version that uses SqlMetal's

Thank you.
I encountered two issues:

1. I need probably for WinForms GUI to trim trailing characters from CHAR
type columns and apply custom encoding conversion for all CHAR database
columns whose lenght is creater than 1.

In application init I'm planning to use

Vendor.StringConversion += (stringConversionEventArgs)=>
stringConversionEventArgs.Data.ToString().TrimEnd();

Code which implements this is:

class Vendor {

public class StringConversionEventArgs {

public object Data;
public Type Type;
public IDataRecord DataRecord;

public StringConversionEventArgs(object data, Type type, IDataRecord
dataRecord) {

Data = data;
Type = type;
DataRecord = dataRecord;
}
}

public static event Action<StringConversionEventArgs> StringConversion;

static string OnStringConversion(object data, Type type, IDataRecord
dataRecord) {

StringConversionEventArgs stringConversionEventArgs =
new StringConversionEventArgs(data, type, dataRecord);

if (StringConversion != null)
StringConversion(stringConversionEventArgs);
return stringConversionEventArgs.Data.ToString();
}
}

Unfortunately I do'nt know hot to add OnStringConversion() method call to
your code.
Also I don't know is this best design pattern to add global conversion
possibility.

2. It does not read properties without ColumnAttribute . MSDN describes
that it should:
<quote>
If a field or property is not mapped, a column with the same name as the
field or property is expected in the resultset.
</quote>

> Crude benchmarks show it slightly faster than raw LINQ-to-SQL, but it
> is doing a lot less:
> * no change tracking

I think it is possible to use Attach() method to add returned entities to
change tracking.
I do'nt know how to determine is the passed type part of DataContext or not.
I havent found DataContext method like

bool BelongsToThisDataContext( Type entity)

which can be used to test is Attach() valid. So I'm plannig to use crude
try/catch to add returned entites to DataContext in
some other method.

> It made an interesting diversion, but I can't think of many cases when
> I would use this instead of a data-context (ideally EF when RTM).

I'm planning to use it for queries against tables in database metadata.
I need to get list of available schemas and estimated number of records.

AFAIK , no one DLinq provides nor EF does not provide way nor generate
classes to access database metadata tables and views.

So ExecuteQuery<>() is the only way without falling back to DataSets.

Also this method allows to create entity type dynamically from script and
use this method to fill this dynamic entity with data.

Andrus.

Marc Gravell

unread,
Mar 26, 2008, 4:03:43 PM3/26/08
to
Both issues mentioned are solveable - although I'd probably do the
conversion thing a little differently.... I'll see what I can do on
the train tomorrow...

Marc

Marc Gravell

unread,
Mar 27, 2008, 1:24:43 AM3/27/08
to

A simple option for your first point (translated columns) would be to
have a facade property that does the translation?

example:

Column(Name="ShipCountry", Storage = "_ShipCountry", DbType =
"NVarChar(15)")]
public string ShipCountryRaw {.......}

public string ShipCountry // could be any Type
{
get { return Reverse(ShipCountryRaw); }
set { ShipCountryRaw = Reverse(value); }
}
private string Reverse(string value) {
if(value == null || value.Length <= 1) return value;
char[] buffer = value.ToCharArray();
Array.Reverse(buffer);
return new string(buffer);
}

This seems to work well, and ShipCountryRaw is used correctly when
reading and writing (either mechanism), and allows WHERE in LINQ-to-
SQL etc based on ShipCountryRaw (but not on our facade ShipCountry).

As an alternative to the above (but less robust) - have you tried
using a non-default Storage? i.e. set Storage to a private property
that does the translation... seems to work fine when *loading* data
for both LINQ-to-SQL and the code as posted (without changes)... but
unfortunately LINQ-to-SQL doesn't seem to use Storage when using
SubmitChanges() [or when building WHERE clauses, but that is more
reasonable]. For these 2 reasons I don't recommend this option.

example:

[Column(Storage = "DbShipCountry", DbType = "NVarChar(15)")]
public string ShipCountry {......}

private string DbShipCountry
{
get { return Reverse(_ShipCountry); }
set { _ShipCountry = Reverse(value); }
}

--------

For your second point (unmapped columns), I was able to do this with
minimal changes:

In CreateInitializer, at the top of the ordinal loop:

string name = names[ordinal];

BindingInfo bindingInfo;
if (!TryGetBinding(name, out bindingInfo))
{ // try implicit binding
MemberInfo member = GetBindingMember(name);
if(member == null) continue; // not bound
bindingInfo = new BindingInfo(true, member);
}

where GetBindingMember is defined in InitializerCache<T> as (note:
moved FLAGS and PROP_FIELD out to the class itself):

const BindingFlags FLAGS = BindingFlags.Instance |
BindingFlags.Public | BindingFlags.NonPublic;

const MemberTypes PROP_FIELD = MemberTypes.Property |
MemberTypes.Field;

private static MemberInfo GetBindingMember(string name)
{
Type type = typeof(T);
return FirstMember(type.GetMember(name, PROP_FIELD, FLAGS))
?? FirstMember(type.GetMember(name, PROP_FIELD, FLAGS |
BindingFlags.IgnoreCase));
}

and used GetBindingMember from the static cctor (search on the
comment):

// locate prop/field: case-sensitive first, then
insensitive
storageMember = GetBindingMember(storage);

Oh; I think I also made the comparer in "readers" case-insensitive.

Marc

Marc Gravell

unread,
Mar 27, 2008, 3:52:04 AM3/27/08
to
One other thing - the DataContext.ExecuteQuery<T> method accepts
parameters in string.Format format... propose tweak as below (focusing
on the handling of "parameters" and subsequent CommandText).

Marc

static IEnumerable<T> ExecuteQuery<T>(string command, params
object[] parameters)
{
if (parameters == null) throw new
ArgumentNullException("parameters");

using (DbConnection conn = new SqlConnection(CS))
using (DbCommand cmd = conn.CreateCommand())
{
string[] paramNames = new string[parameters.Length];
for (int i = 0; i < parameters.Length; i++)
{
paramNames[i] = "@p" + i.ToString();
DbParameter param = cmd.CreateParameter();
param.ParameterName = paramNames[i];
param.Value = parameters[i] ?? DBNull.Value;
cmd.Parameters.Add(param);
}
cmd.CommandType = CommandType.Text;
cmd.CommandText = string.Format(command, paramNames);
// SNIP everything else "as was"

Andrus

unread,
Mar 27, 2008, 6:10:12 AM3/27/08
to
Marc,

> One other thing - the DataContext.ExecuteQuery<T> method accepts
> parameters in string.Format format... propose tweak as below (focusing
> on the handling of "parameters" and subsequent CommandText).

Thank you.
I use actually a bit changed code as shown in the SVN link I posted earlier.
This code includes call to special common method
Executequery_preparaparametes which performs proper parameter substitution.
So this is not an issue for me.

Andrus.

Andrus

unread,
Mar 27, 2008, 6:44:21 AM3/27/08
to
Marc,

> It made an interesting diversion, but I can't think of many cases when
> I would use this instead of a data-context (ideally EF when RTM).

I addition to previus goodie (global custom conversion support)
your version also allows to retrieve partial properties of object without
using your double-projection
extension method as discussed earlier, e.q:

1. Retrieve only customer name to customer object using your
ExecuteQuery<Customer>() (MS version fails probably in this case according
to MSDN doc).
2. Attach() retrieved Customer objects to DataContext
3. Now we can Update name and Delete customers.

Using pure MS code to perform same operations requires to retrieve all
customer properties from db and is thus a magnitude slower.

Andrus.


Andrus

unread,
Mar 27, 2008, 8:05:30 AM3/27/08
to
Marc,

> A simple option for your first point (translated columns) would be to
> have a facade property that does the translation?

> This seems to work well, and ShipCountryRaw is used correctly when


> reading and writing (either mechanism), and allows WHERE in LINQ-to-
> SQL etc based on ShipCountryRaw (but not on our facade ShipCountry).

All columns in my database are fixed length CHAR(n) types.
I need to trim trailing spaces from all database CHAR columns.
Mainly this is required prevent DataDridView displaying three dots ... in
end of narrow columns (I havent found a way to turn this off).

For accented characters I need to apply custom conversion to UTF to make
them propery visible in GUI since they are retried in non-unicode format
from db.

Using your approach for this requires:

1. Use surrogate property names in every place of my code, eq.
ShipCountryRaw instead of ShipCountry
2. Creating custom SQLMetal which generates those surrogate properties
statically.

So it seems that using this is not reasonable.

Only solution I know is before yield return statement in your code :

1. Loop over all string properties
2. Invoke conversion event as described in my previous message separately
for every string property.

This requires to use reflection so probably decreases perfomance.

> As an alternative to the above (but less robust) - have you tried
> using a non-default Storage? i.e. set Storage to a private property
> that does the translation... seems to work fine when *loading* data
> for both LINQ-to-SQL and the code as posted (without changes)... but
> unfortunately LINQ-to-SQL doesn't seem to use Storage when using
> SubmitChanges() [or when building WHERE clauses, but that is more
> reasonable]. For these 2 reasons I don't recommend this option.
>
> example:
>
> [Column(Storage = "DbShipCountry", DbType = "NVarChar(15)")]
> public string ShipCountry {......}
>
> private string DbShipCountry
> {
> get { return Reverse(_ShipCountry); }
> set { _ShipCountry = Reverse(value); }
> }

Storage doc from

http://msdn2.microsoft.com/en-us/library/system.data.linq.mapping.dataattribute.storage.aspx :

wrote:

Gets or sets a private storage *field* ....

So Storage = "DbShipCountry" is not allowed in normal DLinq.

Also I think that this has also the same issues as the first approach.

It may be possible to add conversion property like Frans wrote is
implemented in LLBLgen .
But this also requires changing SQLMetal go generate constant property for
every string column an is thus not reasonable.

> For your second point (unmapped columns), I was able to do this with
> minimal changes:

I applied those changes, moved all code to separate file and marked you as
author in this file.
Should I publish a link to this file in SVN here ?

> Oh; I think I also made the comparer in "readers" case-insensitive.

Currently I need only case insensitive match. So I havent tested case
issues.

Andrus.


Marc Gravell

unread,
Mar 27, 2008, 8:52:57 AM3/27/08
to
My concern is how to introduce a conversion into the pipeline without
crippling things. At one level it would be nice to have it very granular so
that only the properties you care about get converted, but this is at odds
with your need here...


Marc Gravell

unread,
Mar 27, 2008, 9:52:40 AM3/27/08
to
I've e-mailed a variant that allows an event on the context instance. Seems
to work; still quicker than LINQ-to-SQL ;-p

Marc


Marc Gravell

unread,
Mar 27, 2008, 10:26:46 AM3/27/08
to
Ignore "quicker" - I was testing on too small a data-set. The event does get
in the way; I'll e-mail yet another version that considers this... (sheesh)

Marc


Andrus

unread,
Mar 27, 2008, 1:35:46 PM3/27/08
to
Marc,

> Ignore "quicker" - I was testing on too small a data-set. The event does
> get in the way; I'll e-mail yet another version that considers this...
> (sheesh)

Thank you.
I performed minor test, moved conversion class to IVendor and committed it.

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

Andrus.


Andrus

unread,
Mar 29, 2008, 4:50:54 AM3/29/08
to
Marc,

> Ignore "quicker" - I was testing on too small a data-set. The event does
> get in the way; I'll e-mail yet another version that considers this...
> (sheesh)

Some thoughts:

1. ConvertValue event appears to be thread-unsafe (since it uses a member
field as argument event).

2. The method also makes use of reflection to get method names: why not to
use lambda, since this allow refactoring (if a method name changes, the
lambda will follow, but the literal strings describing the method may not).
There are some samples in DLinq RowEnumerator<> (where reflection is
replaced by lambdas)

Andrus.


Marc Gravell

unread,
Mar 30, 2008, 5:19:07 AM3/30/08
to
> 1. ConvertValue event appears to be thread-unsafe (since it uses a member
> field as argument event).
Do you mean the bit where I'm re-using the event-arg? Well, it is
limited to a single context, and I don't know whether an individual
data-context promises thread safety, but yes: I suppose it might be
better to build a shim object that holds the event-arg and presumably
also the event delegate itself - and call the On"..." from that shim,
not the context. My intent was to avoid creating huge volumes of gen-0
event-arg objects.

> 2. The method also makes use of reflection to get method names: why not to
> use lambda

If you mean for the methdos known at compile-time, then yes; I have
myself posted "infoof" implementations using lambdas; but I was simply
trying to use least complexity:
http://groups.google.co.uk/group/microsoft.public.dotnet.languages.csharp/browse_thread/thread/f44dc57a9dc5168d/4805324df6b30218#4805324df6b30218

Reply all
Reply to author
Forward
0 new messages