title: LINQ Enabling ADO.NET layer runs slower
body: <p>I had some dataaccess code that I Linq enabled to raise the level of abstraction in the business layer but the performance is not excellent for large sets of data. Any Ideas on how to make it faster ?</p>
  
  <pre><code>public class StronglyTypedDataSource : IDisposable
  {
      //
      private static readonly string _selectAll = "SELECT * FROM ";
      private static readonly string _insert = "INSERT INTO ";
      private static readonly string _update = "UPDATE ";
      private static readonly string _remove = "DELETE FROM ";
      private static readonly string _where = " WHERE ";
      private static readonly string _value = " VALUES ";
      private static readonly string _and = " AND ";
      private static readonly string _set = " SET ";
      private static readonly string _scopeIdentity = "SELECT LAST_INSERT_ID()";
      private static string _connectionString;       
      private MySqlConnection connection;
      private MySqlCommand command;
      private MySqlDataReader Reader;
      private int _indexer = 0;
  
      public StronglyTypedDataSource()
      {
          _connectionString = GetDefaultConnectionString();
      }
  
      /// &lt;summary&gt;
      /// Returns a record from a database table.
      /// &lt;/summary&gt;
      /// &lt;typeparam name="T"&gt;&lt;/typeparam&gt;
      /// &lt;param name="predicate"&gt;&lt;/param&gt;
      /// &lt;param name="optionalTableName"&gt;&lt;/param&gt;
      /// &lt;returns&gt;&lt;/returns&gt;
      public T Read&lt;T&gt;(Expression&lt;Func&lt;T, bool&gt;&gt; predicate, string optionalTableName = null) where T : class
      {
  
          T returnValue = default(T);
          CreateReadCommand&lt;T&gt;(predicate, optionalTableName);
          var holder = GetDataRows&lt;T&gt;(1).ToList();
          if(holder != default(T) &amp;&amp; holder.Any())
              returnValue = holder[0];
          connection.Close();
          return returnValue;
      }
  
      /// &lt;summary&gt;
      /// Returns a sequence of records from a database table.
      /// &lt;/summary&gt;
      /// &lt;typeparam name="T"&gt;&lt;/typeparam&gt;
      /// &lt;param name="optionalTableName"&gt;&lt;/param&gt;
      /// &lt;returns&gt;&lt;/returns&gt;
      public IEnumerable&lt;T&gt; ReadAll&lt;T&gt;(string optionalTableName = null) where T : class
      {
          return ReadAll&lt;T&gt;(null, optionalTableName);
      }
  
      /// &lt;summary&gt;
      /// Returns a sequence of records from a database table.
      /// &lt;/summary&gt;
      /// &lt;typeparam name="T"&gt;&lt;/typeparam&gt;
      /// &lt;param name="predicate"&gt;&lt;/param&gt;
      /// &lt;param name="optionalTableName"&gt;&lt;/param&gt;
      /// &lt;returns&gt;&lt;/returns&gt;
      public IEnumerable&lt;T&gt; ReadAll&lt;T&gt;(Expression&lt;Func&lt;T, bool&gt;&gt; predicate, string optionalTableName = null) where T : class
      {          
          CreateReadCommand&lt;T&gt;(predicate, optionalTableName);
          var returnValues = GetDataRows&lt;T&gt;().ToList();
          connection.Close();
          return returnValues;
      }
  
      /// &lt;summary&gt;
      /// Delete records fromt a database table that matches the predicate.
      /// &lt;/summary&gt;
      /// &lt;typeparam name="T"&gt;&lt;/typeparam&gt;
      /// &lt;param name="predicate"&gt;&lt;/param&gt;
      /// &lt;param name="optionalTableName"&gt;&lt;/param&gt;
      public void Delete&lt;T&gt;(Expression&lt;Func&lt;T, bool&gt;&gt; predicate, string optionalTableName = null) where T : class
      {
          CreateReadCommand&lt;T&gt;(predicate, optionalTableName, false);
          command.ExecuteNonQuery();
      }
  
      /// &lt;summary&gt;
      /// Inserts a record into a database table.
      /// &lt;/summary&gt;
      /// &lt;typeparam name="T"&gt;&lt;/typeparam&gt;
      /// &lt;param name="objectToSave"&gt;&lt;/param&gt;
      /// &lt;param name="optionalTableName"&gt;&lt;/param&gt;
      /// &lt;returns&gt;&lt;/returns&gt;
      public int Save&lt;T&gt;(T objectToSave, string optionalTableName = null, bool update = false, Expression&lt;Func&lt;T, bool&gt;&gt; predicate = null) where T : class
      {
          if (!update)
              return InsertTableRow&lt;T&gt;(objectToSave, optionalTableName);
          else if (predicate != null)
              return UpdateTableRow&lt;T&gt;(objectToSave, predicate, optionalTableName);
          else
              return -1;
      }
  
  
      /// &lt;summary&gt;
      /// executes the specified by passing using the dictionary of parameters supplied.
      /// &lt;/summary&gt;
      /// &lt;typeparam name="T"&gt;&lt;/typeparam&gt;
      /// &lt;param name="storedProcName"&gt;&lt;/param&gt;
      /// &lt;param name="optionalValues"&gt;&lt;/param&gt;
      /// &lt;returns&gt;&lt;/returns&gt;
      public IEnumerable&lt;T&gt; ExecuteStoredProcdure&lt;T&gt;(string storedProcName, Dictionary&lt;string, object&gt; optionalValues) where T : class
      {          
          InitializeQueryEngine();
          command.CommandText = storedProcName;
          command.CommandType = CommandType.StoredProcedure;
          optionalValues = optionalValues ?? new Dictionary&lt;string, object&gt;();
          var optionalValuesEnumerator = optionalValues.GetEnumerator();
          while (optionalValuesEnumerator.MoveNext())
          {
              command.Parameters.AddWithValue(optionalValuesEnumerator.Current.Key, optionalValuesEnumerator.Current.Value);
          }
          return GetDataRows&lt;T&gt;();
  
      }
  
      private void InitializeQueryEngine()
      {
          connection = new MySqlConnection(_connectionString);
          command = connection.CreateCommand(); 
          if(connection.State != ConnectionState.Open)       
              connection.Open();
          _indexer = 0;
      }
  
      /// &lt;summary&gt;
      ///  Parses the expression tree.
      /// &lt;/summary&gt;
      /// &lt;typeparam name="T"&gt;&lt;/typeparam&gt;
      /// &lt;param name="predicate"&gt;&lt;/param&gt;
      /// &lt;param name="optionalTableName"&gt;&lt;/param&gt;
      /// &lt;param name="isSelect"&gt;&lt;/param&gt;
      protected internal void CreateReadCommand&lt;T&gt;(Expression&lt;Func&lt;T, bool&gt;&gt; predicate, string optionalTableName = null, bool isSelect = true)
      {          
  
          InitializeQueryEngine();
          string parametizedCommandString = String.Empty;
          string whereClause = predicate != null ? AnalyzePredicate(predicate) : String.Empty;          
  
          parametizedCommandString = ((isSelect ? _selectAll : _remove) + (String.IsNullOrEmpty(optionalTableName) ? typeof(T).Name : optionalTableName) + (whereClause ?? string.Empty));
  
          command.CommandText = parametizedCommandString;            
      }
      private static Expression&lt;Func&lt;T, bool&gt;&gt; ToExpression&lt;T&gt;(BinaryExpression binaryExpression)
      {
          return Expression.Lambda&lt;Func&lt;T, bool&gt;&gt;(binaryExpression, new ParameterExpression[]{ Expression.Parameter(typeof(T)) });
      }
      private string AnalyzePredicate&lt;T&gt;(Expression&lt;Func&lt;T, bool&gt;&gt; predicate, bool isFirstParse = true)
      {
          string whereClause = String.Empty;
          BinaryExpression expression = (BinaryExpression)predicate.Body;
          if (!(expression.NodeType == ExpressionType.Equal || expression.NodeType == ExpressionType.AndAlso))
              throw new NotSupportedException("Only == and &amp;&amp; operator is supported");           
  
          if (expression.Left is BinaryExpression)
          {
              var leftExpression = (BinaryExpression)expression.Left;              
              whereClause += AnalyzePredicate&lt;T&gt;(ToExpression&lt;T&gt;((BinaryExpression)expression.Left), _indexer == 0);             
          }            
  
          if (expression.Right is BinaryExpression)
          {
              var rightExpression = (BinaryExpression)expression.Right;
              whereClause += AnalyzePredicate&lt;T&gt;(ToExpression&lt;T&gt;((BinaryExpression)expression.Right), _indexer == 0);
          }
  
          if (expression.Left is MemberExpression)
          {
              whereClause += ConvertExpressionToSQL&lt;T&gt;(expression, isFirstParse);
              _indexer++;
          }
  
          return whereClause;
      }
  
      private string ConvertExpressionToSQL&lt;T&gt;(BinaryExpression expression, bool isFirstParse)
      {
          string whereClause = String.Empty;
          if (expression.NodeType == ExpressionType.Equal)
          {
              var parsedExpression = GetLeftRightExpresion(expression);
              whereClause += (isFirstParse ? _where : _and) + parsedExpression.Item1.Member.Name + "= " + "@" + parsedExpression.Item1.Member.Name;
              if (command.Parameters != null &amp;&amp; !command.Parameters.Contains("@" + parsedExpression.Item1.Member.Name))
                  command.Parameters.AddWithValue("@" + parsedExpression.Item1.Member.Name, parsedExpression.Item2);
          }
  
          return whereClause;
      }
  
      private Tuple&lt;MemberExpression, object&gt; GetLeftRightExpresion(BinaryExpression expression)
      {
          object value = null;
          if (expression.Right is ConstantExpression)
              value = ((ConstantExpression)expression.Right).Value;
          else
              value = Expression.Lambda(expression.Right).Compile().DynamicInvoke();
          return new Tuple&lt;MemberExpression, object&gt;((MemberExpression)expression.Left, value);
  
  
      }
  
      protected internal IEnumerable&lt;T&gt; GetDataRows&lt;T&gt;(int maxRows = -1) where T : class
      {         
          Reader = command.ExecuteReader();
          int number_of_returned_rows = 0;
          List&lt;T&gt; resrtul = new List&lt;T&gt;();
          if (Reader.HasRows)
          {
              var returnValueProperties = typeof(T).GetProperties();
              var returnValuePropertiesEnumerator = returnValueProperties.GetEnumerator();
              while (Reader.Read())
              {
                  if (maxRows != -1 &amp;&amp; number_of_returned_rows &gt;= maxRows)
                      break;
                  T returnValue = (T)Activator.CreateInstance(typeof(T));
                  while (returnValuePropertiesEnumerator.MoveNext())
                  {
                      var currentProperty = (PropertyInfo)returnValuePropertiesEnumerator.Current;
                      if (Attribute.IsDefined(currentProperty, typeof(GCHDBIgnoreAttribute)))
                          continue;
                      if (Reader[currentProperty.Name].GetType().FullName == "MySql.Data.Types.MySqlDateTime")
                          currentProperty.SetValue(returnValue, DateTime.Parse(Reader[currentProperty.Name].ToString()), null);
                      else if (Reader[currentProperty.Name].GetType().FullName == "System.DBNull")
                          continue;
                      else
                          currentProperty.SetValue(returnValue, Reader[currentProperty.Name], null);
                  }
                  number_of_returned_rows++;                    
                  yield return returnValue;
                  returnValuePropertiesEnumerator.Reset();
              } 
           }
          else
              yield return default(T);
      }
  
      protected internal int InsertTableRow&lt;T&gt;(T row, string optionalTableName = null)
      {
  
          var returnValueProperties = typeof(T).GetProperties();
          var returnValuePropertiesEnumerator = returnValueProperties.GetEnumerator();
          StringBuilder parametizedCommandString = new StringBuilder(_insert + (String.IsNullOrEmpty(optionalTableName) ? row.GetType().Name : optionalTableName) + "( ");
          var placeHolders = new List&lt;string&gt;();
  
          InitializeQueryEngine();
  
          while (returnValuePropertiesEnumerator.MoveNext())
          {
              if (Attribute.IsDefined((PropertyInfo)returnValuePropertiesEnumerator.Current, typeof(GCHDBIgnoreAttribute)))
                  continue;
              var currentPropertyName = ((PropertyInfo)returnValuePropertiesEnumerator.Current).Name;
              parametizedCommandString.Append(currentPropertyName);
              parametizedCommandString.Append(", ");
              placeHolders.Add("@" + currentPropertyName);
          }
          parametizedCommandString.Remove(parametizedCommandString.Length - 2, 1);           
          parametizedCommandString.Append(" )" + _value + "( ");
  
          for (int i = 0; i &lt; placeHolders.Count; i++)
          {
              parametizedCommandString.Append(placeHolders[i]);
              parametizedCommandString.Append(", ");
          }
          parametizedCommandString.Remove(parametizedCommandString.Length - 2, 1);
          parametizedCommandString.Append(" )");
          returnValuePropertiesEnumerator.Reset();
          for (int i = 0; i &lt; placeHolders.Count; i++)
          {                
              returnValuePropertiesEnumerator.MoveNext();
              if (Attribute.IsDefined((PropertyInfo)returnValuePropertiesEnumerator.Current, typeof(GCHDBIgnoreAttribute)))
                  continue;
              command.Parameters.AddWithValue(placeHolders[i], ((PropertyInfo)returnValuePropertiesEnumerator.Current).GetValue(row, null));
          }
          command.CommandText = parametizedCommandString.ToString();
          command.ExecuteNonQuery();
          command.Parameters.Clear();
          command.CommandText = _scopeIdentity;
          return Convert.ToInt32(command.ExecuteScalar());
  
      }
  
      protected internal int UpdateTableRow&lt;T&gt;(T row, Expression&lt;Func&lt;T, bool&gt;&gt; predicate, string optionalTableName)
      {
          var returnValueProperties = typeof(T).GetProperties();
          var returnValuePropertiesEnumerator = returnValueProperties.GetEnumerator();
          StringBuilder parametizedCommandString = new StringBuilder(_update + (String.IsNullOrEmpty(optionalTableName) ? row.GetType().Name : optionalTableName) + _set);
          var placeHolders = new List&lt;string&gt;();
  
          InitializeQueryEngine();
  
          while (returnValuePropertiesEnumerator.MoveNext())
          {
              if (Attribute.IsDefined((PropertyInfo)returnValuePropertiesEnumerator.Current, typeof(GCHDBIgnoreAttribute)))
                  continue;
              var currentPropertyName = ((PropertyInfo)returnValuePropertiesEnumerator.Current).Name;
              parametizedCommandString.Append(currentPropertyName + "= @" + currentPropertyName);
              parametizedCommandString.Append(", ");
              placeHolders.Add("@" + currentPropertyName);
          }
          parametizedCommandString.Remove(parametizedCommandString.Length - 2, 1);            
          parametizedCommandString.Append(AnalyzePredicate&lt;T&gt;(predicate));
          returnValuePropertiesEnumerator.Reset();
          for (int i = 0; i &lt; placeHolders.Count; i++)
          {
              returnValuePropertiesEnumerator.MoveNext();
              if (Attribute.IsDefined((PropertyInfo)returnValuePropertiesEnumerator.Current, typeof(GCHDBIgnoreAttribute)))
                  continue;
              if(command.Parameters != null &amp;&amp; !command.Parameters.Contains(placeHolders[i]))
               command.Parameters.AddWithValue(placeHolders[i], ((PropertyInfo)returnValuePropertiesEnumerator.Current).GetValue(row, null));
  
          }
          command.CommandText = parametizedCommandString.ToString();
          command.ExecuteNonQuery();
          command.Parameters.Clear();
  
          return 0;
      }
  
      protected internal string GetDefaultConnectionString()
      {
          return ConfigurationManager.ConnectionStrings["TSIF_DefaultConnectionString"].ConnectionString;             
      }
  
      public void Dispose()
      {
          if (connection != null &amp;&amp; connection.State == ConnectionState.Closed)
          {
              connection.Dispose();               
          }
      }
  
      ~StronglyTypedDataSource()
      {
          Dispose();
      }
  }   
  </code></pre>