PageRenderTime 49ms CodeModel.GetById 2ms app.highlight 37ms RepoModel.GetById 2ms app.codeStats 0ms

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

https://github.com/ekovalenko-softheme/mono
C# | 1215 lines | 972 code | 167 blank | 76 comment | 77 complexity | 645ee6223161f8dfb563b0d2a2f201c8 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
  26using System;
  27using System.Collections.Generic;
  28using System.Text;
  29using System.Linq;
  30using NUnit.Framework;
  31using Test_NUnit;
  32using System.Data.Linq.Mapping;
  33
  34using nwind;
  35
  36#if MONO_STRICT
  37using DataLinq = System.Data.Linq;
  38#else
  39using DataLinq = DbLinq.Data.Linq;
  40#endif
  41
  42namespace nwind
  43{
  44    interface IHasAddress
  45    {
  46        string Address { get; set; }
  47    }
  48
  49    partial class Customer : IHasAddress
  50    {
  51    }
  52
  53    partial class Employee : IHasAddress
  54    {
  55    }
  56}
  57
  58// test ns 
  59#if MYSQL
  60    namespace Test_NUnit_MySql
  61#elif ORACLE && ODP
  62    namespace Test_NUnit_OracleODP
  63#elif ORACLE
  64    namespace Test_NUnit_Oracle
  65#elif POSTGRES
  66    namespace Test_NUnit_PostgreSql
  67#elif SQLITE
  68    namespace Test_NUnit_Sqlite
  69#elif INGRES
  70    namespace Test_NUnit_Ingres
  71#elif MSSQL && L2SQL
  72    namespace Test_NUnit_MsSql_Strict
  73#elif MSSQL
  74    namespace Test_NUnit_MsSql
  75#elif FIREBIRD
  76    namespace Test_NUnit_Firebird
  77#endif
  78{
  79    [TestFixture]
  80    public class ReadTest : TestBase
  81    {
  82        #region Tests 'A' check for DB being ready
  83
  84
  85        /// <summary>
  86        /// in NUnit, tests are executed in alpha order.
  87        /// We want to start by checking access to DB.
  88        /// </summary>
  89        [Test]
  90        public void A1_PingDatabase()
  91        {
  92            Northwind db = CreateDB();
  93            bool pingOK = db.DatabaseExists();
  94            //bool pingOK = Conn.Ping(); //Schildkroete - Ping throws NullRef if conn is not open
  95            Assert.IsTrue(pingOK, "Pinging database");
  96        }
  97
  98
  99
 100#if !DEBUG && (MSSQL && L2SQL)
 101        // L2SQL doesn't support 'SELECT' queries in DataContext.ExecuteCommand().
 102        [Explicit]
 103#endif
 104        [Test]
 105        public void A3_ProductsTableHasPen()
 106        {
 107            Northwind db = CreateDB();
 108            //string sql = @"SELECT count(*) FROM linqtestdb.Products WHERE ProductName='Chai'";
 109            string sql = @"SELECT count(*) FROM [Products] WHERE [ProductName]='Chai'";
 110            long iResult = db.ExecuteCommand(sql);
 111            //long iResult = base.ExecuteScalar(sql);
 112            Assert.AreEqual(iResult, 1L, "Expecting one Chai in Products table, got:" + iResult + " (SQL:" + sql + ")");
 113        }
 114
 115        [Test]
 116        public void A4_SelectSingleCustomer()
 117        {
 118            Northwind db = CreateDB();
 119
 120            // Query for a specific customer
 121            var cust = db.Customers.Single(c => c.CompanyName == "Around the Horn");
 122            Assert.IsNotNull(cust, "Expected one customer 'Around the Horn'.");
 123            var id = 1;
 124            var prod = db.Products.Single(p => p.ProductID == id);
 125            Assert.AreEqual("Chai", prod.ProductName);
 126            id = 2;
 127            prod = db.Products.Single(p => p.ProductID == id);
 128            Assert.AreEqual("Chang", prod.ProductName);
 129        }
 130
 131        [Test]
 132        public void A5_SelectSingleOrDefault()
 133        {
 134            Northwind db = CreateDB();
 135
 136            // Query for a specific customer
 137            var cust = db.Customers.SingleOrDefault(c => c.CompanyName == "Around the Horn");
 138            Assert.IsNotNull(cust, "Expected one customer 'Around the Horn'.");
 139
 140#if false
 141            var id = "ALFKI";
 142            cust = db.Customers.SingleOrDefault(c => c.CustomerID == id);
 143            Assert.AreEqual("ALFKI", cust.CustomerID);
 144            id = "BLAUS";
 145            cust = db.Customers.SingleOrDefault(c => c.CustomerID == id);
 146            Assert.AreEqual("BLAUS", cust.CustomerID);
 147            id = "DNE";
 148            cust = db.Customers.SingleOrDefault(c => c.CustomerID == id); // Does Not Exist
 149            Assert.IsNull(cust);
 150
 151            id = "ALFKI";
 152            cust = db.Customers.SingleOrDefault(c => c.CustomerID == id);
 153            Assert.AreEqual("ALFKI", cust.CustomerID);
 154            id = "BLAUS";
 155            cust = db.Customers.SingleOrDefault(c => c.CustomerID == id);
 156#endif
 157            cust = GetCustomerById(db, "ALFKI");
 158            Assert.AreEqual("ALFKI", cust.CustomerID);
 159
 160            cust = GetCustomerById(db, "BLAUS");
 161            Assert.AreEqual("BLAUS", cust.CustomerID);
 162
 163            cust = GetCustomerById(db, "DNE");
 164            Assert.IsNull(cust);
 165
 166            cust = GetCustomerById(db, "ALFKI");
 167            Assert.AreEqual("ALFKI", cust.CustomerID);
 168
 169            cust = GetCustomerById(db, "BLAUS");
 170            Assert.AreEqual("BLAUS", cust.CustomerID);
 171        }
 172
 173
 174        private static Customer GetCustomerById(Northwind db, string id)
 175        {
 176            return db.Customers.SingleOrDefault(c => c.CustomerID == id);
 177        }
 178
 179
 180        [Test]
 181        public void A6_ConnectionOpenTest()
 182        {
 183            Northwind db = CreateDB(System.Data.ConnectionState.Open);
 184            Product p1 = db.Products.Single(p => p.ProductID == 1);
 185            Assert.IsTrue(p1.ProductID == 1);
 186        }
 187
 188        [Test]
 189        public void A7_ConnectionClosedTest()
 190        {
 191            Northwind db = CreateDB(System.Data.ConnectionState.Closed);
 192            Product p1 = db.Products.Single(p => p.ProductID == 1);
 193            Assert.IsTrue(p1.ProductID == 1);
 194        }
 195
 196        [Test]
 197        public void A8_SelectSingleOrDefault_QueryCacheDisabled()
 198        {
 199            Northwind db = CreateDB();
 200#if !MONO_STRICT
 201            db.QueryCacheEnabled = true;
 202#endif
 203
 204            // Query for a specific customer
 205            var cust = db.Customers.SingleOrDefault(c => c.CompanyName == "Around the Horn");
 206            Assert.IsNotNull(cust, "Expected one customer 'Around the Horn'.");
 207
 208            cust = GetCustomerById(db, "ALFKI");
 209            Assert.AreEqual("ALFKI", cust.CustomerID);
 210
 211            cust = GetCustomerById(db, "BLAUS");
 212            Assert.AreEqual("BLAUS", cust.CustomerID);
 213
 214            cust = GetCustomerById(db, "DNE");
 215            Assert.IsNull(cust);
 216
 217            cust = GetCustomerById(db, "ALFKI");
 218            Assert.AreEqual("ALFKI", cust.CustomerID);
 219
 220            cust = GetCustomerById(db, "BLAUS");
 221            Assert.AreEqual("BLAUS", cust.CustomerID);
 222        }
 223
 224        #endregion
 225
 226        //TODO: group B, which checks AllTypes
 227
 228        #region Tests 'C' do plain select - no aggregation
 229        [Test]
 230        public void C1_SelectProducts()
 231        {
 232            Northwind db = CreateDB();
 233
 234            var q = from p in db.Products select p;
 235            List<Product> products = q.ToList();
 236            int productCount = products.Count;
 237            Assert.Greater(productCount, 0, "Expected some products, got none");
 238        }
 239
 240#if !DEBUG && SQLITE
 241        [Explicit]
 242#endif
 243        [Test]
 244        public void C2_SelectPenId()
 245        {
 246            Northwind db = CreateDB();
 247
 248            var q = from p in db.Products where p.ProductName == "Chai" select p.ProductID;
 249            var productIDs = q.ToList();
 250            int productCount = productIDs.Count;
 251            Assert.AreEqual(productCount, 1, "Expected one pen, got count=" + productCount);
 252        }
 253
 254        [Test]
 255        public void C2b_SelectPenId()
 256        {
 257            Northwind db = CreateDB();
 258
 259            var pen = "Chai";
 260            var q = from p in db.Products where p.ProductName == pen select p.ProductID;
 261            var productIDs = q.ToList();
 262            int productCount = productIDs.Count;
 263            Assert.AreEqual(productCount, 1, "Expected one pen, got count=" + productCount);
 264        }
 265
 266        [Test]
 267        public void C3_SelectPenIdName()
 268        {
 269            Northwind db = CreateDB();
 270
 271            var q = from p in db.Products
 272                    where p.ProductName == "Chai"
 273                    select new { ProductId = p.ProductID, Name = p.ProductName };
 274            int count = 0;
 275            //string penName;
 276            foreach (var v in q)
 277            {
 278                Assert.AreEqual(v.Name, "Chai", "Expected ProductName='Chai'");
 279                count++;
 280            }
 281            Assert.AreEqual(count, 1, "Expected one pen, got count=" + count);
 282        }
 283
 284#if !DEBUG && POSTGRES
 285        [Explicit]
 286#endif
 287        [Test]
 288        public void C4_CountWithOrderBy()
 289        {
 290            Northwind db = CreateDB();
 291            var q = (from p in db.Products
 292                     orderby p.ProductID
 293                     select p).Count();
 294            Assert.IsTrue(q > 0);
 295        }
 296
 297        [Test]
 298        public void C5_ConstantProperty()
 299        {
 300            Northwind db = CreateDB();
 301            var res = from o in db.Orders
 302                      select new { test = 1 };
 303            var list = res.ToList();
 304            Assert.AreEqual(db.Orders.Count(), list.Count);
 305        }
 306
 307
 308        [Test]
 309        public void C6_NullParentEmplyee()
 310        {
 311            //this should generate a LEFT JOIN statement, but currently does not.
 312            Northwind db = CreateDB();
 313
 314            var query = from e in db.Employees
 315                        select new
 316                        {
 317                            Name = e.FirstName,
 318                            ReportsTo = e.ReportsToEmployee.FirstName
 319                        };
 320
 321            var list = query.ToList();
 322            // PC patch: I get 4 results...
 323            Assert.IsTrue(list.Count >= 3);
 324        }
 325
 326
 327
 328        [Test]
 329        public void C7_CaseInsensitiveSubstringSearch()
 330        {
 331            Northwind db = CreateDB();
 332
 333            string search = "HERKKU";
 334            var query = db.Customers.Where(d => d.CompanyName.ToUpper()
 335              .Contains(search));
 336
 337            var list = query.ToList();
 338            Assert.AreEqual(1, list.Count);
 339        }
 340
 341
 342        /// <summary>
 343        /// from http://www.agilior.pt/blogs/pedro.rainho/archive/2008/04/11/4271.aspx
 344        /// </summary>
 345#if !DEBUG && (SQLITE || POSTGRES || (MSSQL && !L2SQL))
 346        [Explicit]
 347#endif
 348        [Test(Description = "Using LIKE operator from linq query")]
 349        public void C7B_LikeOperator()
 350        {
 351            Northwind db = CreateDB();
 352
 353            //this used to read "Like(HU%F)" but I don't think we have that company.
 354
 355            var query = (from c in db.Customers
 356                         where System.Data.Linq.SqlClient.SqlMethods.Like(c.CompanyName, "Alfre%")
 357                         select c).ToList();
 358            var list = query.ToList();
 359            Assert.AreEqual(1, list.Count);
 360        }
 361
 362        [Test]
 363        public void C8_SelectPenByLocalVariable()
 364        {
 365            Northwind db = CreateDB();
 366            string pen = "Chai";
 367
 368            var q = from p in db.Products
 369                    where (p.ProductName == pen)
 370                    select p;
 371            var productIDs = q.ToList();
 372            int productCount = productIDs.Count;
 373            Assert.AreEqual(productCount, 1, "Expected one pen, got count=" + productCount);
 374        }
 375
 376        [Test]
 377        public void C9_OrderByLeftJoin()
 378        {
 379            Northwind db = CreateDB();
 380            var q = from p in db.Orders
 381                    orderby p.Customer.City
 382                    select p;
 383            
 384            int count = q.ToList().Count();
 385            int ordcount = db.Orders.Count();
 386            Assert.AreEqual(ordcount, count);
 387        }
 388
 389        [Test]
 390        public void C10_ConstantPredicate()
 391        {
 392            Northwind db = CreateDB();
 393            var q = from p in db.Customers
 394                    where true
 395                    select p;
 396
 397            int count = q.ToList().Count;
 398            Assert.AreEqual(count, db.Customers.Count());
 399        }
 400
 401        [Test]
 402        public void C10b_ConstantPredicate()
 403        {
 404            Northwind db = CreateDB();
 405            var q = from p in db.Customers
 406                    where false
 407                    select p;
 408
 409            int count = q.Count();
 410            Assert.AreEqual(count, 0);
 411        }
 412
 413        [Test]
 414        public void C10c_ConstantPredicate()
 415        {
 416            Northwind db = CreateDB();
 417            var q = from p in db.Customers
 418                    where (p.Address.StartsWith("A") && false)
 419                    select p;
 420
 421            int count = q.Count();
 422            Assert.AreEqual(count, 0);
 423        }
 424
 425        [Test]
 426        public void C10d_ConstantPredicate()
 427        {
 428            Northwind db = CreateDB();
 429            var q = from p in db.Customers
 430                    where (p.Address.StartsWith("A") || true)
 431                    select p;
 432
 433            int count = q.Count();
 434            Assert.AreEqual(count, db.Customers.Count());
 435        }
 436
 437        [Test]
 438        public void C10e_ConstantPredicate()
 439        {
 440            Northwind db = CreateDB();
 441            var q = from p in db.Customers
 442                    where (p.Address.StartsWith("A") || false)
 443                    select p;
 444
 445            int count = q.Count();
 446            Assert.Less(count, db.Customers.Count());
 447        }
 448
 449        [Test]
 450        public void C10f_ConstantPredicate()
 451        {
 452            Northwind db = CreateDB();
 453            var q = from p in db.Customers
 454                    where (p.Address.StartsWith("A") && true)
 455                    select p;
 456
 457            int count = q.Count();
 458            Assert.Less(count, db.Customers.Count());
 459        }
 460
 461        [Test]
 462        public void C11_SelectProductsDiscontinued()
 463        {
 464            Northwind db = CreateDB();
 465            var q = from p in db.Products 
 466#if INGRES
 467                    where p.Discontinued == "Y"
 468#else
 469                    where p.Discontinued == true 
 470#endif
 471                    select p.ProductID;
 472
 473            var productIDs = q.ToList();
 474            int productCount = productIDs.Count;
 475            Assert.AreEqual(productCount, 8, "Expected eight products discontinued, got count=" + productCount);
 476        }
 477
 478        [Explicit]
 479        [Test]
 480        public void C12_SelectEmployee_MultiJoinWithWhere()
 481        {
 482            Northwind db = CreateDB();
 483            var q = from t in db.Territories
 484                      join l in db.EmployeeTerritories on t.TerritoryID equals l.TerritoryID
 485                      join e in db.Employees on l.EmployeeID equals e.EmployeeID
 486                      where t.RegionID > 3
 487                      select e; 
 488            /* Note that written this way it work, but it's not always possible.
 489            var q = from t in db.Territories.Where(t => t.RegionID > 3)
 490                    join l in db.EmployeeTerritories on t.TerritoryID equals l.TerritoryID
 491                    join e in db.Employees on l.EmployeeID equals e.EmployeeID
 492                    select e; 
 493             */
 494            var employeeCount = q.Count();
 495            Assert.AreEqual(4, employeeCount, "Expected for employees, got count=" + employeeCount);
 496        }
 497
 498        [Test]
 499        [ExpectedException(ExceptionType=typeof(InvalidOperationException), ExpectedMessage="Data context options cannot be modified after results have been returned from a query.")]
 500        public void C13_Changing_ObjectTrackingEnabled2False()
 501        {
 502            Northwind db = CreateDB();
 503            var q = from t in db.Territories
 504                    select t;
 505            var territoryCount = q.FirstOrDefault();
 506            db.ObjectTrackingEnabled = false;
 507        }
 508
 509        [Test]
 510        [ExpectedException(ExceptionType = typeof(InvalidOperationException), ExpectedMessage = "Data context options cannot be modified after results have been returned from a query.")]
 511        public void C14_Changing_DeferredLoadingEnabled2False()
 512        {
 513            Northwind db = CreateDB();
 514            var q = from t in db.Territories
 515                    select t;
 516            var territoryCount = q.FirstOrDefault();
 517            db.DeferredLoadingEnabled = false;
 518        }
 519
 520        [Test]
 521        [ExpectedException(ExceptionType = typeof(InvalidOperationException), ExpectedMessage = "Object tracking is not enabled for the current data context instance.")]
 522        public void C15_SubmitChanges_DeferredLoadingEnabled_False()
 523        {
 524            Northwind db = CreateDB();
 525            db.ObjectTrackingEnabled = false;
 526            var q = from t in db.Territories
 527                    select t;
 528            var territoryCount = q.Count();
 529            db.SubmitChanges();
 530        }
 531
 532        [Test]
 533        public void C16_GettingProperty_DeferredLoadingEnabled2False()
 534        {
 535            Northwind db = CreateDB();
 536            db.DeferredLoadingEnabled = false;
 537            var q = from t in db.Territories
 538                    select t;
 539            Territory territory = q.FirstOrDefault();
 540            Assert.IsNotNull(territory);
 541            Assert.IsNull(territory.Region);
 542        }
 543
 544        [Test]
 545        public void C17_GettingProperty_ObjectTrackingEnabled2False()
 546        {
 547            Northwind db = CreateDB();
 548            db.ObjectTrackingEnabled = false;
 549            var q = from t in db.Territories
 550                    select t;
 551            Territory territory = q.FirstOrDefault();
 552            Assert.IsNotNull(territory);
 553            Assert.IsNull(territory.Region);
 554        }
 555
 556        [Test]
 557        public void C18_GettingProperty_LazyLoaded()
 558        {
 559            Northwind db = CreateDB();
 560            var q = from t in db.Territories
 561                    select t;
 562            Territory territory = q.FirstOrDefault();
 563            Assert.IsNotNull(territory);
 564            Assert.IsNotNull(territory.Region);
 565        }
 566
 567        [Test]
 568        public void C19_SelectEmployee_Fluent()
 569        {
 570            Northwind db = CreateDB();
 571            var q = db.GetTable<Territory>()
 572                        .Join(db.GetTable<EmployeeTerritory>(), t => t.TerritoryID, l => l.TerritoryID, (t, l) => l)
 573                        .Join(db.GetTable<Employee>().Where(e => e.EmployeeID > 0), l => l.EmployeeID, e => e.EmployeeID, (l, e) => e);
 574            var employeeCount = q.Count();
 575            Assert.Greater(employeeCount, 0, "Expected any employees, got count=" + employeeCount);
 576        }
 577
 578        /// <summary>
 579        /// Test the use of DbLinq as a QueryObject
 580        /// http://www.martinfowler.com/eaaCatalog/queryObject.html
 581        /// </summary>
 582        [Test]
 583        public void C20_SelectEmployee_DbLinqAsQueryObject()
 584        {
 585            Northwind db = CreateDB();
 586            IQueryable<Employee> allEmployees = db.GetTable<Employee>();
 587
 588            allEmployees = filterByNameOrSurnameContains(db, allEmployees, "an");
 589
 590            allEmployees = filterByTerritoryName(db, allEmployees, "Neward");
 591
 592            Assert.AreEqual(1, allEmployees.Count());
 593        }
 594
 595        [Test]
 596        public void C21_SelectEmployee_DbLinqAsQueryObjectWithOrderCount()
 597        {
 598            Northwind db = CreateDB();
 599            IQueryable<Employee> allEmployees = db.GetTable<Employee>();
 600
 601            allEmployees = filterByOrderCountGreaterThan(db, allEmployees, 50);
 602            allEmployees = filterByNameOrSurnameContains(db, allEmployees, "an");
 603
 604            allEmployees = filterByTerritoryNames(db, allEmployees, "Neward", "Boston", "Wilton");
 605
 606            int employeesCount = allEmployees.ToList().Count;
 607
 608            Assert.AreEqual(employeesCount, allEmployees.Count());
 609        }
 610
 611
 612        private IQueryable<Employee> filterByOrderCountGreaterThan(Northwind db, IQueryable<Employee> allEmployees, int minimumOrderNumber)
 613        {
 614            return from e in allEmployees.Where(e => e.Orders.Count > minimumOrderNumber) select e;
 615        }
 616
 617        private IQueryable<Employee> filterByNameOrSurnameContains(Northwind db, IQueryable<Employee> allEmployees, string namePart)
 618        {
 619            return from e in allEmployees.Where(e => e.FirstName.Contains(namePart) || e.LastName.Contains(namePart)) select e;
 620        }
 621
 622        private IQueryable<Employee> filterByTerritoryName(Northwind db, IQueryable<Employee> allEmployees, string territoryName)
 623        {
 624            IQueryable<Territory> territoryRequired = db.GetTable<Territory>().Where(t => t.TerritoryDescription == territoryName);
 625            var q = territoryRequired
 626                        .Join(db.GetTable<EmployeeTerritory>(), t => t.TerritoryID, l => l.TerritoryID, (t, l) => l)
 627                        .Join(allEmployees, l => l.EmployeeID, e => e.EmployeeID, (l, e) => e);
 628            return q;
 629        }
 630
 631        private IQueryable<Employee> filterByTerritoryNames(Northwind db, IQueryable<Employee> allEmployees, params string[] territoryNames)
 632        {
 633            IQueryable<Territory> territoryRequired = db.GetTable<Territory>().Where(t => territoryNames.Contains(t.TerritoryDescription));
 634            var q = territoryRequired
 635                        .Join(db.GetTable<EmployeeTerritory>(), t => t.TerritoryID, l => l.TerritoryID, (t, l) => l)
 636                        .Join(allEmployees, l => l.EmployeeID, e => e.EmployeeID, (l, e) => e);
 637            return q;
 638        }
 639
 640        [Test]
 641        public void C22_SelectEmployee_GetCommandTextWithNoFilter()
 642        {
 643            Northwind db = CreateDB();
 644            IQueryable<Employee> allEmployees = db.GetTable<Employee>();
 645            var commandText = db.GetCommand(allEmployees).CommandText;
 646            Assert.IsNotNull(commandText);
 647        }
 648
 649        [Test]
 650        public void C23_SelectEmployees()
 651        {
 652            Northwind db = CreateDB();
 653            var allEmployees = db.GetTable<Employee>();
 654            int count = 0;
 655            foreach (var emp in allEmployees)
 656            {
 657                ++count;
 658            }
 659            Assert.AreEqual(9, count);
 660        }
 661
 662#if !DEBUG && (MSSQL && !L2SQL)
 663        [Explicit]
 664#endif
 665        [Test]
 666        public void C24_SelectEmployee_DbLinqAsQueryObjectWithExceptAndImage()
 667        {
 668            // This fail becouse Employee contains a ndata, ndata is not comparable
 669            // and EXCEPT make a distinct on DATA
 670            Northwind db = CreateDB();
 671            IQueryable<Employee> allEmployees = db.GetTable<Employee>();
 672
 673            var toExclude = filterByOrderCountGreaterThan(db, allEmployees, 50);
 674            allEmployees = filterByNameOrSurnameContains(db, allEmployees, "a").Except(toExclude);
 675
 676            string commandText = db.GetCommand(allEmployees).CommandText;
 677
 678            int employeesCount = allEmployees.ToList().Count;
 679
 680            Assert.AreEqual(employeesCount, allEmployees.Count());
 681        }
 682
 683        [Test]
 684        public void C25_SelectViaInterface()
 685        {
 686            var db = CreateDB();
 687            var c = MatchAddress(db.Customers, "ignoreme").FirstOrDefault();
 688            Assert.IsNotNull(c);
 689            var e = MatchAddress(db.Employees, "ignoreme").FirstOrDefault();
 690            Assert.IsNotNull(e);
 691        }
 692
 693        private static IEnumerable<T> MatchAddress<T>(IQueryable<T> query, string searchValue)
 694            where T : IHasAddress
 695        {
 696            var lookups = query.OrderByDescending(v => v.Address.Length);
 697            return lookups;
 698        }
 699
 700#if !DEBUG && POSTGRES
 701        [Explicit]
 702#endif
 703        [Test]
 704        public void C26_SelectWithNestedMethodCall()
 705        {
 706            var db = CreateDB();
 707            var s = "param";
 708            var q = from e in db.Employees select new
 709            {
 710                BackName    = e.LastName + ", " + e.FirstName,
 711                StaticName  = GetStaticName(e),
 712                InstanceName= GetInstanceName(e, s, "constant"),
 713                Territories = e.EmployeeTerritories.ToList(),
 714            };
 715            var actual  = q.ToList();
 716            var expected = new[]{
 717                new {
 718                    BackName        = "Davolio, Nancy",
 719                    StaticName      = "Nancy Davolio [Hired: 1992-05-01]",
 720                    InstanceName    = "Nancy Davolio [Home Phone: (206) 555-9857]",
 721                    TerritoryCount  = 2,
 722                },
 723                new {
 724                    BackName        = "Fuller, Andrew",
 725                    StaticName      = "Andrew Fuller [Hired: 1992-08-14]",
 726                    InstanceName    = "Andrew Fuller [Home Phone: (206) 555-9482]",
 727                    TerritoryCount  = 7,
 728                },
 729                new {
 730                    BackName        = "Leverling, Janet",
 731                    StaticName      = "Janet Leverling [Hired: 1992-04-01]",
 732                    InstanceName    = "Janet Leverling [Home Phone: (206) 555-3412]",
 733                    TerritoryCount  = 4,
 734                },
 735                new {
 736                    BackName        = "Peacock, Margaret",
 737                    StaticName      = "Margaret Peacock [Hired: 1993-05-03]",
 738                    InstanceName    = "Margaret Peacock [Home Phone: (206) 555-8122]",
 739                    TerritoryCount  = 3,
 740                },
 741                new {
 742                    BackName        = "Buchanan, Steven",
 743                    StaticName      = "Steven Buchanan [Hired: 1993-10-17]",
 744                    InstanceName    = "Steven Buchanan [Home Phone: (71) 555-4848]",
 745                    TerritoryCount  = 7,
 746                },
 747                new {
 748                    BackName        = "Suyama, Michael",
 749                    StaticName      = "Michael Suyama [Hired: 1993-10-17]",
 750                    InstanceName    = "Michael Suyama [Home Phone: (71) 555-7773]",
 751                    TerritoryCount  = 5,
 752                },
 753                new {
 754                    BackName        = "King, Robert",
 755                    StaticName      = "Robert King [Hired: 1994-01-02]",
 756                    InstanceName    = "Robert King [Home Phone: (71) 555-5598]",
 757                    TerritoryCount  = 10,
 758                },
 759                new {
 760                    BackName        = "Callahan, Laura",
 761                    StaticName      = "Laura Callahan [Hired: 1994-03-05]",
 762                    InstanceName    = "Laura Callahan [Home Phone: (206) 555-1189]",
 763                    TerritoryCount  = 4,
 764                },
 765                new {
 766                    BackName        = "Dodsworth, Anne",
 767                    StaticName      = "Anne Dodsworth [Hired: 1994-11-15]",
 768                    InstanceName    = "Anne Dodsworth [Home Phone: (71) 555-4444]",
 769                    TerritoryCount  = 7,
 770                },
 771            };
 772            Assert.AreEqual(expected.Length, actual.Count);
 773            for (int i = 0; i < expected.Length; ++i)
 774            {
 775                Assert.AreEqual(expected[i].BackName, actual[i].BackName);
 776                Assert.AreEqual(expected[i].StaticName, actual[i].StaticName);
 777                Assert.AreEqual(expected[i].InstanceName, actual[i].InstanceName);
 778                Assert.AreEqual(expected[i].TerritoryCount, actual[i].Territories.Count);
 779            }
 780        }
 781
 782        static string GetStaticName(Employee e)
 783        {
 784            return e.FirstName + " " + e.LastName + " [Hired: " + 
 785                (e.HireDate.HasValue ? e.HireDate.Value.ToString("yyyy-MM-dd") : "") + "]";
 786        }
 787
 788        string GetInstanceName(Employee e, string a, string b)
 789        {
 790            return e.FirstName + " " + e.LastName + " [Home Phone: " + e.HomePhone.ToString() + "]";
 791        }
 792
 793        [Test]
 794        public void C27_SelectEntitySet()
 795        {
 796            // Debugger.Break();
 797            var db = CreateDB();
 798            var q = from e in db.Employees
 799                    orderby e.EmployeeID
 800                    select new
 801                    {
 802                        e.Orders
 803                    };
 804            var expectedOrderCounts = new[]{
 805                123,    // Nancy Davolio
 806                 96,    // Andrew Fuller
 807                127,    // Janet Leverling
 808                156,    // Margaret Peacock
 809                 42,    // Steven Buchanan
 810                 67,    // Michael Suyama
 811                 72,    // Robert King
 812                104,    // Laura Callahan
 813                 43,    // Anne Dodsworth
 814            };
 815            int c = 0;
 816            foreach (var e in q)
 817            {
 818                Assert.AreEqual(expectedOrderCounts[c], e.Orders.Count);
 819                ++c;
 820            }
 821            Assert.AreEqual(expectedOrderCounts.Length, c);
 822        }
 823
 824        [Test]
 825        public void C28_SelectEntityRef()
 826        {
 827            var db = CreateDB();
 828            var emp = db.Employees.Single(e => e.EmployeeID == 1);
 829            Assert.IsNotNull(emp.ReportsToEmployee);
 830            Assert.AreEqual(emp.ReportsTo.Value, emp.ReportsToEmployee.EmployeeID);
 831        }
 832        #endregion
 833
 834        #region region D - select first or last - calls IQueryable.Execute instead of GetEnumerator
 835        [Test]
 836        public void D01_SelectFirstPenID()
 837        {
 838            Northwind db = CreateDB();
 839
 840            var q = from p in db.Products where p.ProductName == "Chai" select p.ProductID;
 841            var productID = q.First();
 842            Assert.Greater(productID, 0, "Expected penID>0, got " + productID);
 843        }
 844
 845
 846        /// <summary>
 847        /// Reported by pwy.mail in http://code.google.com/p/dblinq2007/issues/detail?id=67
 848        /// </summary>
 849        [Test]
 850        public void D01b_SelectFirstOrDefaultCustomer()
 851        {
 852            Northwind db = CreateDB();
 853            var q =
 854              from c in db.Customers
 855              select c;
 856
 857            Customer customer = q.FirstOrDefault();
 858            Assert.IsNotNull(customer.CustomerID);
 859        }
 860
 861
 862        [Test]
 863        public void D02_SelectFirstPen()
 864        {
 865            Northwind db = CreateDB();
 866
 867            var q = from p in db.Products where p.ProductName == "Chai" select p;
 868            Product pen = q.First();
 869            Assert.IsNotNull(pen, "Expected non-null Product");
 870        }
 871
 872#if !DEBUG && MSSQL
 873        // L2SQL: System.NotSupportedException : The query operator 'Last' is not supported.
 874        [Explicit]
 875#endif
 876        [Test]
 877        public void D03_SelectLastPenID()
 878        {
 879            Northwind db = CreateDB();
 880
 881            var q = from p in db.Products where p.ProductName == "Chai" select p.ProductID;
 882            var productID = q.Last();
 883            Assert.Greater(productID, 0, "Expected penID>0, got " + productID);
 884        }
 885
 886#if !DEBUG && (POSTGRES || (MSSQL && !L2SQL))
 887        [Explicit]
 888#endif
 889        [Test]
 890        public void D04_SelectProducts_OrderByName()
 891        {
 892            Northwind db = CreateDB();
 893
 894            var q = from p in db.Products orderby p.ProductName select p;
 895            string prevProductName = null;
 896            foreach (Product p in q)
 897            {
 898                if (prevProductName == p.ProductName && p.ProductName.StartsWith("temp_"))
 899                    continue; //skip temp rows
 900
 901                if (prevProductName != null)
 902                {
 903                    //int compareNames = prevProductName.CompareTo(p.ProductName);
 904                    int compareNames = string.Compare(prevProductName, p.ProductName, stringComparisonType);
 905                    Assert.Less(compareNames, 0, "When ordering by names, expected " + prevProductName + " to come after " + p.ProductName);
 906                }
 907                prevProductName = p.ProductName;
 908            }
 909            //Assert.Greater(productID,0,"Expected penID>0, got "+productID);
 910        }
 911
 912        [Test]
 913        public void D05_SelectOrdersForProduct()
 914        {
 915            Northwind db = CreateDB();
 916            //var q = from p in db.Products where "Chai"==p.ProductName select p.Order;
 917            //List<Order> penOrders = q.ToList();
 918            //Assert.Greater(penOrders.Count,0,"Expected some orders for product 'Chai'");
 919
 920            var q =
 921                from o in db.Orders
 922                where o.Customer.City == "London"
 923                select new { c = o.Customer, o };
 924
 925            var list1 = q.ToList();
 926            foreach (var co in list1)
 927            {
 928                Assert.IsNotNull(co.c, "Expected non-null customer");
 929                Assert.IsNotNull(co.c.City, "Expected non-null customer city");
 930                Assert.IsNotNull(co.o, "Expected non-null order");
 931            }
 932            Assert.Greater(list1.Count, 0, "Expected some orders for London customers");
 933        }
 934
 935        [Test]
 936        public void D06_OrdersFromLondon()
 937        {
 938            Northwind db = CreateDB();
 939            var q =
 940                from o in db.Orders
 941                where o.Customer.City == "London"
 942                select new { c = o.Customer, o };
 943
 944            var list1 = q.ToList();
 945            foreach (var co in list1)
 946            {
 947                Assert.IsNotNull(co.c, "Expected non-null customer");
 948                Assert.IsNotNull(co.o, "Expected non-null order");
 949            }
 950            Assert.Greater(list1.Count, 0, "Expected some orders for London customers");
 951        }
 952
 953        [Test]
 954        public void D07_OrdersFromLondon_Alt()
 955        {
 956            //this is a "SelectMany" query:
 957            Northwind db = CreateDB();
 958
 959            var q =
 960                from c in db.Customers
 961                from o in c.Orders
 962                where c.City == "London"
 963                select new { c, o };
 964
 965            Assert.Greater(q.ToList().Count, 0, "Expected some orders for London customers");
 966        }
 967
 968        [Test]
 969        public void D08_Products_Take5()
 970        {
 971            Northwind db = CreateDB();
 972            var q = (from p in db.Products select p).Take(5);
 973            List<Product> prods = q.ToList();
 974            Assert.AreEqual(5, prods.Count, "Expected five products");
 975        }
 976
 977        [Test]
 978        public void D09_Products_LetterP_Take5()
 979        {
 980            Northwind db = CreateDB();
 981
 982            //var q = (from p in db.Products where p.ProductName.Contains("p") select p).Take(5);
 983            var q = db.Products.Where(p => p.ProductName.Contains("p")).Take(5);
 984            List<Product> prods = q.ToList();
 985#if POSTGRES || INGRES
 986            int expectedCount = 0; //Only 'Toilet Paper'
 987#else
 988            int expectedCount = 2; //Oracle, Mysql: 'Toilet Paper' and 'iPod'
 989#endif
 990            Assert.Greater(prods.Count, expectedCount, "Expected couple of products with letter 'p'");
 991        }
 992
 993        [Test]
 994        public void D10_Products_LetterP_Desc()
 995        {
 996            Northwind db = CreateDB();
 997
 998            var q = (from p in db.Products
 999                     where p.ProductName.Contains("P")
1000                     orderby p.ProductID descending
1001                     select p
1002            ).Take(5);
1003            //var q = db.Products.Where( p=>p.ProductName.Contains("p")).Take(5);
1004            List<Product> prods = q.ToList();
1005            Assert.Greater(prods.Count, 2, "Expected couple of products with letter 'p'");
1006
1007            var prodID0 = prods[0].ProductID;
1008            var prodID1 = prods[1].ProductID;
1009            Assert.Greater(prodID0, prodID1, "Sorting is broken");
1010        }
1011
1012        [Test]
1013        public void D11_Products_DoubleWhere()
1014        {
1015            Northwind db = CreateDB();
1016            var q1 = db.Products.Where(p => p.ProductID > 1).Where(q => q.ProductID < 10);
1017            int count1 = q1.Count();
1018        }
1019
1020
1021#if !DEBUG && (SQLITE || POSTGRES || MSSQL)
1022        // L2SQL: System.InvalidOperationException : The type 'Test_NUnit_MsSql_Strict.ReadTest+Northwind1+CustomerDerivedClass' is not mapped as a Table.
1023        [Explicit]
1024#endif
1025        [Test]
1026        public void D12_SelectDerivedClass()
1027        {
1028            Northwind dbo = CreateDB();
1029            Northwind1 db = new Northwind1(dbo.Connection);
1030
1031            var derivedCustomer = (from c in db.ChildCustomers
1032                                   where c.City == "London"
1033                                   select c).First();
1034            Assert.IsTrue(derivedCustomer.City == "London");
1035        }
1036
1037        public class Northwind1 : Northwind
1038        {
1039            public Northwind1(System.Data.IDbConnection connection)
1040                : base(connection)
1041            { }
1042
1043            public class CustomerDerivedClass : Customer { }
1044            public class CustomerDerivedClass2 : CustomerDerivedClass { }
1045
1046            public DataLinq.Table<CustomerDerivedClass> ChildCustomers
1047            {
1048                get { return base.GetTable<CustomerDerivedClass>(); }
1049            }
1050        }
1051
1052
1053        [Test(Description = "Calls ExecuteQuery<> to store result into object type property")]
1054        // note: for PostgreSQL requires database with lowercase names, NorthwindReqular.SQL
1055        public void D13_ExecuteQueryObjectProperty()
1056        {
1057            Northwind db = CreateDB();
1058
1059            var res = db.ExecuteQuery<Chai>(@"SELECT [ProductID] AS ChaiId FROM [Products] WHERE
1060              [ProductName] ='Chai'").Single();
1061            Assert.AreEqual(1, res.ChaiId);
1062        }
1063
1064        class Chai
1065        {
1066            internal int ChaiId;
1067        }
1068
1069        [Test]
1070        public void D14_ProjectedProductList()
1071        {
1072            Northwind db = CreateDB();
1073
1074            var query = from pr in db.Products
1075                        select new
1076                        {
1077                            pr.ProductID,
1078                            pr.ProductName,
1079                            pr.Supplier,         // exception!
1080                            pr.UnitPrice,        // exception!
1081                            pr.UnitsInStock,
1082                            pr.UnitsOnOrder
1083                        };
1084            //WARNING - as of 2008Apr, we return Suppliers without blowing up, but they need to be live
1085            var list = query.ToList();
1086            Assert.IsTrue(list.Count > 0);
1087            foreach (var item in list)
1088            {
1089                Assert.IsTrue(item.Supplier != null);
1090            }
1091        }
1092
1093#if !DEBUG && (SQLITE || POSTGRES || MSSQL)
1094        // L2SQL: System.InvalidOperationException : The type 'Test_NUnit_MsSql_Strict.ReadTest+NorthwindDupl+CustomerDerivedClass' is not mapped as a Table.
1095        [Explicit]
1096#endif
1097        [Test]
1098        public void D15_DuplicateProperty()
1099        {
1100            Northwind dbo = CreateDB();
1101            NorthwindDupl db = new NorthwindDupl(dbo.Connection);
1102            var derivedCustomer = (from c in db.ChildCustomers
1103                                   where c.City == "London"
1104                                   select c).First();
1105            Assert.IsTrue(derivedCustomer.City == "London");
1106        }
1107
1108        public class NorthwindDupl : Northwind
1109        {
1110            public NorthwindDupl(System.Data.IDbConnection connection)
1111                : base(connection)
1112            { }
1113
1114            public class CustomerDerivedClass : Customer
1115            {
1116                private string city;
1117                [Column(Storage = "city", Name = "city")]
1118                public new string City
1119                {
1120                    get
1121                    {
1122                        return city;
1123                    }
1124                    set
1125                    {
1126                        if (value != city)
1127                        {
1128                            city = value;
1129                        }
1130                    }
1131                }
1132            }
1133
1134            public DataLinq.Table<CustomerDerivedClass> ChildCustomers
1135            {
1136                get { return base.GetTable<CustomerDerivedClass>(); }
1137            }
1138        }
1139
1140        /// <summary>
1141        /// DbLinq must use field and should not look to setter.
1142        /// </summary>
1143        // PC: is this specified somewhere?
1144        [Test]
1145        public void D16_CustomerWithoutSetter()
1146        {
1147            Assert.Ignore("See if this is specified");
1148            Northwind dbo = CreateDB();
1149            NorthwindAbstractBaseClass db = new NorthwindAbstractBaseClass(dbo.Connection);
1150            var Customer = (from c in db.ChildCustomers
1151                            where c.City == "London"
1152                            select c).First();
1153            Assert.IsTrue(Customer.City == "London");
1154        }
1155
1156
1157        abstract class AbstractCustomer
1158        {
1159            public abstract string City { get; }
1160        }
1161
1162        class NorthwindAbstractBaseClass : Northwind
1163        {
1164            public NorthwindAbstractBaseClass(System.Data.IDbConnection connection)
1165                : base(connection) { }
1166
1167            [Table(Name = "customers")]
1168            public class Customer : AbstractCustomer
1169            {
1170                string city;
1171                [Column(Storage = "city", Name = "city")]
1172                public override string City
1173                {
1174                    get
1175                    {
1176                        return city;
1177                    }
1178                }
1179            }
1180
1181            [Table(Name = "customers")]
1182            public class Customer2 : Customer { }
1183
1184            public DataLinq.Table<Customer2> ChildCustomers
1185            {
1186                get { return base.GetTable<Customer2>(); }
1187            }
1188        }
1189
1190
1191        #endregion
1192
1193        [Test]
1194        public void SqlInjectionAttack()
1195        {
1196            var db = CreateDB();
1197            var q = db.Customers.Where(c => c.ContactName == "'; DROP TABLE DoesNotExist; --");
1198            Assert.AreEqual(0, q.Count());
1199        }
1200              
1201#if POSTGRES || MSSQL
1202        [Test]
1203        public void Storage01()
1204        {
1205            var db = CreateDB();
1206            var q = db.NoStorageCategories.Where(c => c.CategoryID == 1);
1207            var r = q.First();
1208            Assert.AreEqual(1, q.Count());
1209            Assert.AreEqual(1, r.CategoryID);
1210            Assert.IsTrue(r.propertyInvoked_CategoryName);     
1211            Assert.IsFalse(r.propertyInvoked_Description);     
1212        }
1213#endif    
1214    }
1215}