PageRenderTime 39ms CodeModel.GetById 28ms app.highlight 8ms RepoModel.GetById 1ms app.codeStats 0ms

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

https://github.com/QuickJack/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
 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}