/mcs/class/System.Data.Linq/src/DbLinq/Test/Providers/ReadTest_GroupBy.cs
C# | 322 lines | 239 code | 35 blank | 48 comment | 40 complexity | a08e1342f6b3adadec7c2a9091e47ed9 MD5 | raw file
1#region MIT license
2//
3// MIT license
4//
5// Copyright (c) 2007-2008 Jiri Moudry, Pascal Craponne
6//
7// Permission is hereby granted, free of charge, to any person obtaining a copy
8// of this software and associated documentation files (the "Software"), to deal
9// in the Software without restriction, including without limitation the rights
10// to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
11// copies of the Software, and to permit persons to whom the Software is
12// furnished to do so, subject to the following conditions:
13//
14// The above copyright notice and this permission notice shall be included in
15// all copies or substantial portions of the Software.
16//
17// THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
18// IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
19// FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
20// AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
21// LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
22// OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
23// THE SOFTWARE.
24//
25#endregion
26
27using System;
28using System.Collections.Generic;
29using System.Text;
30using System.Linq;
31using System.Linq.Expressions;
32using NUnit.Framework;
33using Test_NUnit;
34
35using nwind;
36
37// test ns
38#if MYSQL
39 namespace Test_NUnit_MySql
40#elif ORACLE && ODP
41 namespace Test_NUnit_OracleODP
42#elif ORACLE
43 namespace Test_NUnit_Oracle
44#elif POSTGRES
45 namespace Test_NUnit_PostgreSql
46#elif SQLITE
47 namespace Test_NUnit_Sqlite
48#elif INGRES
49 namespace Test_NUnit_Ingres
50#elif MSSQL && L2SQL
51 namespace Test_NUnit_MsSql_Strict
52#elif MSSQL
53 namespace Test_NUnit_MsSql
54#elif FIREBIRD
55 namespace Test_NUnit_Firebird
56#endif
57{
58 [TestFixture]
59 public class ReadTest_GroupBy : TestBase
60 {
61
62
63 [Test]
64 public void G01_SimpleGroup_Count()
65 {
66 Northwind db = base.CreateDB();
67
68 var q2 = db.Customers.GroupBy(c => c.City)
69 .Select(g => new { g.Key, Count = g.Count() });
70
71 int rowCount = 0;
72 foreach (var g in q2)
73 {
74 rowCount++;
75 Assert.IsTrue(g.Count > 0, "Must have Count");
76 Assert.IsTrue(g.Key != null, "Must have City");
77 }
78 Assert.IsTrue(rowCount > 0, "Must have some rows");
79 }
80
81#if !DEBUG && (POSTGRES || (MSSQL && !L2SQL))
82 [Explicit]
83#endif
84 [Test]
85 public void G02_SimpleGroup_First()
86 {
87 try
88 {
89 //Note: this SQL is allowed in Mysql but illegal on Postgres
90 //(PostgreSql ERROR: column "c$.customerid" must appear in the GROUP BY clause or be used in an aggregate function - SQL state: 42803)
91 //"SELECT City, customerid FROM customer GROUP BY City"
92 //that's why DbLinq disallows it
93 Northwind db = base.CreateDB();
94 var q2 = db.Customers.GroupBy(c => c.City);
95 var q3 = q2.First();
96
97 Assert.IsTrue(q3 != null && q3.Key != null, "Must have result with Key");
98 foreach (var c in q3)
99 {
100 Assert.IsTrue(c.City != null, "City must be non-null");
101 }
102 }
103 catch(InvalidOperationException)
104 {
105 Assert.Ignore("Some vendors don't support this request (which doesn't make sense anyway)");
106 }
107 }
108
109#if !DEBUG && (POSTGRES || (MSSQL && !L2SQL))
110 [Explicit]
111#endif
112 [Test]
113 public void G03_SimpleGroup_WithSelector_Invalid()
114 {
115 try
116 {
117 //Note: this SQL is allowed in Mysql but illegal on Postgres
118 //(PostgreSql ERROR: column "c$.customerid" must appear in the GROUP BY clause or be used in an aggregate function - SQL state: 42803)
119 //"SELECT City, customerid FROM customer GROUP BY City"
120 Northwind db = base.CreateDB();
121
122 var q2 = db.Customers.GroupBy(c => c.City, c => new {c.City, c.CustomerID});
123
124 foreach (var g in q2)
125 {
126 int entryCount = 0;
127 foreach (var c in g)
128 {
129 Assert.IsTrue(c.City != null, "City must be non-null");
130 entryCount++;
131 }
132 Assert.IsTrue(entryCount > 0, "Must have some entries in group");
133 }
134 }
135 catch (InvalidOperationException)
136 {
137 Assert.Ignore("Some vendors don't support this request (which doesn't make sense anyway)");
138 }
139 }
140
141 [Test]
142 public void G03_DoubleKey()
143 {
144 //Note: this SQL is allowed in Mysql but illegal on Postgres
145 //(PostgreSql ERROR: column "c$.customerid" must appear in the GROUP BY clause or be used in an aggregate function - SQL state: 42803)
146 //"SELECT City, customerid FROM customer GROUP BY City"
147 Northwind db = base.CreateDB();
148
149 var q2 = from o in db.Orders
150 group o by new { o.CustomerID, o.EmployeeID } into g
151 select new { g.Key.CustomerID, g.Key.EmployeeID, Count = g.Count() };
152
153 int entryCount = 0;
154 foreach (var g in q2)
155 {
156 entryCount++;
157 Assert.IsTrue(g.CustomerID != null, "Must have non-null customerID");
158 Assert.IsTrue(g.EmployeeID > 0, "Must have >0 employeeID");
159 Assert.IsTrue(g.Count >= 0, "Must have non-neg Count");
160 }
161 Assert.IsTrue(entryCount > 0, "Must have some entries in group");
162 }
163
164
165#if !DEBUG && (POSTGRES || (MSSQL && !L2SQL))
166 [Explicit]
167#endif
168 [Test]
169 public void G04_SimpleGroup_WithSelector()
170 {
171 try
172 {
173 //Note: this SQL is allowed in Mysql but illegal on Postgres
174 //(PostgreSql ERROR: column "c$.customerid" must appear in the GROUP BY clause or be used in an aggregate function - SQL state: 42803)
175 //"SELECT City, customerid FROM customer GROUP BY City"
176 Northwind db = base.CreateDB();
177 var q2 = db.Customers.GroupBy(c => c.City, c => c.CustomerID);
178
179 foreach (var g in q2)
180 {
181 int entryCount = 0;
182 foreach (var c in g)
183 {
184 Assert.IsTrue(c != null, "CustomerID must be non-null");
185 entryCount++;
186 }
187 Assert.IsTrue(entryCount > 0, "Must have some entries in group");
188 }
189 }
190 catch (InvalidOperationException)
191 {
192 Assert.Ignore("Some vendors don't support this request (which doesn't make sense anyway)");
193 }
194 }
195
196#if !DEBUG && (SQLITE || POSTGRES || (MSSQL && !L2SQL))
197 [Explicit]
198#endif
199 [Test]
200 public void G05_Group_Into()
201 {
202 Northwind db = base.CreateDB();
203 var q2 =
204 from c in db.Customers
205 //where c.Country == "France"
206 group new { c.PostalCode, c.ContactName } by c.City into g
207 select g;
208 var q3 = from g in q2 select new { FortyTwo = 42, g.Key, Count = g.Count() };
209 //select new {g.Key.Length, g};
210 //select new {42,g};
211
212 int entryCount = 0;
213 foreach (var g in q3)
214 {
215 Assert.IsTrue(g.FortyTwo == 42, "Forty42 must be there");
216 Assert.IsTrue(g.Count > 0, "Positive count");
217 entryCount++;
218 }
219 Assert.IsTrue(entryCount > 0, "Must have some entries in group");
220 }
221
222
223 [Test]
224 public void G06_OrderCountByCustomerID()
225 {
226 Northwind db = base.CreateDB();
227
228 var q2 = from o in db.Orders
229 group o by o.CustomerID into g
230 //where g.Count()>1
231 select new { g.Key, OrderCount = g.Count() };
232
233 var lst = q2.ToList();
234 Assert.Greater(lst.Count, 0, "Expected some grouped order results");
235 var result0 = lst[0];
236 Assert.IsTrue(result0.Key != null, "Key must be non-null");
237 Assert.Greater(result0.OrderCount, 0, "Count must be > 0");
238 //select new { g.Key , SumPerCustomer = g.Sum(o2=>o2.OrderID) };
239 }
240
241#if !DEBUG && (SQLITE || POSTGRES || (MSSQL && !L2SQL))
242 [Explicit]
243#endif
244 [Test]
245 public void G07_OrderCountByCustomerID_Where()
246 {
247 Northwind db = base.CreateDB();
248
249 var q2 = from o in db.Orders
250 group o by o.CustomerID into g
251 where g.Count() > 1
252 select new { g.Key, OrderCount = g.Count() };
253
254 var lst = q2.ToList();
255 Assert.Greater(lst.Count, 0, "Expected some grouped order results");
256 var result0 = lst[0];
257 Assert.IsTrue(result0.Key != null, "Key must be non-null");
258 Assert.Greater(result0.OrderCount, 0, "Count must be > 0");
259 //select new { g.Key , SumPerCustomer = g.Sum(o2=>o2.OrderID) };
260 }
261
262#if !DEBUG && (SQLITE || POSTGRES || (MSSQL && !L2SQL))
263 [Explicit]
264#endif
265 [Test]
266 public void G08_OrderSumByCustomerID()
267 {
268 Northwind db = base.CreateDB();
269
270 var q2 = from o in db.Orders
271 group o by o.CustomerID into g
272 //where g.Count()>1
273 select new { g.Key, OrderSum = g.Sum(o => o.OrderID) };
274 var lst = q2.ToList();
275 Assert.Greater(lst.Count, 0, "Expected some grouped order results");
276 foreach (var result in lst)
277 {
278 Console.WriteLine(" Result: custID=" + result.Key + " sum=" + result.OrderSum);
279 Assert.IsTrue(result.Key != null, "Key must be non-null");
280 Assert.Greater(result.OrderSum, 0, "OrderSum must be > 0");
281 }
282 //select new { g.Key , SumPerCustomer = g.Sum(o2=>o2.OrderID) };
283 }
284
285 /// <summary>
286 /// Reported by pwy.mail in http://code.google.com/p/dblinq2007/issues/detail?id=64
287 /// </summary>
288#if !DEBUG && (POSTGRES || (MSSQL && !L2SQL))
289 [Explicit]
290#endif
291 [Test]
292 public void G09_UnitPriceGreaterThan10()
293 {
294 Northwind db = base.CreateDB();
295
296 var priceQuery =
297 from prod in db.Products
298 group prod by new
299 {
300 Criterion = prod.UnitPrice > 10
301 }
302 into grouping
303 select grouping;
304
305 foreach (var prodObj in priceQuery)
306 {
307 if (prodObj.Key.Criterion == false)
308 Console.WriteLine("Prices 10 or less:");
309 else
310 Console.WriteLine("\nPrices greater than 10");
311 foreach (var listing in prodObj)
312 {
313 Console.WriteLine("{0}, {1}", listing.ProductName,
314 listing.UnitPrice);
315 }
316 }
317
318 }
319
320
321 }
322}