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 } } } } } -- You received this message because you are subscribed to the Google Groups "DbLinq" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/dblinq?hl=en.
