PageRenderTime 47ms CodeModel.GetById 20ms RepoModel.GetById 0ms app.codeStats 0ms

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

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