ExecuteQuery<T> is very slow after refactorings

[Andrus]

I tested with simple query, returning few rows. DataContext.ExecuteQuery<T>
is extremely slow.
It takes 400 ms while same query executed from pgAdmin takes 2 ms.

I tried to profile dbLinq but havent found cause.
Speed degradation seems to be linear to number of columns returned and
occurs in enumerator which returns data form datareader.

I replaced in my application DataContext ExecuteQuery calls with Marc
Gravell original code which I initially commited as ExecuteQuery code to
DbLinq some years ago (below), converatsion is in
http://www.eggheadcafe.com/software/aspnet/31850600/creating-executequery-met.aspx

In this case ExecuteQuery takes only 2 ms to run, 100 times (!) faster.
This does does not handle decimal-> int and other conversions like DbLinq
method, InvalidCastexception occurs in this case.

Andrus.

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

/// <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>
/// Represents a single bindable member of a type
/// </summary>
internal class BindingInfo
{
public bool CanBeNull { get; private set; }
public MemberInfo StorageMember { get; private set; }
public MemberInfo BindingMember { get; private set; }
public BindingInfo(bool canBeNull, MemberInfo bindingMember,
MemberInfo storageMember)
{
CanBeNull = canBeNull;
BindingMember = bindingMember;
StorageMember = storageMember;
}
public Type StorageType
{
get
{
switch (StorageMember.MemberType)
{
case MemberTypes.Field:
return ((FieldInfo)StorageMember).FieldType;
case MemberTypes.Property:
return ((PropertyInfo)StorageMember).PropertyType;
default:
throw new
NotSupportedException(string.Format("Unexpected member-type: {0}",
StorageMember.Name));
}
}
}
}

/// <summary>
/// Responsible for creating and caching reader-delegates for compatible
/// column sets; thread safe.
/// </summary>
static class InitializerCache<T>
{
/// <summary>
/// Cache of all readers for this T (by column sets)
/// </summary>
static readonly Dictionary<string[], Func<IDataRecord,
MyDataContext, T>> convertReaders
= new Dictionary<string[], Func<IDataRecord, MyDataContext, T>>(
new
ArrayComparer<string>(StringComparer.InvariantCultureIgnoreCase)),
vanillaReaders = new Dictionary<string[], Func<IDataRecord,
MyDataContext, T>>(
new
ArrayComparer<string>(StringComparer.InvariantCultureIgnoreCase));

/// <summary>
/// Cache of all bindable columns for this T (by source-name)
/// </summary>
private static readonly SortedList<string, BindingInfo> dataMembers
= new SortedList<string,
BindingInfo>(StringComparer.InvariantCultureIgnoreCase);

static bool TryGetBinding(string columnName, out BindingInfo
binding)
{
return dataMembers.TryGetValue(columnName, out binding);
}

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

static InitializerCache()
{
Type type = typeof(T);
foreach (MemberInfo member in type.GetMembers(FLAGS))
{
if ((member.MemberType & PROP_FIELD) == 0) continue; // only
applies to prop/fields
ColumnAttribute col = Attribute.GetCustomAttribute(member,
typeof(ColumnAttribute)) as ColumnAttribute;
if (col == null) continue; // not a column
string name = col.Name;
if (string.IsNullOrEmpty(name))
{ // default to self
name = member.Name;
}
string storage = col.Storage;
MemberInfo storageMember;
if (string.IsNullOrEmpty(storage) || storage == name)
{ // default to self
storageMember = member;
}
else
{
// locate prop/field: case-sensitive first, then
insensitive
storageMember = GetBindingMember(storage);
if (storageMember == null)
{
throw new InvalidOperationException("Storage member
not found: " + storage);
}
}
if (storageMember.MemberType == MemberTypes.Property &&
!((PropertyInfo)storageMember).CanWrite)
{ // write to a r/o prop?
throw new InvalidOperationException("Cannot write to
readonly storage property: " + storage);
}
// log it...
dataMembers.Add(name, new BindingInfo(col.CanBeNull, member,
storageMember));
}
}

private static MemberInfo FirstMember(MemberInfo[] members)
{
return members != null && members.Length > 0 ? members[0] :
null;
}

public static Func<IDataRecord, MyDataContext, T>
GetInitializer(string[] names, bool useConversion)
{
if (names == null) throw new ArgumentNullException();
Func<IDataRecord, MyDataContext, T> initializer;
Dictionary<string[], Func<IDataRecord, MyDataContext, T>> cache
=
useConversion ? convertReaders : vanillaReaders;
lock (cache)
{
if (!cache.TryGetValue(names, out initializer))
{
initializer = CreateInitializer(names, useConversion);
cache.Add((string[])names.Clone(), initializer);
}
}
return initializer;
}

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

ParameterExpression readerParam =
Expression.Parameter(typeof(IDataRecord), "record"),
ctxParam = Expression.Parameter(typeof(MyDataContext),
"ctx");

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),
convertMethod =
typeof(MyDataContext).GetMethod("OnConvertValue", BindingFlags.Instance |
BindingFlags.NonPublic);

NewExpression ctor = Expression.New(underlyingEntityType); //
try this first...
for (int ordinal = 0; ordinal < names.Length; ordinal++)
{
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, member);
}
//Trace.WriteLine(string.Format("Binding {0} to {1} ({2})",
name, bindingInfo.Member.Name, bindingInfo.Member.MemberType));
Type valueType = bindingInfo.StorageType;

Type underlyingType = Nullable.GetUnderlyingType(valueType)
?? valueType;

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

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

if (bindingInfo.CanBeNull && (underlyingType.IsClass ||
underlyingType != valueType))
{
// reference-type of Nullable<T>; check for null
// (conditional ternary operator)
rhs = Expression.Condition(
Expression.Call(readerParam, isNullMethod,
ordinalExp),
Expression.Constant(null, valueType), rhs);
}
if (useConversion)
{
rhs = Expression.Convert(Expression.Call(ctxParam,
convertMethod, ordinalExp, readerParam,
Expression.Convert(rhs, typeof(object))),
valueType);
}
bindings.Add(Expression.Bind(bindingInfo.StorageMember,
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<IDataRecord, MyDataContext,
T>>(body, readerParam, ctxParam).Compile();
}
}

public class ValueConversionEventArgs : EventArgs
{
internal void Init(int ordinal, IDataRecord record, object value)
{
Ordinal = ordinal;
Record = record;
Value = value;
}
internal ValueConversionEventArgs() { }
public ValueConversionEventArgs(int ordinal, IDataRecord record,
object value)
{
Init(ordinal, record, value);
}
public int Ordinal { get; private set; }
public object Value { get; set; }
public IDataRecord Record { get; private set; }
}

public class MyDataContext
{

// re-use args to miniimze GEN0
private readonly ValueConversionEventArgs conversionArgs = new
ValueConversionEventArgs();
public event EventHandler<ValueConversionEventArgs> ConvertValue;

internal object OnConvertValue(int ordinal, IDataRecord record,
object value)
{
if (ConvertValue == null)
{
return value;
}
else
{
conversionArgs.Init(ordinal, record, value);
ConvertValue(this, conversionArgs);
return conversionArgs.Value;
}
}

public IEnumerable<T> ExecuteQuery<T>(string command, params
object[] parameters)
{

if (parameters == null) throw new
ArgumentNullException("parameters");

using (IDbConnection conn = DataAccessBase.CreateConnection())
// new SqlConnection(Program.CS))
using (IDbCommand cmd = conn.CreateCommand())
{
string[] paramNames = new string[parameters.Length];

for (int i = 0; i < parameters.Length; i++)
{
paramNames[i] = "@p" + i.ToString();
IDbDataParameter 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);
conn.Open();
using (IDataReader 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<IDataRecord, MyDataContext, T> objInit =
InitializerCache<T>.GetInitializer(names, ConvertValue != null);
do
{ // walk the data

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