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

#region MIT license

// 

// MIT license

//

// Copyright (c) 2007-2008 Jiri Moudry, Pascal Craponne

// 

// Permission is hereby granted, free of charge, to any person obtaining a copy

// of this software and associated documentation files (the "Software"), to deal

// in the Software without restriction, including without limitation the rights

// to use, copy, modify, merge, publish, distribute, sublicense, and/or sell

// copies of the Software, and to permit persons to whom the Software is

// furnished to do so, subject to the following conditions:

// 

// The above copyright notice and this permission notice shall be included in

// all copies or substantial portions of the Software.

// 

// THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR

// IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,

// FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE

// AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER

// LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,

// OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN

// THE SOFTWARE.

// 

#endregion



using System;

using System.Collections.Generic;

using System.Text;

using System.Linq;

using System.Linq.Expressions;

using NUnit.Framework;

using Test_NUnit;



using nwind;



// test ns 

#if MYSQL

    namespace Test_NUnit_MySql

#elif ORACLE && ODP

    namespace Test_NUnit_OracleODP

#elif ORACLE

    namespace Test_NUnit_Oracle

#elif POSTGRES

    namespace Test_NUnit_PostgreSql

#elif SQLITE

    namespace Test_NUnit_Sqlite

#elif INGRES

    namespace Test_NUnit_Ingres

#elif MSSQL && L2SQL

    namespace Test_NUnit_MsSql_Strict

#elif MSSQL

    namespace Test_NUnit_MsSql

#elif FIREBIRD

    namespace Test_NUnit_Firebird

#endif

{

    [TestFixture]

    public class ReadTest_GroupBy : TestBase

    {





        [Test]

        public void G01_SimpleGroup_Count()

        {

            Northwind db = base.CreateDB();



            var q2 = db.Customers.GroupBy(c => c.City)

                .Select(g => new { g.Key, Count = g.Count() });



            int rowCount = 0;

            foreach (var g in q2)

            {

                rowCount++;

                Assert.IsTrue(g.Count > 0, "Must have Count");

                Assert.IsTrue(g.Key != null, "Must have City");

            }

            Assert.IsTrue(rowCount > 0, "Must have some rows");

        }



#if !DEBUG && (POSTGRES || (MSSQL && !L2SQL))

        [Explicit]

#endif

        [Test]

        public void G02_SimpleGroup_First()

        {

            try

            {

                //Note: this SQL is allowed in Mysql but illegal on Postgres 

                //(PostgreSql ERROR: column "c$.customerid" must appear in the GROUP BY clause or be used in an aggregate function - SQL state: 42803)

                //"SELECT City, customerid FROM customer GROUP BY City"

                //that's why DbLinq disallows it

                Northwind db = base.CreateDB();

                var q2 = db.Customers.GroupBy(c => c.City);

                var q3 = q2.First();



                Assert.IsTrue(q3 != null && q3.Key != null, "Must have result with Key");

                foreach (var c in q3)

                {

                    Assert.IsTrue(c.City != null, "City must be non-null");

                }

            }

            catch(InvalidOperationException)

            {

                Assert.Ignore("Some vendors don't support this request (which doesn't make sense anyway)");

            }

        }



#if !DEBUG && (POSTGRES || (MSSQL && !L2SQL))

        [Explicit]

#endif

        [Test]

        public void G03_SimpleGroup_WithSelector_Invalid()

        {

            try

            {

                //Note: this SQL is allowed in Mysql but illegal on Postgres 

                //(PostgreSql ERROR: column "c$.customerid" must appear in the GROUP BY clause or be used in an aggregate function - SQL state: 42803)

                //"SELECT City, customerid FROM customer GROUP BY City"

                Northwind db = base.CreateDB();



                var q2 = db.Customers.GroupBy(c => c.City, c => new {c.City, c.CustomerID});



                foreach (var g in q2)

                {

                    int entryCount = 0;

                    foreach (var c in g)

                    {

                        Assert.IsTrue(c.City != null, "City must be non-null");

                        entryCount++;

                    }

                    Assert.IsTrue(entryCount > 0, "Must have some entries in group");

                }

            }

            catch (InvalidOperationException)

            {

                Assert.Ignore("Some vendors don't support this request (which doesn't make sense anyway)");

            }

        }



        [Test]

        public void G03_DoubleKey()

        {

            //Note: this SQL is allowed in Mysql but illegal on Postgres 

            //(PostgreSql ERROR: column "c$.customerid" must appear in the GROUP BY clause or be used in an aggregate function - SQL state: 42803)

            //"SELECT City, customerid FROM customer GROUP BY City"

            Northwind db = base.CreateDB();



            var q2 = from o in db.Orders

                     group o by new { o.CustomerID, o.EmployeeID } into g

                     select new { g.Key.CustomerID, g.Key.EmployeeID, Count = g.Count() };



            int entryCount = 0;

            foreach (var g in q2)

            {

                entryCount++;

                Assert.IsTrue(g.CustomerID != null, "Must have non-null customerID");

                Assert.IsTrue(g.EmployeeID > 0, "Must have >0 employeeID");

                Assert.IsTrue(g.Count >= 0, "Must have non-neg Count");

            }

            Assert.IsTrue(entryCount > 0, "Must have some entries in group");

        }





#if !DEBUG && (POSTGRES || (MSSQL && !L2SQL))

        [Explicit]

#endif

        [Test]

        public void G04_SimpleGroup_WithSelector()

        {

            try

            {

                //Note: this SQL is allowed in Mysql but illegal on Postgres 

                //(PostgreSql ERROR: column "c$.customerid" must appear in the GROUP BY clause or be used in an aggregate function - SQL state: 42803)

                //"SELECT City, customerid FROM customer GROUP BY City"

                Northwind db = base.CreateDB();

                var q2 = db.Customers.GroupBy(c => c.City, c => c.CustomerID);



                foreach (var g in q2)

                {

                    int entryCount = 0;

                    foreach (var c in g)

                    {

                        Assert.IsTrue(c != null, "CustomerID must be non-null");

                        entryCount++;

                    }

                    Assert.IsTrue(entryCount > 0, "Must have some entries in group");

                }

            }

            catch (InvalidOperationException)

            {

                Assert.Ignore("Some vendors don't support this request (which doesn't make sense anyway)");

            }

        }



#if !DEBUG && (SQLITE || POSTGRES || (MSSQL && !L2SQL))

        [Explicit]

#endif

        [Test]

        public void G05_Group_Into()

        {

            Northwind db = base.CreateDB();

            var q2 =

                from c in db.Customers

                //where c.Country == "France"

                group new { c.PostalCode, c.ContactName } by c.City into g

                select g;

            var q3 = from g in q2 select new { FortyTwo = 42, g.Key, Count = g.Count() };

            //select new {g.Key.Length, g};

            //select new {42,g};



            int entryCount = 0;

            foreach (var g in q3)

            {

                Assert.IsTrue(g.FortyTwo == 42, "Forty42 must be there");

                Assert.IsTrue(g.Count > 0, "Positive count");

                entryCount++;

            }

            Assert.IsTrue(entryCount > 0, "Must have some entries in group");

        }





        [Test]

        public void G06_OrderCountByCustomerID()

        {

            Northwind db = base.CreateDB();



            var q2 = from o in db.Orders

                     group o by o.CustomerID into g

                     //where g.Count()>1

                     select new { g.Key, OrderCount = g.Count() };



            var lst = q2.ToList();

            Assert.Greater(lst.Count, 0, "Expected some grouped order results");

            var result0 = lst[0];

            Assert.IsTrue(result0.Key != null, "Key must be non-null");

            Assert.Greater(result0.OrderCount, 0, "Count must be > 0");

            //select new { g.Key , SumPerCustomer = g.Sum(o2=>o2.OrderID) };

        }



#if !DEBUG && (SQLITE || POSTGRES || (MSSQL && !L2SQL))

        [Explicit]

#endif

        [Test]

        public void G07_OrderCountByCustomerID_Where()

        {

            Northwind db = base.CreateDB();



            var q2 = from o in db.Orders

                     group o by o.CustomerID into g

                     where g.Count() > 1

                     select new { g.Key, OrderCount = g.Count() };



            var lst = q2.ToList();

            Assert.Greater(lst.Count, 0, "Expected some grouped order results");

            var result0 = lst[0];

            Assert.IsTrue(result0.Key != null, "Key must be non-null");

            Assert.Greater(result0.OrderCount, 0, "Count must be > 0");

            //select new { g.Key , SumPerCustomer = g.Sum(o2=>o2.OrderID) };

        }



#if !DEBUG && (SQLITE || POSTGRES || (MSSQL && !L2SQL))

        [Explicit]

#endif

        [Test]

        public void G08_OrderSumByCustomerID()

        {

            Northwind db = base.CreateDB();



            var q2 = from o in db.Orders

                     group o by o.CustomerID into g

                     //where g.Count()>1

                     select new { g.Key, OrderSum = g.Sum(o => o.OrderID) };

            var lst = q2.ToList();

            Assert.Greater(lst.Count, 0, "Expected some grouped order results");

            foreach (var result in lst)

            {

                Console.WriteLine("  Result: custID=" + result.Key + " sum=" + result.OrderSum);

                Assert.IsTrue(result.Key != null, "Key must be non-null");

                Assert.Greater(result.OrderSum, 0, "OrderSum must be > 0");

            }

            //select new { g.Key , SumPerCustomer = g.Sum(o2=>o2.OrderID) };

        }



        /// <summary>

        /// Reported by  pwy.mail in http://code.google.com/p/dblinq2007/issues/detail?id=64

        /// </summary>

#if !DEBUG && (POSTGRES || (MSSQL && !L2SQL))

        [Explicit]

#endif

        [Test]

        public void G09_UnitPriceGreaterThan10()

        {

            Northwind db = base.CreateDB();



            var priceQuery =

                from prod in db.Products

                group prod by new

                {

                    Criterion = prod.UnitPrice > 10

                }

                    into grouping

                    select grouping;



            foreach (var prodObj in priceQuery)

            {

                if (prodObj.Key.Criterion == false)

                    Console.WriteLine("Prices 10 or less:");

                else

                    Console.WriteLine("\nPrices greater than 10");

                foreach (var listing in prodObj)

                {

                    Console.WriteLine("{0}, {1}", listing.ProductName,

                        listing.UnitPrice);

                }

            }



        }





    }

}