|
0
|
1 using System;
|
|
|
2 using System.Linq;
|
|
|
3
|
|
|
4 using NUnit.Framework;
|
|
|
5
|
|
|
6 using BLToolkit.Data.DataProvider;
|
|
|
7
|
|
|
8 // ReSharper disable ReturnValueOfPureMethodIsNotUsed
|
|
|
9
|
|
|
10 namespace Data.Linq
|
|
|
11 {
|
|
|
12 [TestFixture]
|
|
|
13 public class OrderByTest : TestBase
|
|
|
14 {
|
|
|
15 [Test]
|
|
|
16 public void OrderBy1()
|
|
|
17 {
|
|
|
18 var expected =
|
|
|
19 from ch in Child
|
|
|
20 orderby ch.ParentID descending, ch.ChildID ascending
|
|
|
21 select ch;
|
|
|
22
|
|
|
23 ForEachProvider(db =>
|
|
|
24 {
|
|
|
25 var result =
|
|
|
26 from ch in db.Child
|
|
|
27 orderby ch.ParentID descending, ch.ChildID ascending
|
|
|
28 select ch;
|
|
|
29
|
|
|
30 Assert.IsTrue(result.ToList().SequenceEqual(expected));
|
|
|
31 });
|
|
|
32 }
|
|
|
33
|
|
|
34 [Test]
|
|
|
35 public void OrderBy2()
|
|
|
36 {
|
|
|
37 var expected =
|
|
|
38 from ch in Child
|
|
|
39 orderby ch.ParentID descending, ch.ChildID ascending
|
|
|
40 select ch;
|
|
|
41
|
|
|
42 ForEachProvider(db =>
|
|
|
43 {
|
|
|
44 var result =
|
|
|
45 from ch in db.Child
|
|
|
46 orderby ch.ParentID descending, ch.ChildID ascending
|
|
|
47 select ch;
|
|
|
48
|
|
|
49 Assert.IsTrue(result.ToList().SequenceEqual(expected));
|
|
|
50 });
|
|
|
51 }
|
|
|
52
|
|
|
53 [Test]
|
|
|
54 public void OrderBy3()
|
|
|
55 {
|
|
|
56 var expected =
|
|
|
57 from ch in
|
|
|
58 from ch in Child
|
|
|
59 orderby ch.ParentID descending
|
|
|
60 select ch
|
|
|
61 orderby ch.ParentID descending , ch.ChildID
|
|
|
62 select ch;
|
|
|
63
|
|
|
64 ForEachProvider(db =>
|
|
|
65 {
|
|
|
66 var result =
|
|
|
67 from ch in
|
|
|
68 from ch in db.Child
|
|
|
69 orderby ch.ParentID descending
|
|
|
70 select ch
|
|
|
71 orderby ch.ParentID descending , ch.ChildID
|
|
|
72 select ch;
|
|
|
73
|
|
|
74 Assert.IsTrue(result.ToList().SequenceEqual(expected));
|
|
|
75 });
|
|
|
76 }
|
|
|
77
|
|
|
78 [Test]
|
|
|
79 public void OrderBy4()
|
|
|
80 {
|
|
|
81 var expected =
|
|
|
82 from ch in
|
|
|
83 from ch in Child
|
|
|
84 orderby ch.ParentID descending
|
|
|
85 select ch
|
|
|
86 orderby ch.ParentID descending, ch.ChildID, ch.ParentID + 1 descending
|
|
|
87 select ch;
|
|
|
88
|
|
|
89 ForEachProvider(db =>
|
|
|
90 {
|
|
|
91 var result =
|
|
|
92 from ch in
|
|
|
93 from ch in db.Child
|
|
|
94 orderby ch.ParentID descending
|
|
|
95 select ch
|
|
|
96 orderby ch.ParentID descending, ch.ChildID, ch.ParentID + 1 descending
|
|
|
97 select ch;
|
|
|
98
|
|
|
99 Assert.IsTrue(result.ToList().SequenceEqual(expected));
|
|
|
100 });
|
|
|
101 }
|
|
|
102
|
|
|
103 [Test]
|
|
|
104 public void OrderBy5()
|
|
|
105 {
|
|
|
106 var expected =
|
|
|
107 from ch in Child
|
|
|
108 orderby ch.ChildID % 2, ch.ChildID
|
|
|
109 select ch;
|
|
|
110
|
|
|
111 ForEachProvider(db =>
|
|
|
112 {
|
|
|
113 var result =
|
|
|
114 from ch in db.Child
|
|
|
115 orderby ch.ChildID % 2, ch.ChildID
|
|
|
116 select ch;
|
|
|
117
|
|
|
118 Assert.IsTrue(result.ToList().SequenceEqual(expected));
|
|
|
119 });
|
|
|
120 }
|
|
|
121
|
|
|
122 [Test]
|
|
|
123 public void OrderBy6([DataContexts(ExcludeLinqService = true)] string context)
|
|
|
124 {
|
|
|
125 using (var dataContext = GetDataContext(context))
|
|
|
126 {
|
|
|
127 if (!(dataContext is TestDbManager)) return;
|
|
|
128 var db = (TestDbManager)dataContext;
|
|
|
129
|
|
|
130 var q =
|
|
|
131 from person in db.Person
|
|
|
132 join patient in db.Patient on person.ID equals patient.PersonID into g
|
|
|
133 from patient in g.DefaultIfEmpty()
|
|
|
134 orderby person.MiddleName // if comment this line then "Diagnosis" is not selected.
|
|
|
135 select new { person.ID, PatientID = patient != null ? (int?)patient.PersonID : null };
|
|
|
136
|
|
|
137 q.ToList();
|
|
|
138
|
|
|
139 Assert.IsFalse(db.LastQuery.Contains("Diagnosis"), "Why do we select Patient.Diagnosis??");
|
|
|
140
|
|
|
141 };
|
|
|
142 }
|
|
|
143
|
|
|
144 [Test]
|
|
|
145 public void ConditionOrderBy()
|
|
|
146 {
|
|
|
147 var expected =
|
|
|
148 from ch in Child
|
|
|
149 orderby ch.ParentID > 0 && ch.ChildID != ch.ParentID descending, ch.ChildID
|
|
|
150 select ch;
|
|
|
151
|
|
|
152 ForEachProvider(db =>
|
|
|
153 {
|
|
|
154 var result =
|
|
|
155 from ch in db.Child
|
|
|
156 orderby ch.ParentID > 0 && ch.ChildID != ch.ParentID descending, ch.ChildID
|
|
|
157 select ch;
|
|
|
158
|
|
|
159 Assert.IsTrue(result.ToList().SequenceEqual(expected));
|
|
|
160 });
|
|
|
161 }
|
|
|
162
|
|
|
163 [Test]
|
|
|
164 public void OrderBySelf1()
|
|
|
165 {
|
|
|
166 var expected = from p in Parent orderby p select p;
|
|
|
167
|
|
|
168 ForEachProvider(db =>
|
|
|
169 {
|
|
|
170 var result = from p in db.Parent orderby p select p;
|
|
|
171 Assert.IsTrue(result.ToList().SequenceEqual(expected));
|
|
|
172 });
|
|
|
173 }
|
|
|
174
|
|
|
175 [Test]
|
|
|
176 public void OrderBySelf2()
|
|
|
177 {
|
|
|
178 var expected = from p in Parent1 orderby p select p;
|
|
|
179
|
|
|
180 ForEachProvider(db =>
|
|
|
181 {
|
|
|
182 var result = from p in db.Parent1 orderby p select p;
|
|
|
183 Assert.IsTrue(result.ToList().SequenceEqual(expected));
|
|
|
184 });
|
|
|
185 }
|
|
|
186
|
|
|
187 [Test]
|
|
|
188 public void OrderBySelectMany1()
|
|
|
189 {
|
|
|
190 var expected =
|
|
|
191 from p in Parent.OrderBy(p => p.ParentID)
|
|
|
192 from c in Child. OrderBy(c => c.ChildID)
|
|
|
193 where p == c.Parent
|
|
|
194 select new { p.ParentID, c.ChildID };
|
|
|
195
|
|
|
196 ForEachProvider(new[] { ProviderName.Access }, db =>
|
|
|
197 {
|
|
|
198 var result =
|
|
|
199 from p in db.Parent.OrderBy(p => p.ParentID)
|
|
|
200 from c in db.Child. OrderBy(c => c.ChildID)
|
|
|
201 where p == c.Parent
|
|
|
202 select new { p.ParentID, c.ChildID };
|
|
|
203
|
|
|
204 Assert.IsTrue(result.ToList().SequenceEqual(expected));
|
|
|
205 });
|
|
|
206 }
|
|
|
207
|
|
|
208 [Test]
|
|
|
209 public void OrderBySelectMany2()
|
|
|
210 {
|
|
|
211 var expected =
|
|
|
212 from p in Parent1.OrderBy(p => p.ParentID)
|
|
|
213 from c in Child. OrderBy(c => c.ChildID)
|
|
|
214 where p.ParentID == c.Parent1.ParentID
|
|
|
215 select new { p.ParentID, c.ChildID };
|
|
|
216
|
|
|
217 ForEachProvider(db =>
|
|
|
218 {
|
|
|
219 var result =
|
|
|
220 from p in db.Parent1.OrderBy(p => p.ParentID)
|
|
|
221 from c in db.Child. OrderBy(c => c.ChildID)
|
|
|
222 where p == c.Parent1
|
|
|
223 select new { p.ParentID, c.ChildID };
|
|
|
224
|
|
|
225 Assert.IsTrue(result.ToList().SequenceEqual(expected));
|
|
|
226 });
|
|
|
227 }
|
|
|
228
|
|
|
229 [Test]
|
|
|
230 public void OrderBySelectMany3()
|
|
|
231 {
|
|
|
232 var expected =
|
|
|
233 from p in Parent.OrderBy(p => p.ParentID)
|
|
|
234 from c in Child. OrderBy(c => c.ChildID)
|
|
|
235 where c.Parent == p
|
|
|
236 select new { p.ParentID, c.ChildID };
|
|
|
237
|
|
|
238 ForEachProvider(new[] { ProviderName.Access }, db =>
|
|
|
239 {
|
|
|
240 var result =
|
|
|
241 from p in db.Parent.OrderBy(p => p.ParentID)
|
|
|
242 from c in db.Child. OrderBy(c => c.ChildID)
|
|
|
243 where c.Parent == p
|
|
|
244 select new { p.ParentID, c.ChildID };
|
|
|
245
|
|
|
246 Assert.IsTrue(result.ToList().SequenceEqual(expected));
|
|
|
247 });
|
|
|
248 }
|
|
|
249
|
|
|
250 [Test]
|
|
|
251 public void OrderAscDesc()
|
|
|
252 {
|
|
|
253 var expected = Parent.OrderBy(p => p.ParentID).OrderByDescending(p => p.ParentID);
|
|
|
254
|
|
|
255 ForEachProvider(db =>
|
|
|
256 {
|
|
|
257 var result = db.Parent.OrderBy(p => p.ParentID).OrderByDescending(p => p.ParentID);
|
|
|
258 Assert.IsTrue(result.ToList().SequenceEqual(expected));
|
|
|
259 });
|
|
|
260 }
|
|
|
261
|
|
|
262 [Test]
|
|
|
263 public void Count1()
|
|
|
264 {
|
|
|
265 ForEachProvider(db => Assert.AreEqual(
|
|
|
266 Parent.OrderBy(p => p.ParentID).Count(),
|
|
|
267 db.Parent.OrderBy(p => p.ParentID).Count()));
|
|
|
268 }
|
|
|
269
|
|
|
270 [Test]
|
|
|
271 public void Count2()
|
|
|
272 {
|
|
|
273 ForEachProvider(new[] { ProviderName.Sybase }, db => Assert.AreEqual(
|
|
|
274 Parent.OrderBy(p => p.ParentID).Take(3).Count(),
|
|
|
275 db.Parent.OrderBy(p => p.ParentID).Take(3).Count()));
|
|
|
276 }
|
|
|
277
|
|
|
278 [Test]
|
|
|
279 public void Min1()
|
|
|
280 {
|
|
|
281 ForEachProvider(db => Assert.AreEqual(
|
|
|
282 Parent.OrderBy(p => p.ParentID).Min(p => p.ParentID),
|
|
|
283 db.Parent.OrderBy(p => p.ParentID).Min(p => p.ParentID)));
|
|
|
284 }
|
|
|
285
|
|
|
286 [Test]
|
|
|
287 public void Min2()
|
|
|
288 {
|
|
|
289 ForEachProvider(new[] { ProviderName.Sybase }, db => Assert.AreEqual(
|
|
|
290 Parent.OrderBy(p => p.ParentID).Take(3).Min(p => p.ParentID),
|
|
|
291 db.Parent.OrderBy(p => p.ParentID).Take(3).Min(p => p.ParentID)));
|
|
|
292 }
|
|
|
293
|
|
|
294 [Test]
|
|
|
295 public void Min3()
|
|
|
296 {
|
|
|
297 ForEachProvider(new[] { ProviderName.Sybase, ProviderName.Informix }, db => Assert.AreEqual(
|
|
|
298 Parent.OrderBy(p => p.Value1).Take(3).Min(p => p.ParentID),
|
|
|
299 db.Parent.OrderBy(p => p.Value1).Take(3).Min(p => p.ParentID)));
|
|
|
300 }
|
|
|
301
|
|
|
302 [Test]
|
|
|
303 public void Distinct()
|
|
|
304 {
|
|
|
305 ForEachProvider(db => AreEqual(
|
|
|
306 (from p in Parent
|
|
|
307 join c in Child on p.ParentID equals c.ParentID
|
|
|
308 join g in GrandChild on c.ChildID equals g.ChildID
|
|
|
309 select p).Distinct().OrderBy(p => p.ParentID),
|
|
|
310 (from p in db.Parent
|
|
|
311 join c in db.Child on p.ParentID equals c.ParentID
|
|
|
312 join g in db.GrandChild on c.ChildID equals g.ChildID
|
|
|
313 select p).Distinct().OrderBy(p => p.ParentID)));
|
|
|
314 }
|
|
|
315
|
|
|
316 [Test]
|
|
|
317 public void Take()
|
|
|
318 {
|
|
|
319 ForEachProvider(db =>
|
|
|
320 {
|
|
|
321 var q =
|
|
|
322 (from p in db.Parent
|
|
|
323 join c in db.Child on p.ParentID equals c.ParentID
|
|
|
324 join g in db.GrandChild on c.ChildID equals g.ChildID
|
|
|
325 select p).Take(3).OrderBy(p => p.ParentID);
|
|
|
326
|
|
|
327 Assert.AreEqual(3, q.AsEnumerable().Count());
|
|
|
328 });
|
|
|
329 }
|
|
|
330
|
|
|
331 }
|
|
|
332 }
|
