PageRenderTime 56ms CodeModel.GetById 25ms RepoModel.GetById 0ms app.codeStats 0ms

/mcs/class/System.Data.Linq/src/DbLinq/Test/Providers/ReadTest_GroupBy.cs

https://github.com/iainlane/mono
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. using System;
  27. using System.Collections.Generic;
  28. using System.Text;
  29. using System.Linq;
  30. using System.Linq.Expressions;
  31. using NUnit.Framework;
  32. using Test_NUnit;
  33. using nwind;
  34. // test ns
  35. #if MYSQL
  36. namespace Test_NUnit_MySql
  37. #elif ORACLE && ODP
  38. namespace Test_NUnit_OracleODP
  39. #elif ORACLE
  40. namespace Test_NUnit_Oracle
  41. #elif POSTGRES
  42. namespace Test_NUnit_PostgreSql
  43. #elif SQLITE
  44. namespace Test_NUnit_Sqlite
  45. #elif INGRES
  46. namespace Test_NUnit_Ingres
  47. #elif MSSQL && L2SQL
  48. namespace Test_NUnit_MsSql_Strict
  49. #elif MSSQL
  50. namespace Test_NUnit_MsSql
  51. #elif FIREBIRD
  52. namespace Test_NUnit_Firebird
  53. #endif
  54. {
  55. [TestFixture]
  56. public class ReadTest_GroupBy : TestBase
  57. {
  58. [Test]
  59. public void G01_SimpleGroup_Count()
  60. {
  61. Northwind db = base.CreateDB();
  62. var q2 = db.Customers.GroupBy(c => c.City)
  63. .Select(g => new { g.Key, Count = g.Count() });
  64. int rowCount = 0;
  65. foreach (var g in q2)
  66. {
  67. rowCount++;
  68. Assert.IsTrue(g.Count > 0, "Must have Count");
  69. Assert.IsTrue(g.Key != null, "Must have City");
  70. }
  71. Assert.IsTrue(rowCount > 0, "Must have some rows");
  72. }
  73. #if !DEBUG && (POSTGRES || (MSSQL && !L2SQL))
  74. [Explicit]
  75. #endif
  76. [Test]
  77. public void G02_SimpleGroup_First()
  78. {
  79. try
  80. {
  81. //Note: this SQL is allowed in Mysql but illegal on Postgres
  82. //(PostgreSql ERROR: column "c$.customerid" must appear in the GROUP BY clause or be used in an aggregate function - SQL state: 42803)
  83. //"SELECT City, customerid FROM customer GROUP BY City"
  84. //that's why DbLinq disallows it
  85. Northwind db = base.CreateDB();
  86. var q2 = db.Customers.GroupBy(c => c.City);
  87. var q3 = q2.First();
  88. Assert.IsTrue(q3 != null && q3.Key != null, "Must have result with Key");
  89. foreach (var c in q3)
  90. {
  91. Assert.IsTrue(c.City != null, "City must be non-null");
  92. }
  93. }
  94. catch(InvalidOperationException)
  95. {
  96. Assert.Ignore("Some vendors don't support this request (which doesn't make sense anyway)");
  97. }
  98. }
  99. #if !DEBUG && (POSTGRES || (MSSQL && !L2SQL))
  100. [Explicit]
  101. #endif
  102. [Test]
  103. public void G03_SimpleGroup_WithSelector_Invalid()
  104. {
  105. try
  106. {
  107. //Note: this SQL is allowed in Mysql but illegal on Postgres
  108. //(PostgreSql ERROR: column "c$.customerid" must appear in the GROUP BY clause or be used in an aggregate function - SQL state: 42803)
  109. //"SELECT City, customerid FROM customer GROUP BY City"
  110. Northwind db = base.CreateDB();
  111. var q2 = db.Customers.GroupBy(c => c.City, c => new {c.City, c.CustomerID});
  112. foreach (var g in q2)
  113. {
  114. int entryCount = 0;
  115. foreach (var c in g)
  116. {
  117. Assert.IsTrue(c.City != null, "City must be non-null");
  118. entryCount++;
  119. }
  120. Assert.IsTrue(entryCount > 0, "Must have some entries in group");
  121. }
  122. }
  123. catch (InvalidOperationException)
  124. {
  125. Assert.Ignore("Some vendors don't support this request (which doesn't make sense anyway)");
  126. }
  127. }
  128. [Test]
  129. public void G03_DoubleKey()
  130. {
  131. //Note: this SQL is allowed in Mysql but illegal on Postgres
  132. //(PostgreSql ERROR: column "c$.customerid" must appear in the GROUP BY clause or be used in an aggregate function - SQL state: 42803)
  133. //"SELECT City, customerid FROM customer GROUP BY City"
  134. Northwind db = base.CreateDB();
  135. var q2 = from o in db.Orders
  136. group o by new { o.CustomerID, o.EmployeeID } into g
  137. select new { g.Key.CustomerID, g.Key.EmployeeID, Count = g.Count() };
  138. int entryCount = 0;
  139. foreach (var g in q2)
  140. {
  141. entryCount++;
  142. Assert.IsTrue(g.CustomerID != null, "Must have non-null customerID");
  143. Assert.IsTrue(g.EmployeeID > 0, "Must have >0 employeeID");
  144. Assert.IsTrue(g.Count >= 0, "Must have non-neg Count");
  145. }
  146. Assert.IsTrue(entryCount > 0, "Must have some entries in group");
  147. }
  148. #if !DEBUG && (POSTGRES || (MSSQL && !L2SQL))
  149. [Explicit]
  150. #endif
  151. [Test]
  152. public void G04_SimpleGroup_WithSelector()
  153. {
  154. try
  155. {
  156. //Note: this SQL is allowed in Mysql but illegal on Postgres
  157. //(PostgreSql ERROR: column "c$.customerid" must appear in the GROUP BY clause or be used in an aggregate function - SQL state: 42803)
  158. //"SELECT City, customerid FROM customer GROUP BY City"
  159. Northwind db = base.CreateDB();
  160. var q2 = db.Customers.GroupBy(c => c.City, c => c.CustomerID);
  161. foreach (var g in q2)
  162. {
  163. int entryCount = 0;
  164. foreach (var c in g)
  165. {
  166. Assert.IsTrue(c != null, "CustomerID must be non-null");
  167. entryCount++;
  168. }
  169. Assert.IsTrue(entryCount > 0, "Must have some entries in group");
  170. }
  171. }
  172. catch (InvalidOperationException)
  173. {
  174. Assert.Ignore("Some vendors don't support this request (which doesn't make sense anyway)");
  175. }
  176. }
  177. #if !DEBUG && (SQLITE || POSTGRES || (MSSQL && !L2SQL))
  178. [Explicit]
  179. #endif
  180. [Test]
  181. public void G05_Group_Into()
  182. {
  183. Northwind db = base.CreateDB();
  184. var q2 =
  185. from c in db.Customers
  186. //where c.Country == "France"
  187. group new { c.PostalCode, c.ContactName } by c.City into g
  188. select g;
  189. var q3 = from g in q2 select new { FortyTwo = 42, g.Key, Count = g.Count() };
  190. //select new {g.Key.Length, g};
  191. //select new {42,g};
  192. int entryCount = 0;
  193. foreach (var g in q3)
  194. {
  195. Assert.IsTrue(g.FortyTwo == 42, "Forty42 must be there");
  196. Assert.IsTrue(g.Count > 0, "Positive count");
  197. entryCount++;
  198. }
  199. Assert.IsTrue(entryCount > 0, "Must have some entries in group");
  200. }
  201. [Test]
  202. public void G06_OrderCountByCustomerID()
  203. {
  204. Northwind db = base.CreateDB();
  205. var q2 = from o in db.Orders
  206. group o by o.CustomerID into g
  207. //where g.Count()>1
  208. select new { g.Key, OrderCount = g.Count() };
  209. var lst = q2.ToList();
  210. Assert.Greater(lst.Count, 0, "Expected some grouped order results");
  211. var result0 = lst[0];
  212. Assert.IsTrue(result0.Key != null, "Key must be non-null");
  213. Assert.Greater(result0.OrderCount, 0, "Count must be > 0");
  214. //select new { g.Key , SumPerCustomer = g.Sum(o2=>o2.OrderID) };
  215. }
  216. #if !DEBUG && (SQLITE || POSTGRES || (MSSQL && !L2SQL))
  217. [Explicit]
  218. #endif
  219. [Test]
  220. public void G07_OrderCountByCustomerID_Where()
  221. {
  222. Northwind db = base.CreateDB();
  223. var q2 = from o in db.Orders
  224. group o by o.CustomerID into g
  225. where g.Count() > 1
  226. select new { g.Key, OrderCount = g.Count() };
  227. var lst = q2.ToList();
  228. Assert.Greater(lst.Count, 0, "Expected some grouped order results");
  229. var result0 = lst[0];
  230. Assert.IsTrue(result0.Key != null, "Key must be non-null");
  231. Assert.Greater(result0.OrderCount, 0, "Count must be > 0");
  232. //select new { g.Key , SumPerCustomer = g.Sum(o2=>o2.OrderID) };
  233. }
  234. #if !DEBUG && (SQLITE || POSTGRES || (MSSQL && !L2SQL))
  235. [Explicit]
  236. #endif
  237. [Test]
  238. public void G08_OrderSumByCustomerID()
  239. {
  240. Northwind db = base.CreateDB();
  241. var q2 = from o in db.Orders
  242. group o by o.CustomerID into g
  243. //where g.Count()>1
  244. select new { g.Key, OrderSum = g.Sum(o => o.OrderID) };
  245. var lst = q2.ToList();
  246. Assert.Greater(lst.Count, 0, "Expected some grouped order results");
  247. foreach (var result in lst)
  248. {
  249. Console.WriteLine(" Result: custID=" + result.Key + " sum=" + result.OrderSum);
  250. Assert.IsTrue(result.Key != null, "Key must be non-null");
  251. Assert.Greater(result.OrderSum, 0, "OrderSum must be > 0");
  252. }
  253. //select new { g.Key , SumPerCustomer = g.Sum(o2=>o2.OrderID) };
  254. }
  255. /// <summary>
  256. /// Reported by pwy.mail in http://code.google.com/p/dblinq2007/issues/detail?id=64
  257. /// </summary>
  258. #if !DEBUG && (POSTGRES || (MSSQL && !L2SQL))
  259. [Explicit]
  260. #endif
  261. [Test]
  262. public void G09_UnitPriceGreaterThan10()
  263. {
  264. Northwind db = base.CreateDB();
  265. var priceQuery =
  266. from prod in db.Products
  267. group prod by new
  268. {
  269. Criterion = prod.UnitPrice > 10
  270. }
  271. into grouping
  272. select grouping;
  273. foreach (var prodObj in priceQuery)
  274. {
  275. if (prodObj.Key.Criterion == false)
  276. Console.WriteLine("Prices 10 or less:");
  277. else
  278. Console.WriteLine("\nPrices greater than 10");
  279. foreach (var listing in prodObj)
  280. {
  281. Console.WriteLine("{0}, {1}", listing.ProductName,
  282. listing.UnitPrice);
  283. }
  284. }
  285. }
  286. }
  287. }