Skip to content

Commit 7b090eb

Browse files
ThatRendleThatRendle
committed
All Where operations implemented
1 parent c3f4c1b commit 7b090eb

File tree

2 files changed

+267
-12
lines changed

2 files changed

+267
-12
lines changed

‎Simple.Data.UnitTest/DictionaryQueryRunnerTest.cs‎

Lines changed: 180 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -117,13 +117,192 @@ public void SelectLengthShouldUseLengthFunction()
117117
}
118118

119119
[Test]
120-
public void BasicWhereShouldWork()
120+
public void BasicWhereEqualShouldWork()
121121
{
122122
var tableRef = new ObjectReference("FooTable");
123123
var whereClause = new WhereClause(new ObjectReference("Name", tableRef) == "Alice");
124124
var runner = new DictionaryQueryRunner(SelectSource(), whereClause);
125125
var actual = runner.Run().ToList();
126126
Assert.AreEqual(1, actual.Count);
127+
Assert.AreEqual("Alice", actual[0]["Name"]);
128+
}
129+
130+
[Test]
131+
public void WhereNullShouldWorkWhenValueExistsAndIsNull()
132+
{
133+
var tableRef = new ObjectReference("FooTable");
134+
var whereClause = new WhereClause(new ObjectReference("Value", tableRef) == null);
135+
var data = new List<IDictionary<string, object>>
136+
{
137+
new Dictionary<string, object>
138+
{
139+
{"Name", "Steve"}, { "Value", null }
140+
},
141+
new Dictionary<string, object>
142+
{
143+
{"Name", "Dave"}, { "Value", 42 }
144+
},
145+
};
146+
var runner = new DictionaryQueryRunner(data, whereClause);
147+
var actual = runner.Run().ToList();
148+
Assert.AreEqual(1, actual.Count);
149+
Assert.AreEqual("Steve", actual[0]["Name"]);
150+
}
151+
152+
[Test]
153+
public void WhereNullShouldWorkWhenValueDoesNotExist()
154+
{
155+
var tableRef = new ObjectReference("FooTable");
156+
var whereClause = new WhereClause(new ObjectReference("Value", tableRef) == null);
157+
var data = new List<IDictionary<string, object>>
158+
{
159+
new Dictionary<string, object>
160+
{
161+
{"Name", "Steve"}
162+
},
163+
new Dictionary<string, object>
164+
{
165+
{"Name", "Dave"}, { "Value", 42 }
166+
},
167+
};
168+
var runner = new DictionaryQueryRunner(data, whereClause);
169+
var actual = runner.Run().ToList();
170+
Assert.AreEqual(1, actual.Count);
171+
Assert.AreEqual("Steve", actual[0]["Name"]);
172+
}
173+
174+
[Test]
175+
public void WhereEqualWithByteArrayShouldWork()
176+
{
177+
var tableRef = new ObjectReference("FooTable");
178+
var whereClause = new WhereClause(new ObjectReference("Array", tableRef) == new byte[] { 1, 2, 3, 4});
179+
var data = new List<IDictionary<string, object>>
180+
{
181+
new Dictionary<string, object>
182+
{
183+
{"Name", "Steve"}, { "Array", new byte[] { 1, 2, 3, 4}}
184+
},
185+
new Dictionary<string, object>
186+
{
187+
{"Name", "Dave"}, { "Array", new byte[] { 2, 3, 4}}
188+
},
189+
};
190+
var runner = new DictionaryQueryRunner(data, whereClause);
191+
var actual = runner.Run().ToList();
192+
Assert.AreEqual(1, actual.Count);
193+
Assert.AreEqual("Steve", actual[0]["Name"]);
194+
}
195+
196+
[Test]
197+
public void BasicWhereNotEqualShouldWork()
198+
{
199+
var tableRef = new ObjectReference("FooTable");
200+
var whereClause = new WhereClause(new ObjectReference("Name", tableRef) != "Alice");
201+
var runner = new DictionaryQueryRunner(SelectSource(), whereClause);
202+
var actual = runner.Run().ToList();
203+
Assert.AreEqual(3, actual.Count);
204+
Assert.False(actual.Any(a => (string)a["Name"] == "Alice"));
205+
}
206+
207+
[Test]
208+
public void WhereNotNullShouldWork()
209+
{
210+
var tableRef = new ObjectReference("FooTable");
211+
var whereClause = new WhereClause(new ObjectReference("Value", tableRef) != null);
212+
var data = new List<IDictionary<string, object>>
213+
{
214+
new Dictionary<string, object>
215+
{
216+
{"Name", "Steve"}, { "Value", null }
217+
},
218+
new Dictionary<string, object>
219+
{
220+
{"Name", "Dave"}, { "Value", 42 }
221+
},
222+
};
223+
var runner = new DictionaryQueryRunner(data, whereClause);
224+
var actual = runner.Run().ToList();
225+
Assert.AreEqual(1, actual.Count);
226+
Assert.AreEqual("Dave", actual[0]["Name"]);
227+
}
228+
229+
[Test]
230+
public void WhereNotEqualWithByteArrayShouldWork()
231+
{
232+
var tableRef = new ObjectReference("FooTable");
233+
var whereClause = new WhereClause(new ObjectReference("Array", tableRef) != new byte[] { 1, 2, 3, 4 });
234+
var data = new List<IDictionary<string, object>>
235+
{
236+
new Dictionary<string, object>
237+
{
238+
{"Name", "Steve"}, { "Array", new byte[] { 1, 2, 3, 4}}
239+
},
240+
new Dictionary<string, object>
241+
{
242+
{"Name", "Dave"}, { "Array", new byte[] { 2, 3, 4}}
243+
},
244+
};
245+
var runner = new DictionaryQueryRunner(data, whereClause);
246+
var actual = runner.Run().ToList();
247+
Assert.AreEqual(1, actual.Count);
248+
Assert.AreEqual("Dave", actual[0]["Name"]);
249+
}
250+
251+
[Test]
252+
public void BasicWhereGreaterThanShouldWork()
253+
{
254+
var tableRef = new ObjectReference("FooTable");
255+
var whereClause = new WhereClause(new ObjectReference("Weight", tableRef) > 200M);
256+
var runner = new DictionaryQueryRunner(SelectSource(), whereClause);
257+
var actual = runner.Run().ToList();
258+
Assert.AreEqual(1, actual.Count);
259+
Assert.AreEqual("David", actual[0]["Name"]);
260+
}
261+
262+
[Test]
263+
public void BasicWhereLessThanShouldWork()
264+
{
265+
var tableRef = new ObjectReference("FooTable");
266+
var whereClause = new WhereClause(new ObjectReference("Weight", tableRef) < 150M);
267+
var runner = new DictionaryQueryRunner(SelectSource(), whereClause);
268+
var actual = runner.Run().ToList();
269+
Assert.AreEqual(1, actual.Count);
270+
Assert.AreEqual("Alice", actual[0]["Name"]);
271+
}
272+
273+
[Test]
274+
public void BasicWhereGreaterThanOrEqualShouldWork()
275+
{
276+
var tableRef = new ObjectReference("FooTable");
277+
var whereClause = new WhereClause(new ObjectReference("Weight", tableRef) >= 250M);
278+
var runner = new DictionaryQueryRunner(SelectSource(), whereClause);
279+
var actual = runner.Run().ToList();
280+
Assert.AreEqual(1, actual.Count);
281+
Assert.AreEqual("David", actual[0]["Name"]);
282+
}
283+
284+
[Test]
285+
public void BasicWhereLessThanOrEqualShouldWork()
286+
{
287+
var tableRef = new ObjectReference("FooTable");
288+
var whereClause = new WhereClause(new ObjectReference("Weight", tableRef) <= 100M);
289+
var runner = new DictionaryQueryRunner(SelectSource(), whereClause);
290+
var actual = runner.Run().ToList();
291+
Assert.AreEqual(1, actual.Count);
292+
Assert.AreEqual("Alice", actual[0]["Name"]);
293+
}
294+
295+
[Test]
296+
public void BasicLikeShouldWork()
297+
{
298+
var tableRef = new ObjectReference("FooTable");
299+
dynamic objRef = new ObjectReference("Name", tableRef);
300+
var expression = new SimpleExpression(objRef, new SimpleFunction("like", new[] {"A%"}), SimpleExpressionType.Function);
301+
var whereClause = new WhereClause(expression);
302+
var runner = new DictionaryQueryRunner(SelectSource(), whereClause);
303+
var actual = runner.Run().ToList();
304+
Assert.AreEqual(1, actual.Count);
305+
Assert.AreEqual("Alice", actual[0]["Name"]);
127306
}
128307

129308
#region Distinct sources

‎Simple.Data/QueryPolyfills/WhereClauseHandler.cs‎

Lines changed: 87 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -4,6 +4,7 @@ namespace Simple.Data.QueryPolyfills
44
using System.Collections;
55
using System.Collections.Generic;
66
using System.Linq;
7+
using System.Text.RegularExpressions;
78

89
internal class WhereClauseHandler
910
{
@@ -19,27 +20,92 @@ public WhereClauseHandler(WhereClause whereClause)
1920
{SimpleExpressionType.And, LogicalExpressionToWhereClause},
2021
{SimpleExpressionType.Or, LogicalExpressionToWhereClause},
2122
{SimpleExpressionType.Equal, EqualExpressionToWhereClause},
22-
//{SimpleExpressionType.NotEqual, NotEqualExpressionToWhereClause},
23-
//{SimpleExpressionType.Function, FunctionExpressionToWhereClause},
24-
//{SimpleExpressionType.GreaterThan, expr => BinaryExpressionToWhereClause(expr, ">")},
25-
//{SimpleExpressionType.GreaterThanOrEqual, expr => BinaryExpressionToWhereClause(expr, ">=")},
26-
//{SimpleExpressionType.LessThan, expr => BinaryExpressionToWhereClause(expr, "<")},
27-
//{SimpleExpressionType.LessThanOrEqual, expr => BinaryExpressionToWhereClause(expr, "<=")},
23+
{SimpleExpressionType.NotEqual, NotEqualExpressionToWhereClause},
24+
{SimpleExpressionType.Function, FunctionExpressionToWhereClause},
25+
{SimpleExpressionType.GreaterThan, GreaterThanToWhereClause},
26+
{SimpleExpressionType.LessThan, LessThanToWhereClause},
27+
{SimpleExpressionType.GreaterThanOrEqual, GreaterThanOrEqualToWhereClause},
28+
{SimpleExpressionType.LessThanOrEqual, LessThanOrEqualToWhereClause},
2829
{SimpleExpressionType.Empty, expr => _ => true },
2930
};
3031
}
3132

32-
private Func<IDictionary<string, object>, bool> EqualExpressionToWhereClause(SimpleExpression arg)
33+
private Func<IDictionary<string, object>, bool> FunctionExpressionToWhereClause(SimpleExpression arg)
3334
{
34-
var reference = arg.LeftOperand as ObjectReference;
35+
var key = GetKeyFromLeftOperand(arg);
36+
var function = arg.RightOperand as SimpleFunction;
37+
if (ReferenceEquals(function, null)) throw new InvalidOperationException("Expression type of function but no function supplied.");
38+
if (function.Name.Equals("like", StringComparison.OrdinalIgnoreCase))
39+
{
40+
var pattern = function.Args[0].ToString();
41+
if (pattern.Contains("%") || pattern.Contains("_")) // SQL Server LIKE
42+
{
43+
pattern = pattern.Replace("%", ".*").Replace('_', '.');
44+
}
3545

36-
if (reference.IsNull()) throw new NotSupportedException("Only ObjectReference types are supported.");
46+
var regex = new Regex("^" + pattern + "$", RegexOptions.Multiline | RegexOptions.IgnoreCase);
3747

38-
var key = reference.GetName();
48+
return d => d.ContainsKey(key) && (!ReferenceEquals(d[key], null)) && regex.IsMatch(d[key].ToString());
49+
}
50+
51+
throw new NotSupportedException("Expression Function not supported.");
52+
}
53+
54+
private Func<IDictionary<string, object>, bool> GreaterThanToWhereClause(SimpleExpression arg)
55+
{
56+
var key = GetKeyFromLeftOperand(arg);
57+
58+
return d => d.ContainsKey(key) && !ReferenceEquals(d[key], null) && ((IComparable)d[key]).CompareTo(arg.RightOperand) > 0;
59+
}
60+
61+
private Func<IDictionary<string, object>, bool> LessThanToWhereClause(SimpleExpression arg)
62+
{
63+
var key = GetKeyFromLeftOperand(arg);
64+
65+
return d => d.ContainsKey(key) && !ReferenceEquals(d[key], null) && ((IComparable)d[key]).CompareTo(arg.RightOperand) < 0;
66+
}
67+
68+
private Func<IDictionary<string, object>, bool> GreaterThanOrEqualToWhereClause(SimpleExpression arg)
69+
{
70+
var key = GetKeyFromLeftOperand(arg);
71+
72+
return d => d.ContainsKey(key) && !ReferenceEquals(d[key], null) && ((IComparable)d[key]).CompareTo(arg.RightOperand) >= 0;
73+
}
74+
75+
private Func<IDictionary<string, object>, bool> LessThanOrEqualToWhereClause(SimpleExpression arg)
76+
{
77+
var key = GetKeyFromLeftOperand(arg);
78+
79+
return d => d.ContainsKey(key) && !ReferenceEquals(d[key], null) && ((IComparable)d[key]).CompareTo(arg.RightOperand) <= 0;
80+
}
81+
82+
private Func<IDictionary<string, object>, bool> NotEqualExpressionToWhereClause(SimpleExpression arg)
83+
{
84+
var key = GetKeyFromLeftOperand(arg);
3985

4086
if (ReferenceEquals(arg.RightOperand, null))
4187
{
42-
return d => d.ContainsKey(key) && d[key] == null;
88+
return d => d.ContainsKey(key) && d[key] != null;
89+
}
90+
91+
if (arg.RightOperand.GetType().IsArray)
92+
{
93+
return
94+
d =>
95+
d.ContainsKey(key) &&
96+
!((IEnumerable)d[key]).Cast<object>().SequenceEqual(((IEnumerable)arg.RightOperand).Cast<object>());
97+
}
98+
99+
return d => d.ContainsKey(key) && !Equals(d[key], arg.RightOperand);
100+
}
101+
102+
private Func<IDictionary<string, object>, bool> EqualExpressionToWhereClause(SimpleExpression arg)
103+
{
104+
var key = GetKeyFromLeftOperand(arg);
105+
106+
if (ReferenceEquals(arg.RightOperand, null))
107+
{
108+
return d => (!d.ContainsKey(key)) || d[key] == null;
43109
}
44110

45111
if (arg.RightOperand.GetType().IsArray)
@@ -53,6 +119,16 @@ private Func<IDictionary<string, object>, bool> EqualExpressionToWhereClause(Sim
53119
return d => d.ContainsKey(key) && Equals(d[key], arg.RightOperand);
54120
}
55121

122+
private static string GetKeyFromLeftOperand(SimpleExpression arg)
123+
{
124+
var reference = arg.LeftOperand as ObjectReference;
125+
126+
if (reference.IsNull()) throw new NotSupportedException("Only ObjectReference types are supported.");
127+
128+
var key = reference.GetName();
129+
return key;
130+
}
131+
56132
private Func<IDictionary<string,object>, bool> Format(SimpleExpression expression)
57133
{
58134
Func<SimpleExpression, Func<IDictionary<string,object>,bool>> formatter;

0 commit comments

Comments
 (0)