5 // Copyright (c) 2007-2008 Jiri Moudry, Pascal Craponne
\r
7 // Permission is hereby granted, free of charge, to any person obtaining a copy
\r
8 // of this software and associated documentation files (the "Software"), to deal
\r
9 // in the Software without restriction, including without limitation the rights
\r
10 // to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
\r
11 // copies of the Software, and to permit persons to whom the Software is
\r
12 // furnished to do so, subject to the following conditions:
\r
14 // The above copyright notice and this permission notice shall be included in
\r
15 // all copies or substantial portions of the Software.
\r
17 // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
\r
18 // IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
\r
19 // FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
\r
20 // AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
\r
21 // LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
\r
22 // OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
\r
28 using System.Collections.Generic;
\r
31 using System.Linq.Expressions;
\r
32 using NUnit.Framework;
\r
39 namespace Test_NUnit_MySql
\r
41 namespace Test_NUnit_OracleODP
\r
43 namespace Test_NUnit_Oracle
\r
45 namespace Test_NUnit_PostgreSql
\r
47 namespace Test_NUnit_Sqlite
\r
49 namespace Test_NUnit_Ingres
\r
50 #elif MSSQL && L2SQL
\r
51 namespace Test_NUnit_MsSql_Strict
\r
53 namespace Test_NUnit_MsSql
\r
55 namespace Test_NUnit_Firebird
\r
59 public class ReadTest_GroupBy : TestBase
\r
64 public void G01_SimpleGroup_Count()
\r
66 Northwind db = base.CreateDB();
\r
68 var q2 = db.Customers.GroupBy(c => c.City)
\r
69 .Select(g => new { g.Key, Count = g.Count() });
\r
72 foreach (var g in q2)
\r
75 Assert.IsTrue(g.Count > 0, "Must have Count");
\r
76 Assert.IsTrue(g.Key != null, "Must have City");
\r
78 Assert.IsTrue(rowCount > 0, "Must have some rows");
\r
81 #if !DEBUG && (POSTGRES || (MSSQL && !L2SQL))
\r
85 public void G02_SimpleGroup_First()
\r
89 //Note: this SQL is allowed in Mysql but illegal on Postgres
\r
90 //(PostgreSql ERROR: column "c$.customerid" must appear in the GROUP BY clause or be used in an aggregate function - SQL state: 42803)
\r
91 //"SELECT City, customerid FROM customer GROUP BY City"
\r
92 //that's why DbLinq disallows it
\r
93 Northwind db = base.CreateDB();
\r
94 var q2 = db.Customers.GroupBy(c => c.City);
\r
95 var q3 = q2.First();
\r
97 Assert.IsTrue(q3 != null && q3.Key != null, "Must have result with Key");
\r
98 foreach (var c in q3)
\r
100 Assert.IsTrue(c.City != null, "City must be non-null");
\r
103 catch(InvalidOperationException)
\r
105 Assert.Ignore("Some vendors don't support this request (which doesn't make sense anyway)");
\r
109 #if !DEBUG && (POSTGRES || (MSSQL && !L2SQL))
\r
113 public void G03_SimpleGroup_WithSelector_Invalid()
\r
117 //Note: this SQL is allowed in Mysql but illegal on Postgres
\r
118 //(PostgreSql ERROR: column "c$.customerid" must appear in the GROUP BY clause or be used in an aggregate function - SQL state: 42803)
\r
119 //"SELECT City, customerid FROM customer GROUP BY City"
\r
120 Northwind db = base.CreateDB();
\r
122 var q2 = db.Customers.GroupBy(c => c.City, c => new {c.City, c.CustomerID});
\r
124 foreach (var g in q2)
\r
126 int entryCount = 0;
\r
127 foreach (var c in g)
\r
129 Assert.IsTrue(c.City != null, "City must be non-null");
\r
132 Assert.IsTrue(entryCount > 0, "Must have some entries in group");
\r
135 catch (InvalidOperationException)
\r
137 Assert.Ignore("Some vendors don't support this request (which doesn't make sense anyway)");
\r
142 public void G03_DoubleKey()
\r
144 //Note: this SQL is allowed in Mysql but illegal on Postgres
\r
145 //(PostgreSql ERROR: column "c$.customerid" must appear in the GROUP BY clause or be used in an aggregate function - SQL state: 42803)
\r
146 //"SELECT City, customerid FROM customer GROUP BY City"
\r
147 Northwind db = base.CreateDB();
\r
149 var q2 = from o in db.Orders
\r
150 group o by new { o.CustomerID, o.EmployeeID } into g
\r
151 select new { g.Key.CustomerID, g.Key.EmployeeID, Count = g.Count() };
\r
153 int entryCount = 0;
\r
154 foreach (var g in q2)
\r
157 Assert.IsTrue(g.CustomerID != null, "Must have non-null customerID");
\r
158 Assert.IsTrue(g.EmployeeID > 0, "Must have >0 employeeID");
\r
159 Assert.IsTrue(g.Count >= 0, "Must have non-neg Count");
\r
161 Assert.IsTrue(entryCount > 0, "Must have some entries in group");
\r
165 #if !DEBUG && (POSTGRES || (MSSQL && !L2SQL))
\r
169 public void G04_SimpleGroup_WithSelector()
\r
173 //Note: this SQL is allowed in Mysql but illegal on Postgres
\r
174 //(PostgreSql ERROR: column "c$.customerid" must appear in the GROUP BY clause or be used in an aggregate function - SQL state: 42803)
\r
175 //"SELECT City, customerid FROM customer GROUP BY City"
\r
176 Northwind db = base.CreateDB();
\r
177 var q2 = db.Customers.GroupBy(c => c.City, c => c.CustomerID);
\r
179 foreach (var g in q2)
\r
181 int entryCount = 0;
\r
182 foreach (var c in g)
\r
184 Assert.IsTrue(c != null, "CustomerID must be non-null");
\r
187 Assert.IsTrue(entryCount > 0, "Must have some entries in group");
\r
190 catch (InvalidOperationException)
\r
192 Assert.Ignore("Some vendors don't support this request (which doesn't make sense anyway)");
\r
196 #if !DEBUG && (SQLITE || POSTGRES || (MSSQL && !L2SQL))
\r
200 public void G05_Group_Into()
\r
202 Northwind db = base.CreateDB();
\r
204 from c in db.Customers
\r
205 //where c.Country == "France"
\r
206 group new { c.PostalCode, c.ContactName } by c.City into g
\r
208 var q3 = from g in q2 select new { FortyTwo = 42, g.Key, Count = g.Count() };
\r
209 //select new {g.Key.Length, g};
\r
210 //select new {42,g};
\r
212 int entryCount = 0;
\r
213 foreach (var g in q3)
\r
215 Assert.IsTrue(g.FortyTwo == 42, "Forty42 must be there");
\r
216 Assert.IsTrue(g.Count > 0, "Positive count");
\r
219 Assert.IsTrue(entryCount > 0, "Must have some entries in group");
\r
224 public void G06_OrderCountByCustomerID()
\r
226 Northwind db = base.CreateDB();
\r
228 var q2 = from o in db.Orders
\r
229 group o by o.CustomerID into g
\r
230 //where g.Count()>1
\r
231 select new { g.Key, OrderCount = g.Count() };
\r
233 var lst = q2.ToList();
\r
234 AssertHelper.Greater(lst.Count, 0, "Expected some grouped order results");
\r
235 var result0 = lst[0];
\r
236 Assert.IsTrue(result0.Key != null, "Key must be non-null");
\r
237 AssertHelper.Greater(result0.OrderCount, 0, "Count must be > 0");
\r
238 //select new { g.Key , SumPerCustomer = g.Sum(o2=>o2.OrderID) };
\r
241 #if !DEBUG && (SQLITE || POSTGRES || (MSSQL && !L2SQL))
\r
245 public void G07_OrderCountByCustomerID_Where()
\r
247 Northwind db = base.CreateDB();
\r
249 var q2 = from o in db.Orders
\r
250 group o by o.CustomerID into g
\r
251 where g.Count() > 1
\r
252 select new { g.Key, OrderCount = g.Count() };
\r
254 var lst = q2.ToList();
\r
255 AssertHelper.Greater(lst.Count, 0, "Expected some grouped order results");
\r
256 var result0 = lst[0];
\r
257 Assert.IsTrue(result0.Key != null, "Key must be non-null");
\r
258 AssertHelper.Greater(result0.OrderCount, 0, "Count must be > 0");
\r
259 //select new { g.Key , SumPerCustomer = g.Sum(o2=>o2.OrderID) };
\r
262 #if !DEBUG && (SQLITE || POSTGRES || (MSSQL && !L2SQL))
\r
266 public void G08_OrderSumByCustomerID()
\r
268 Northwind db = base.CreateDB();
\r
270 var q2 = from o in db.Orders
\r
271 group o by o.CustomerID into g
\r
272 //where g.Count()>1
\r
273 select new { g.Key, OrderSum = g.Sum(o => o.OrderID) };
\r
274 var lst = q2.ToList();
\r
275 AssertHelper.Greater(lst.Count, 0, "Expected some grouped order results");
\r
276 foreach (var result in lst)
\r
278 Console.WriteLine(" Result: custID=" + result.Key + " sum=" + result.OrderSum);
\r
279 Assert.IsTrue(result.Key != null, "Key must be non-null");
\r
280 AssertHelper.Greater(result.OrderSum, 0, "OrderSum must be > 0");
\r
282 //select new { g.Key , SumPerCustomer = g.Sum(o2=>o2.OrderID) };
\r
286 /// Reported by pwy.mail in http://code.google.com/p/dblinq2007/issues/detail?id=64
\r
288 #if !DEBUG && (POSTGRES || (MSSQL && !L2SQL))
\r
292 public void G09_UnitPriceGreaterThan10()
\r
294 Northwind db = base.CreateDB();
\r
297 from prod in db.Products
\r
300 Criterion = prod.UnitPrice > 10
\r
305 foreach (var prodObj in priceQuery)
\r
307 if (prodObj.Key.Criterion == false)
\r
308 Console.WriteLine("Prices 10 or less:");
\r
310 Console.WriteLine("\nPrices greater than 10");
\r
311 foreach (var listing in prodObj)
\r
313 Console.WriteLine("{0}, {1}", listing.ProductName,
\r
314 listing.UnitPrice);
\r