PageRenderTime 61ms CodeModel.GetById 29ms RepoModel.GetById 0ms app.codeStats 0ms

/src/LinqToExcel/Query/WhereClauseExpressionTreeVisitor.cs

https://github.com/jwcarroll/LinqToExcel
C# | 269 lines | 221 code | 25 blank | 23 comment | 22 complexity | 49992dabcdefd4ff4fbeda17930b4318 MD5 | raw file
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Linq;
  4. using System.Text;
  5. using Remotion.Data.Linq.Parsing;
  6. using System.Data.OleDb;
  7. using System.Linq.Expressions;
  8. using LinqToExcel.Extensions;
  9. using Remotion.Data.Linq.Clauses.Expressions;
  10. namespace LinqToExcel.Query
  11. {
  12. public class WhereClauseExpressionTreeVisitor : ThrowingExpressionTreeVisitor
  13. {
  14. private readonly StringBuilder _whereClause = new StringBuilder();
  15. private readonly List<OleDbParameter> _params = new List<OleDbParameter>();
  16. private readonly Dictionary<string, string> _columnMapping;
  17. private readonly List<string> _columnNamesUsed = new List<string>();
  18. private readonly Type _sheetType;
  19. private readonly List<string> _validStringMethods;
  20. public WhereClauseExpressionTreeVisitor(Type sheetType, Dictionary<string, string> columnMapping)
  21. {
  22. _sheetType = sheetType;
  23. _columnMapping = columnMapping;
  24. _validStringMethods = new List<string>() {
  25. "Equals",
  26. "Contains",
  27. "StartsWith",
  28. "EndsWith" };
  29. }
  30. public string WhereClause
  31. {
  32. get { return _whereClause.ToString(); }
  33. }
  34. public IEnumerable<OleDbParameter> Params
  35. {
  36. get { return _params; }
  37. }
  38. public IEnumerable<string> ColumnNamesUsed
  39. {
  40. get { return _columnNamesUsed.Select(x => x.Replace("[", "").Replace("]", "")); }
  41. }
  42. public void Visit(Expression expression)
  43. {
  44. base.VisitExpression(expression);
  45. }
  46. protected override Exception CreateUnhandledItemException<T>(T unhandledItem, string visitMethod)
  47. {
  48. throw new NotImplementedException(visitMethod + " method is not implemented");
  49. }
  50. protected override Expression VisitBinaryExpression(BinaryExpression bExp)
  51. {
  52. _whereClause.Append("(");
  53. // Patch for vb.net expression that are always considered a MethodCallExpression even if they are not.
  54. // see http://www.re-motion.org/blogs/mix/archive/2009/10/16/vb.net-specific-text-comparison-in-linq-queries.aspx
  55. bExp = ConvertVbStringCompare(bExp);
  56. //We always want the MemberAccess (ColumnName) to be on the left side of the statement
  57. var bLeft = bExp.Left;
  58. var bRight = bExp.Right;
  59. if ((bExp.Right.NodeType == ExpressionType.MemberAccess) &&
  60. (((MemberExpression)bExp.Right).Member.DeclaringType == _sheetType))
  61. {
  62. bLeft = bExp.Right;
  63. bRight = bExp.Left;
  64. }
  65. VisitExpression(bLeft);
  66. switch (bExp.NodeType)
  67. {
  68. case ExpressionType.AndAlso:
  69. _whereClause.Append(" AND ");
  70. break;
  71. case ExpressionType.Equal:
  72. if (bRight.IsNullValue())
  73. _whereClause.Append(" IS NULL");
  74. else
  75. _whereClause.Append(" = ");
  76. break;
  77. case ExpressionType.GreaterThan:
  78. _whereClause.Append(" > ");
  79. break;
  80. case ExpressionType.GreaterThanOrEqual:
  81. _whereClause.Append(" >= ");
  82. break;
  83. case ExpressionType.LessThan:
  84. _whereClause.Append(" < ");
  85. break;
  86. case ExpressionType.LessThanOrEqual:
  87. _whereClause.Append(" <= ");
  88. break;
  89. case ExpressionType.NotEqual:
  90. if (bRight.IsNullValue())
  91. _whereClause.Append(" IS NOT NULL");
  92. else
  93. _whereClause.Append(" <> ");
  94. break;
  95. case ExpressionType.OrElse:
  96. _whereClause.Append(" OR ");
  97. break;
  98. default:
  99. throw new NotSupportedException(string.Format("{0} statement is not supported", bExp.NodeType.ToString()));
  100. }
  101. if (!bRight.IsNullValue())
  102. VisitExpression(bRight);
  103. _whereClause.Append(")");
  104. return bExp;
  105. }
  106. protected BinaryExpression ConvertVbStringCompare(BinaryExpression exp)
  107. {
  108. if (exp.Left.NodeType == ExpressionType.Call)
  109. {
  110. var compareStringCall = (MethodCallExpression)exp.Left;
  111. if (compareStringCall.Method.DeclaringType.FullName == "Microsoft.VisualBasic.CompilerServices.Operators" && compareStringCall.Method.Name == "CompareString")
  112. {
  113. var arg1 = compareStringCall.Arguments[0];
  114. var arg2 = compareStringCall.Arguments[1];
  115. switch (exp.NodeType)
  116. {
  117. case ExpressionType.LessThan:
  118. return Expression.LessThan(arg1, arg2);
  119. case ExpressionType.LessThanOrEqual:
  120. return Expression.LessThanOrEqual(arg1, arg2);
  121. case ExpressionType.GreaterThan:
  122. return Expression.GreaterThan(arg1, arg2);
  123. case ExpressionType.GreaterThanOrEqual:
  124. return Expression.GreaterThanOrEqual(arg1, arg2);
  125. case ExpressionType.NotEqual:
  126. return Expression.NotEqual(arg1, arg2);
  127. default:
  128. return Expression.Equal(arg1, arg2);
  129. }
  130. }
  131. }
  132. return exp;
  133. }
  134. protected override Expression VisitMemberExpression(MemberExpression mExp)
  135. {
  136. //Set the column name to the property mapping if there is one,
  137. //else use the property name for the column name
  138. var columnName = (_columnMapping.ContainsKey(mExp.Member.Name)) ?
  139. _columnMapping[mExp.Member.Name] :
  140. mExp.Member.Name;
  141. _whereClause.AppendFormat("[{0}]", columnName);
  142. _columnNamesUsed.Add(columnName);
  143. return mExp;
  144. }
  145. protected override Expression VisitConstantExpression(ConstantExpression cExp)
  146. {
  147. _params.Add(new OleDbParameter("?", cExp.Value));
  148. _whereClause.Append("?");
  149. return cExp;
  150. }
  151. /// <summary>
  152. /// This method is visited when the LinqToExcel.Row type is used in the query
  153. /// </summary>
  154. protected override Expression VisitUnaryExpression(UnaryExpression uExp)
  155. {
  156. var columnName = GetColumnName(uExp.Operand);
  157. _whereClause.Append(columnName);
  158. return uExp;
  159. }
  160. /// <summary>
  161. /// Only As<>() method calls on the LinqToExcel.Row type are support
  162. /// </summary>
  163. protected override Expression VisitMethodCallExpression(MethodCallExpression mExp)
  164. {
  165. if (_validStringMethods.Contains(mExp.Method.Name))
  166. ProcessStringMethod(mExp);
  167. else
  168. {
  169. var columnName = GetColumnName(mExp);
  170. _whereClause.Append(columnName);
  171. _columnNamesUsed.Add(columnName);
  172. }
  173. return mExp;
  174. }
  175. private void ProcessStringMethod(MethodCallExpression mExp)
  176. {
  177. switch (mExp.Method.Name)
  178. {
  179. case "Contains":
  180. AddStringMethodToWhereClause(mExp, "LIKE", "%{0}%");
  181. break;
  182. case "StartsWith":
  183. AddStringMethodToWhereClause(mExp, "LIKE", "{0}%");
  184. break;
  185. case "EndsWith":
  186. AddStringMethodToWhereClause(mExp, "LIKE", "%{0}");
  187. break;
  188. case "Equals":
  189. AddStringMethodToWhereClause(mExp, "=", "{0}");
  190. break;
  191. }
  192. }
  193. private void AddStringMethodToWhereClause(MethodCallExpression mExp, string operatorString, string argumentFormat)
  194. {
  195. _whereClause.Append("(");
  196. VisitExpression(mExp.Object);
  197. _whereClause.AppendFormat(" {0} ?)", operatorString);
  198. var value = mExp.Arguments.First().ToString().Replace("\"", "");
  199. var parameter = string.Format(argumentFormat, value);
  200. _params.Add(new OleDbParameter("?", parameter));
  201. }
  202. /// <summary>
  203. /// Retrieves the column name from a member expression or method call expression
  204. /// </summary>
  205. /// <param name="exp">Expression</param>
  206. private string GetColumnName(Expression exp)
  207. {
  208. if (exp is MemberExpression)
  209. return GetColumnName((MemberExpression)exp);
  210. else
  211. return GetColumnName((MethodCallExpression)exp);
  212. }
  213. /// <summary>
  214. /// Retrieves the column name from a member expression
  215. /// </summary>
  216. /// <param name="mExp">Member Expression</param>
  217. private string GetColumnName(MemberExpression mExp)
  218. {
  219. return "[" + mExp.Member.Name + "]";
  220. }
  221. /// <summary>
  222. /// Retrieves the column name from a method call expression
  223. /// </summary>
  224. /// <param name="exp">Method Call Expression</param>
  225. private string GetColumnName(MethodCallExpression mExp)
  226. {
  227. MethodCallExpression method = mExp;
  228. if (mExp.Object is MethodCallExpression)
  229. method = (MethodCallExpression)mExp.Object;
  230. var arg = method.Arguments.First();
  231. if (arg.Type == typeof(int))
  232. {
  233. if (_sheetType == typeof(RowNoHeader))
  234. return string.Format("F{0}", Int32.Parse(arg.ToString()) + 1);
  235. else
  236. throw new ArgumentException("Can only use column indexes in WHERE clause when using WorksheetNoHeader");
  237. }
  238. var columnName = arg.ToString().ToCharArray();
  239. columnName[0] = "[".ToCharArray().First();
  240. columnName[columnName.Length - 1] = "]".ToCharArray().First();
  241. return new string(columnName);
  242. }
  243. }
  244. }