PageRenderTime 49ms CodeModel.GetById 19ms RepoModel.GetById 0ms app.codeStats 0ms

/EntityFramework/Beta2/Entity Framework Query Samples/LinqToEntitiesSamples.cs

#
C# | 1196 lines | 952 code | 243 blank | 1 comment | 50 complexity | d81d3e1095368c979e95bb8f07d872cc MD5 | raw file
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Data;
  4. using System.Data.Metadata.Edm;
  5. using System.Data.Common;
  6. using System.Data.Objects;
  7. using System.Data.Linq;
  8. using System.Data.EntityClient;
  9. using System.Diagnostics;
  10. using System.Linq;
  11. using SampleSupport;
  12. using System.Xml.Linq;
  13. using System.Text;
  14. using System.IO;
  15. using System.Windows.Forms;
  16. using System.Data.SqlClient;
  17. using NorthwindEFModel;
  18. namespace QuerySamples
  19. {
  20. [Title("LINQ to Entities Samples")]
  21. [Prefix("LinqToEntities")]
  22. class LinqToEntitiesSamples : SampleHarness
  23. {
  24. NorthwindEFModel.NorthwindEntities northwindContext;
  25. public LinqToEntitiesSamples()
  26. {
  27. northwindContext = new NorthwindEFModel.NorthwindEntities();
  28. }
  29. #region Restriction Operators
  30. [Category("Restriction Operators")]
  31. [Title("Where - Simple 1")]
  32. [Description("This sample uses WHERE to find all customers whose contact title is Sales Representative.")]
  33. public void LinqToEntities1()
  34. {
  35. var query = from cust in northwindContext.Customers
  36. where cust.ContactTitle == "Sales Representative"
  37. select cust;
  38. ObjectDumper.Write(query);
  39. }
  40. [Category("Restriction Operators")]
  41. [Title("Where - Simple 2")]
  42. [Description("This sample uses WHERE to find all orders placed in 1994 or later.")]
  43. public void LinqToEntities2()
  44. {
  45. DateTime dt = new DateTime(1994, 1, 1);
  46. var query = from order in northwindContext.Orders
  47. where order.OrderDate > dt
  48. select order;
  49. ObjectDumper.Write(query);
  50. }
  51. [Category("Restriction Operators")]
  52. [Title("Where - Simple 3")]
  53. [Description("This sample uses WHERE to filter for Products that have stock below their reorder level and have a units on order of zero.")]
  54. public void LinqToEntities3()
  55. {
  56. var query = from p in northwindContext.Products
  57. where p.UnitsInStock < p.ReorderLevel && p.UnitsOnOrder == 0
  58. select p;
  59. ObjectDumper.Write(query);
  60. }
  61. [Category("Restriction Operators")]
  62. [Title("Where - Simple 4")]
  63. [Description("This sample uses WHERE to filter out Products that have a UnitPrice less than 10.")]
  64. public void LinqToEntities4()
  65. {
  66. var query = from p in northwindContext.Products
  67. where p.UnitPrice < 10
  68. select p;
  69. ObjectDumper.Write(query);
  70. }
  71. [Category("Restriction Operators")]
  72. [Title("Where - Complex Type")]
  73. [Description("This sample uses WHERE to find Employees in London.")]
  74. public void LinqToEntities4a()
  75. {
  76. var query = from e in northwindContext.Employees
  77. where e.Address.City == "London"
  78. select e;
  79. ObjectDumper.Write(query);
  80. }
  81. [Category("Restriction Operators")]
  82. [Title("Where - TypeOf")]
  83. [Description("This sample uses WHERE to get previous employees.")]
  84. public void LinqToEntities5()
  85. {
  86. var query = from e in northwindContext.Employees
  87. where e is NorthwindEFModel.PreviousEmployee
  88. select e;
  89. ObjectDumper.Write(query);
  90. }
  91. [Category("Restriction Operators")]
  92. [Title("Any")]
  93. [Description("This sample uses WHERE to get employees who handle the Boston territory.")]
  94. public void LinqToEntities6()
  95. {
  96. var query = from e in northwindContext.Employees
  97. where e.Territories.Any(t => t.TerritoryDescription == "Boston")
  98. select e;
  99. ObjectDumper.Write(query);
  100. }
  101. [Category("Restriction Operators")]
  102. [Title("Any - 2")]
  103. [Description("This sample uses any Customers who placed an order in 1997.")]
  104. public void LinqToEntities7()
  105. {
  106. var query = from c in northwindContext.Customers
  107. where c.Orders.Any(o => o.OrderDate.HasValue == true && o.OrderDate.Value.Year == 1997)
  108. select c;
  109. ObjectDumper.Write(query);
  110. }
  111. [Category("Restriction Operators")]
  112. [Title("Any - 3")]
  113. [Description("This sample uses ANY to check for any out-of-stock products.")]
  114. public void LinqToEntities8()
  115. {
  116. var query = northwindContext
  117. .Suppliers
  118. .Where(s => s.Products
  119. .Any(p => p.UnitsInStock == 0))
  120. .Select(s => s);
  121. ObjectDumper.Write(query);
  122. }
  123. [Category("Restriction Operators")]
  124. [Title("Any - Related Entities")]
  125. [Description("This sample uses WHERE and ANY to get orders containing a product with a unit on order.")]
  126. public void LinqToEntities9()
  127. {
  128. var query = from o in northwindContext.Orders
  129. where o.OrderDetails.Any(od => od.Product.UnitsOnOrder > 0 )
  130. select o;
  131. ObjectDumper.Write(query);
  132. }
  133. [Category("Restriction Operators")]
  134. [Title("Count - Related Entities Aggregate")]
  135. [Description("This sample uses COUNT to get Products sold to Customers in the same Country as the Products Suppliers, and where all the Products in the order were from the same Country.")]
  136. public void LinqToEntities10()
  137. {
  138. var query = from p in northwindContext.Products
  139. where p.OrderDetails.Count(od => od.Order.Customer.Address.Country == p.Supplier.Address.Country) > 2
  140. select p;
  141. ObjectDumper.Write(query);
  142. }
  143. #endregion
  144. #region Projection Operators
  145. [Category("Projection Operators")]
  146. [Title("Select - Simple 1")]
  147. [Description("This samples uses SELECT to get all Customers as Entity Objects.")]
  148. public void LinqToEntities11()
  149. {
  150. var query = from c in northwindContext.Customers
  151. select c;
  152. ObjectDumper.Write(query);
  153. }
  154. [Category("Projection Operators")]
  155. [Title("Select - Simple 2")]
  156. [Description("This samples uses SELECT to get all Customer Contact Names as Strings.")]
  157. public void LinqToEntities12()
  158. {
  159. var query = from c in northwindContext.Customers
  160. select c.ContactName;
  161. ObjectDumper.Write(query);
  162. }
  163. [Category("Projection Operators")]
  164. [Title("Select - Anonymous 1")]
  165. [Description("This samples uses SELECT to get all Customer Contact Names as an anonoymous type.")]
  166. public void LinqToEntities13()
  167. {
  168. var query = from c in northwindContext.Customers
  169. select new { c.ContactName };
  170. ObjectDumper.Write(query);
  171. }
  172. [Category("Projection Operators")]
  173. [Title("Select - Anonymous 2")]
  174. [Description("This sample uses SELECT to get all Orders as anonymous type")]
  175. public void LinqToEntities14()
  176. {
  177. var query = from o in northwindContext.Orders
  178. select new { o };
  179. ObjectDumper.Write(query);
  180. }
  181. [Category("Projection Operators")]
  182. [Title("Select - Anonymous 3")]
  183. [Description("This sample uses SELECT to get all Orders and associated Customers as anonymous type")]
  184. public void LinqToEntities15()
  185. {
  186. var query = from o in northwindContext.Orders
  187. select new { o, o.Customer };
  188. ObjectDumper.Write(query);
  189. }
  190. [Category("Projection Operators")]
  191. [Title("SelectMany - Simple 1")]
  192. [Description("This sample uses SELECTMANY to get all Orders for a Customer as a flat result")]
  193. public void LinqToEntities16()
  194. {
  195. var query = from c in northwindContext.Customers
  196. where c.CustomerID == "ALFKI"
  197. from o in c.Orders
  198. select o;
  199. ObjectDumper.Write(query);
  200. }
  201. [Category("Projection Operators")]
  202. [Title("SelectMany - Simple 2")]
  203. [Description("This sample uses SELECTMANY to get all Orders for a Customer as a flat result as a method query")]
  204. public void LinqToEntities17()
  205. {
  206. var query = northwindContext.Customers.Where(cust => cust.CustomerID == "ALFKI")
  207. .SelectMany(cust => cust.Orders);
  208. ObjectDumper.Write(query);
  209. }
  210. [Category("Projection Operators")]
  211. [Title("SelectMany - Simple 3")]
  212. [Description("This sample uses SELECTMANY to get all Orders for Customers in Denmark as a flat result")]
  213. public void LinqToEntities18()
  214. {
  215. var query = from c in northwindContext.Customers
  216. where c.Address.Country == "Denmark"
  217. from o in c.Orders
  218. select o;
  219. ObjectDumper.Write(query);
  220. }
  221. [Category("Projection Operators")]
  222. [Title("SelectMany - Simple 4")]
  223. [Description("This sample uses SELECTMANY to get all Orders for Customers in Denmark as a flat result as a method query")]
  224. public void LinqToEntities19()
  225. {
  226. var query = northwindContext.Customers.Where(cust => cust.Address.Country == "Denmark")
  227. .SelectMany(cust => cust.Orders);
  228. ObjectDumper.Write(query);
  229. }
  230. [Category("Projection Operators")]
  231. [Title("SelectMany - Predicate 1")]
  232. [Description("This sample uses SELECTMANY to get all Orders for Customers in Denmark as a flat result")]
  233. public void LinqToEntities20()
  234. {
  235. var query = from c in northwindContext.Customers
  236. where c.Address.Country == "Denmark"
  237. from o in c.Orders
  238. where o.Freight > 5
  239. select o;
  240. ObjectDumper.Write(query);
  241. }
  242. [Category("Projection Operators")]
  243. [Title("SelectMany - Predicate 2")]
  244. [Description("This sample uses SELECTMANY to get all Orders for Customers in Denmark as an anonymous type containing the Orders and Customer flat result")]
  245. public void LinqToEntities21()
  246. {
  247. var query = from c in northwindContext.Customers
  248. where c.Address.Country == "Denmark"
  249. from o in c.Orders
  250. where o.Freight > 5
  251. select new { c, o };
  252. ObjectDumper.Write(query);
  253. }
  254. [Category("Projection Operators")]
  255. [Title("SelectMany - Predicate 3")]
  256. [Description("This sample uses SELECTMANY to get all Orders for Customers in Denmark as a flat result using LINQ opeartors")]
  257. public void LinqToEntities22()
  258. {
  259. var query = northwindContext.Customers.Where(cust => cust.Address.Country == "Denmark")
  260. .SelectMany(cust => cust.Orders.Where(o => o.Freight > 5));
  261. ObjectDumper.Write(query);
  262. }
  263. #endregion
  264. #region Aggregate Operators
  265. [Category("Aggregate Operators")]
  266. [Title("Count - Simple")]
  267. [Description("This sample uses COUNT to get the number of Orders.")]
  268. public void LinqToEntities23()
  269. {
  270. var query = northwindContext.Orders.Count();
  271. ObjectDumper.Write(query);
  272. }
  273. [Category("Aggregate Operators")]
  274. [Title("Count - Predicate 1")]
  275. [Description("This sample uses COUNT to get the number of Orders placed by Customers in Mexico.")]
  276. public void LinqToEntities24()
  277. {
  278. var query = northwindContext.Orders.Where(o => o.Customer.Address.Country == "Mexico").Count();
  279. ObjectDumper.Write(query);
  280. }
  281. [Category("Aggregate Operators")]
  282. [Title("Count - Predicate 2")]
  283. [Description("This sample uses COUNT to get the number of Orders shipped to Mexico.")]
  284. public void LinqToEntities25()
  285. {
  286. var query = northwindContext.Orders
  287. .Where(o => o.ShipCountry == "Mexico").Count();
  288. ObjectDumper.Write(query);
  289. }
  290. [Category("Aggregate Operators")]
  291. [Title("Sum - Simple 1")]
  292. [Description("This sample uses SUM to find the total freight over all Orders.")]
  293. public void LinqToEntities26()
  294. {
  295. var query = northwindContext.Orders.Select(o => o.Freight).Sum();
  296. ObjectDumper.Write(query);
  297. }
  298. [Category("Aggregate Operators")]
  299. [Title("Sum - Simple 2")]
  300. [Description("This sample uses SUM to find the total number of units on order over all Products.")]
  301. public void LinqToEntities27()
  302. {
  303. var query = northwindContext.Products.Sum(p => p.UnitsOnOrder);
  304. ObjectDumper.Write(query);
  305. }
  306. [Category("Aggregate Operators")]
  307. [Title("Sum - Simple 3")]
  308. [Description("This sample uses SUM to find the total number of units on order over all Products out-of-stock.")]
  309. public void LinqToEntities28()
  310. {
  311. var query = northwindContext.Products.Where(p => p.UnitsInStock == 0).Sum(p => p.UnitsOnOrder);
  312. ObjectDumper.Write(query);
  313. }
  314. [Category("Aggregate Operators")]
  315. [Title("Min - Simple 1")]
  316. [Description("This sample uses MIN to find the lowest unit price of any Product.")]
  317. public void LinqToEntities29()
  318. {
  319. var query = northwindContext.Products.Select(p => p.UnitPrice).Min();
  320. ObjectDumper.Write(query);
  321. }
  322. [Category("Aggregate Operators")]
  323. [Title("Min - Simple 2")]
  324. [Description("This sample uses MIN to find the lowest freight of any Order.")]
  325. public void LinqToEntities30()
  326. {
  327. var query = northwindContext.Orders.Min(o => o.Freight);
  328. ObjectDumper.Write(query);
  329. }
  330. [Category("Aggregate Operators")]
  331. [Title("Min - Predicate")]
  332. [Description("This sample uses MIN to find the lowest freight of any Order shipped to Mexico.")]
  333. public void LinqToEntities31()
  334. {
  335. var query = northwindContext.Orders.Where(o => o.ShipCountry == "Mexico").Min(o => o.Freight);
  336. ObjectDumper.Write(query);
  337. }
  338. [Category("Aggregate Operators")]
  339. [Title("Min - Grouping")]
  340. [Description("This sample uses Min to find the Products that have the lowest unit price in each category, and returns the result as an anonoymous type.")]
  341. public void LinqToEntities32()
  342. {
  343. var query = from p in northwindContext.Products
  344. group p by p.Category into g
  345. select new
  346. {
  347. CategoryID = g.Key,
  348. CheapestProducts =
  349. from p2 in g
  350. where p2.UnitPrice == g.Min(p3 => p3.UnitPrice)
  351. select p2
  352. };
  353. ObjectDumper.Write(query, 1);
  354. }
  355. [Category("Aggregate Operators")]
  356. [Title("Max - Simple 1")]
  357. [Description("This sample uses MAX to find the latest hire date of any Employee.")]
  358. public void LinqToEntities33()
  359. {
  360. var query = northwindContext.Employees.Select(e => e.HireDate).Max();
  361. ObjectDumper.Write(query);
  362. }
  363. [Category("Aggregate Operators")]
  364. [Title("Max - Simple 2")]
  365. [Description("This sample uses MAX to find the most units in stock of any Product.")]
  366. public void LinqToEntities34()
  367. {
  368. var query = northwindContext.Products.Max(p => p.UnitsInStock);
  369. ObjectDumper.Write(query);
  370. }
  371. [Category("Aggregate Operators")]
  372. [Title("Max - Predicate")]
  373. [Description("This sample uses MAX to find the most units in stock of any Product with CategoryID = 1.")]
  374. public void LinqToEntities35()
  375. {
  376. var query = northwindContext.Products.Where(p => p.Category.CategoryID == 1).Max(p => p.UnitsInStock);
  377. ObjectDumper.Write(query);
  378. }
  379. [Category("Aggregate Operators")]
  380. [Title("Max - Grouping")]
  381. [Description("This sample uses MAX to find the Products that have the highest unit price in each category, and returns the result as an anonoymous type.")]
  382. public void LinqToEntities36()
  383. {
  384. var query = from p in northwindContext.Products
  385. group p by p.Category into g
  386. select new
  387. {
  388. g.Key,
  389. MostExpensiveProducts =
  390. from p2 in g
  391. where p2.UnitPrice == g.Max(p3 => p3.UnitPrice)
  392. select p2
  393. };
  394. ObjectDumper.Write(query, 1);
  395. }
  396. [Category("Aggregate Operators")]
  397. [Title("Average - Simple 1")]
  398. [Description("This sample uses AVERAGE to find the average freight of all Orders.")]
  399. public void LinqToEntities37()
  400. {
  401. var query = northwindContext.Orders.Select(o => o.Freight).Average();
  402. ObjectDumper.Write(query);
  403. }
  404. [Category("Aggregate Operators")]
  405. [Title("Average - Simple 2")]
  406. [Description("This sample uses AVERAGE to find the average unit price of all Products.")]
  407. public void LinqToEntities38()
  408. {
  409. var query = northwindContext.Products.Average(p => p.UnitPrice);
  410. ObjectDumper.Write(query);
  411. }
  412. [Category("Aggregate Operators")]
  413. [Title("Average - Predicate")]
  414. [Description("This sample uses AVERAGE to find the average unit price of all Products with CategoryID = 1.")]
  415. public void LinqToEntities39()
  416. {
  417. var query = northwindContext.Products.Where(p => p.Category.CategoryID == 1).Average(p => p.UnitPrice);
  418. ObjectDumper.Write(query);
  419. }
  420. [Category("Aggregate Operators")]
  421. [Title("Average - Grouping 1")]
  422. [Description("This sample uses AVERAGE to find the Products that have unit price higher than the average unit price of the category for each category.")]
  423. public void LinqToEntities40()
  424. {
  425. var query = from p in northwindContext.Products
  426. group p by p.Category into g
  427. select new
  428. {
  429. g.Key,
  430. ExpensiveProducts =
  431. from p2 in g
  432. where p2.UnitPrice > g.Average(p3 => p3.UnitPrice)
  433. select p2
  434. };
  435. ObjectDumper.Write(query, 1);
  436. }
  437. [Category("Aggregate Operators")]
  438. [Title("Average - Grouping 2")]
  439. [Description("This sample uses AVERAGE to find the average unit price of each category.")]
  440. public void LinqToEntities41()
  441. {
  442. var query = from p in northwindContext.Products
  443. group p by p.Category into g
  444. select new
  445. {
  446. g.Key,
  447. Average = g.Average(p => p.UnitPrice)
  448. };
  449. ObjectDumper.Write(query, 1);
  450. }
  451. #endregion
  452. #region Set Operators
  453. [Category("Set Operators")]
  454. [Title("First - Simple")]
  455. [Description("This sample uses FIRST and WHERE to get the first (database order) order that is shipped to Seattle. The WHERE predicate is evaluated on the server.")]
  456. public void LinqToEntities42()
  457. {
  458. var query = from o in northwindContext.Orders
  459. where o.ShipCity == "Seattle"
  460. select o;
  461. var result = query.First();
  462. ObjectDumper.Write(result);
  463. }
  464. [Category("Set Operators")]
  465. [Title("First - Predicate")]
  466. [Description("This sample uses FIRST to get the first (database order) order that is shipped to Seattle. The predicate is evaluated on the client.")]
  467. public void LinqToEntities43()
  468. {
  469. var query = from o in northwindContext.Orders
  470. select o;
  471. var result = query
  472. .First(x => x.ShipCity == "Seattle");
  473. ObjectDumper.Write(result);
  474. }
  475. [Category("Set Operators")]
  476. [Title("First - Ordered")]
  477. [Description("This sample uses FIRST, WHERE and ORDER BY to get the first order that is shipped to Seattle, ordered by date. The predicate is evaluated on the server.")]
  478. public void LinqToEntities44()
  479. {
  480. var query = from o in northwindContext.Orders
  481. where o.ShipCity == "Seattle"
  482. orderby o.OrderDate
  483. select o;
  484. var result = query.First();
  485. ObjectDumper.Write(result);
  486. }
  487. [Category("Set Operators")]
  488. [Title("Distinct - Simple")]
  489. [Description("This sample uses DISTINCT to get all the categories of products.")]
  490. public void LinqToEntities45()
  491. {
  492. var query = northwindContext.Products.Select(o => o.Category).Distinct();
  493. ObjectDumper.Write(query);
  494. }
  495. [Category("Set Operators")]
  496. [Title("Union - Simple")]
  497. [Description("This sample uses UNION to get all the orders where the shipping country was Mexico or Canada.")]
  498. public void LinqToEntities46()
  499. {
  500. var mexico = northwindContext.Orders.Where(o => o.ShipCountry == "Mexico").Select(o => o);
  501. var canada = northwindContext.Orders.Where(o => o.ShipCountry == "Canada").Select(o => o);
  502. var query = mexico.Union(canada);
  503. ObjectDumper.Write(query);
  504. }
  505. [Category("Set Operators")]
  506. [Title("Union - With Distinct")]
  507. [Description("This sample uses UNION and DISTINCT to get all the Customers from orders where the shipping country was Mexico or Canada.")]
  508. public void LinqToEntities47()
  509. {
  510. var mexico = northwindContext.Orders.Where(o => o.ShipCountry == "Mexico").Select(o => o);
  511. var canada = northwindContext.Orders.Where(o => o.ShipCountry == "Canada").Select(o => o);
  512. var union = mexico.Union(canada).Select(o => o.Customer);
  513. var query = union.Distinct();
  514. ObjectDumper.Write(query);
  515. }
  516. [Category("Set Operators")]
  517. [Title("Concat - Simple")]
  518. [Description("This sample uses CONCAT to get all orders where the shipping country was Mexico or Canada.")]
  519. public void LinqToEntities48()
  520. {
  521. var mexico = northwindContext.Orders.Where(o => o.ShipCountry == "Mexico").Select(o => o);
  522. var canada = northwindContext.Orders.Where(o => o.ShipCountry == "Canada").Select(o => o);
  523. var query = mexico.Concat(canada);
  524. ObjectDumper.Write(query);
  525. }
  526. [Category("Set Operators")]
  527. [Title("Intersect - Simple 1")]
  528. [Description("This sample uses INTERSECT to get common products where an order was shipped to Mexico or Canada.")]
  529. public void LinqToEntities49()
  530. {
  531. var mexico = northwindContext.OrderDetails.Where(od => od.Order.ShipCountry == "Mexico").Select(od => od.Product);
  532. var canada = northwindContext.OrderDetails.Where(od => od.Order.ShipCountry == "Canada").Select(od => od.Product);
  533. var query = mexico.Intersect(canada);
  534. ObjectDumper.Write(query);
  535. }
  536. [Category("Set Operators")]
  537. [Title("Intersect - Simple 2")]
  538. [Description("This sample uses INTERSECT to get common products where an order was shipped to Mexico or USA in one consolidated query.")]
  539. public void LinqToEntities50()
  540. {
  541. var query = northwindContext.OrderDetails.Where(od => od.Order.ShipCountry == "Mexico").Select(od => od.Product).Intersect(northwindContext.OrderDetails.Where(od => od.Order.ShipCountry == "USA").Select(o => o.Product));
  542. ObjectDumper.Write(query);
  543. }
  544. [Category("Set Operators")]
  545. [Title("Except - Simple 1")]
  546. [Description("This sample uses EXCEPT to get customers who shipped orders to Mexico but not Canada.")]
  547. public void LinqToEntities51()
  548. {
  549. var query = northwindContext.Orders.Where(o => o.ShipCountry == "Mexico").Select(o => o.Customer).Except(northwindContext.Orders.Where(o => o.ShipCountry == "Canada").Select(o => o.Customer));
  550. ObjectDumper.Write(query);
  551. }
  552. [Category("Set Operators")]
  553. [Title("Except - Simple 2")]
  554. [Description("This sample uses EXCEPT to get customers with no orders sent to Mexico.")]
  555. public void LinqToEntities52()
  556. {
  557. var query = northwindContext.Customers.Select(e => e)
  558. .Except(northwindContext.Orders.Where(o => o.ShipCountry == "Mexico").Select(o => o.Customer));
  559. ObjectDumper.Write(query);
  560. }
  561. #endregion
  562. #region Ordering and Grouping
  563. [Category("Ordering and Grouping")]
  564. [Title("OrderBy - Simple 1")]
  565. [Description("Select all customers ordered by ContactName.")]
  566. public void LinqToEntities53()
  567. {
  568. var query = from c in northwindContext.Customers
  569. orderby c.ContactName
  570. select c;
  571. ObjectDumper.Write(query);
  572. }
  573. [Category("Ordering and Grouping")]
  574. [Title("OrderBy - Simple 2")]
  575. [Description("Select all customers ordered by ContactName descending.")]
  576. public void LinqToEntities54()
  577. {
  578. var query = from c in northwindContext.Customers
  579. orderby c.CompanyName descending
  580. select c;
  581. ObjectDumper.Write(query);
  582. }
  583. [Category("Ordering and Grouping")]
  584. [Title("OrderBy - Simple 3")]
  585. [Description("Select an anonoymous type with all product IDs ordered by UnitInStock.")]
  586. public void LinqToEntities55()
  587. {
  588. var query = from p in northwindContext.Products
  589. orderby p.UnitsInStock
  590. select new { p.ProductID };
  591. ObjectDumper.Write(query);
  592. }
  593. [Category("Ordering and Grouping")]
  594. [Title("OrderBy - Simple 4")]
  595. [Description("Select an anonoymous type with all product IDs ordered by UnitInStockas a method query.")]
  596. public void LinqToEntities56()
  597. {
  598. var query = northwindContext.Products.OrderBy(p => p.UnitsInStock)
  599. .Select(p2 => new { p2.ProductID });
  600. ObjectDumper.Write(query);
  601. }
  602. [Category("Ordering and Grouping")]
  603. [Title("OrderByDescending - Simple 1")]
  604. [Description("Select all customers ordered by the descending region.")]
  605. public void LinqToEntities57()
  606. {
  607. var query = from c in northwindContext.Customers
  608. orderby c.Address.Region descending
  609. select c;
  610. ObjectDumper.Write(query);
  611. }
  612. [Category("Ordering and Grouping")]
  613. [Title("OrderByDescending - Simple 2")]
  614. [Description("Select all customers ordered by the descending region as a method query.")]
  615. public void LinqToEntities58()
  616. {
  617. var query = northwindContext.Customers.Select(c => c).OrderByDescending(c2 => c2.Address.Region);
  618. ObjectDumper.Write(query);
  619. }
  620. [Category("Ordering and Grouping")]
  621. [Title("OrderBy with ThenBy")]
  622. [Description("Select all customers ordered by the region, then the contact name.")]
  623. public void LinqToEntities59()
  624. {
  625. var query = northwindContext.Customers.Select(c => c).OrderBy(c => c.Address.Region).ThenBy(c => c.ContactName);
  626. ObjectDumper.Write(query);
  627. }
  628. [Category("Ordering and Grouping")]
  629. [Title("OrderByDescending with ThenBy")]
  630. [Description("Select all customers ordered by the region in descending order, then the contact name.")]
  631. public void LinqToEntities60()
  632. {
  633. var query = northwindContext.Customers.Select(c => c).OrderByDescending(c => c.Address.Region).ThenBy(c => c.ContactName);
  634. ObjectDumper.Write(query);
  635. }
  636. [Category("Ordering and Grouping")]
  637. [Title("OrderBy with ThenByDescending")]
  638. [Description("Select all customers ordered by the region then the contact name in descending order.")]
  639. public void LinqToEntities61()
  640. {
  641. var query = northwindContext.Customers.Select(c => c).OrderBy(c => c.Address.Region).ThenByDescending(c => c.ContactName);
  642. ObjectDumper.Write(query);
  643. }
  644. [Category("Ordering and Grouping")]
  645. [Title("OrderByDescending - Simple 3")]
  646. [Description("Select all products ordered by the descending unit price.")]
  647. public void LinqToEntities62()
  648. {
  649. var query = from p in northwindContext.Products
  650. orderby p.UnitPrice descending
  651. select p;
  652. ObjectDumper.Write(query);
  653. }
  654. [Category("Ordering and Grouping")]
  655. [Title("OrderBy - FK Collection")]
  656. [Description("Select all orders for a customer ordered by date that the order was placed.")]
  657. public void LinqToEntities63()
  658. {
  659. var query = northwindContext.Customers.Where(cust => cust.CustomerID == "ALFKI")
  660. .SelectMany(c => c.Orders.Select(o => o))
  661. .OrderBy(o2 => o2.OrderDate);
  662. foreach (var order in query)
  663. {
  664. ObjectDumper.Write(order);
  665. }
  666. }
  667. [Category("Ordering and Grouping")]
  668. [Title("Grouping - Simple 1")]
  669. [Description("Select all Regions with a customer.")]
  670. public void LinqToEntities64()
  671. {
  672. var query = from c in northwindContext.Customers
  673. group c by c.Address.Region into regions
  674. select new { regions.Key };
  675. ObjectDumper.Write(query);
  676. }
  677. [Category("Ordering and Grouping")]
  678. [Title("Grouping - Simple 2")]
  679. [Description("Select all dates with orders placed.")]
  680. public void LinqToEntities65()
  681. {
  682. var query = from o in northwindContext.Orders
  683. group o by o.OrderDate into dates
  684. select new { dates.Key };
  685. ObjectDumper.Write(query);
  686. }
  687. [Category("Ordering and Grouping")]
  688. [Title("Grouping - Join 1")]
  689. [Description("Select all Regions and customer count for each region.")]
  690. public void LinqToEntities66()
  691. {
  692. var query = from c in northwindContext.Customers
  693. group c by c.Address.Region into regions
  694. select new { region = regions.Key, count = regions.Count() };
  695. ObjectDumper.Write(query);
  696. }
  697. [Category("Ordering and Grouping")]
  698. [Title("Grouping - Join 2")]
  699. [Description("Select all Regions and customer count for each region as a method query.")]
  700. public void LinqToEntities67()
  701. {
  702. var query = northwindContext.Customers.GroupBy(c => c.Address.Region).Select(r => new { region = r.Key, count = r.Count() });
  703. ObjectDumper.Write(query);
  704. }
  705. [Category("Ordering and Grouping")]
  706. [Title("Grouping with a join on Key 1")]
  707. [Description("Select all Customer Regions with the total Freight on all orders for Customers in that Region.")]
  708. public void LinqToEntities68()
  709. {
  710. var query = from c in northwindContext.Customers
  711. group c by c.Address.Region into regions
  712. join c2 in northwindContext.Customers on regions.Key equals c2.Address.Region
  713. select new { region = regions.Key, total = c2.Orders.Sum(o => o.Freight) };
  714. ObjectDumper.Write(query);
  715. }
  716. [Category("Ordering and Grouping")]
  717. [Title("Grouping with a join on Key 2")]
  718. [Description("Select all Customer Regions with the total Freight on all orders for Customers in that Region as a method query.")]
  719. public void LinqToEntities69()
  720. {
  721. var query = northwindContext.Customers.GroupBy(c => c.Address.Region)
  722. .Select(g => new
  723. {
  724. Region = g.Key,
  725. FreightTotal = g
  726. .SelectMany(c2 => c2.Orders)
  727. .Sum(o => o.Freight)
  728. });
  729. ObjectDumper.Write(query);
  730. }
  731. #endregion
  732. #region Relationship Navigation
  733. [Category("Relationship Navigation")]
  734. [Title("Relationship Collection 1")]
  735. [Description("Select a sequence of all the orders for a customer using Select.")]
  736. public void LinqToEntities70()
  737. {
  738. var query = northwindContext.Customers.Where(cust => cust.CustomerID == "ALFKI")
  739. .Select(c => c.Orders.Select(o => o));
  740. foreach (var order in query)
  741. {
  742. ObjectDumper.Write(order);
  743. }
  744. }
  745. [Category("Relationship Navigation")]
  746. [Title("Relationship Collection 2")]
  747. [Description("Select all the orders for a customer using SelectMany.")]
  748. public void LinqToEntities71()
  749. {
  750. var query = northwindContext.Customers.Where(cust => cust.CustomerID == "ALFKI").SelectMany(c => c.Orders);
  751. ObjectDumper.Write(query);
  752. }
  753. [Category("Relationship Navigation")]
  754. [Title("Relationship Collection 3")]
  755. [Description("Select number of orders placed in 1998 for a customer.")]
  756. public void LinqToEntities74()
  757. {
  758. var query = northwindContext.Customers
  759. .Where(cust => cust.CustomerID == "ALFKI")
  760. .SelectMany(c => c.Orders)
  761. .Where(o => o.OrderDate.HasValue == true && o.OrderDate.Value.Year == 1998);
  762. ObjectDumper.Write(query);
  763. }
  764. [Category("Relationship Navigation")]
  765. [Title("Relationship Collection Aggregate")]
  766. [Description("Select a customer and the sum of the freight of thier orders.")]
  767. public void LinqToEntities73()
  768. {
  769. var query = northwindContext.Customers.Where(cust => cust.CustomerID == "ALFKI")
  770. .Select(c => c.Orders.Sum(o => o.Freight));
  771. ObjectDumper.Write(query);
  772. }
  773. [Category("Relationship Navigation")]
  774. [Title("Relationship Collection with Predicate")]
  775. [Description("Select customers with an order where the shipping address is the same as the customers.")]
  776. public void LinqToEntities75()
  777. {
  778. var query = northwindContext.Customers.Where(cust => cust.Orders.Any(o => o.ShipAddress == cust.Address.Address)).Select(c2 => c2);
  779. ObjectDumper.Write(query);
  780. }
  781. [Category("Relationship Navigation")]
  782. [Title("Relationship Collection with Grouping and Aggregate")]
  783. [Description("Selects all regions with a customer, and shows the sum of orders for customers for each region.")]
  784. public void LinqToEntities76()
  785. {
  786. var query = from c in northwindContext.Customers
  787. group c by c.Address.Region into regions
  788. join c2 in northwindContext.Customers on regions.Key equals c2.Address.Region
  789. select new { region = regions.Key, total = c2.Orders.Sum(o => o.Freight) };
  790. ObjectDumper.Write(query);
  791. }
  792. #endregion
  793. #region Inheritance
  794. [Category("Inheritance")]
  795. [Title("Show Type in Hierarchy")]
  796. [Description("Select all products, both active and discontinued products, and shows the type.")]
  797. public void LinqToEntities77()
  798. {
  799. var query = northwindContext
  800. .Products
  801. .Select(p => p);
  802. var query2 = query
  803. // force local execution to show local type
  804. .AsEnumerable()
  805. .Select(p => new { type = p.GetType().ToString(), prod = p });
  806. ObjectDumper.Write(query2);
  807. }
  808. [Category("Inheritance")]
  809. [Title("Select by Type in Hierarchy - OfType 1")]
  810. [Description("Select only discontinued products.")]
  811. public void LinqToEntities78()
  812. {
  813. var query = northwindContext.Products.OfType<DiscontinuedProduct>().Select(p => p);
  814. ObjectDumper.Write(query);
  815. }
  816. [Category("Inheritance")]
  817. [Title("Select by Type in Hierarchy - OfType 2")]
  818. [Description("Select only products, which will reutrn all Products and subtypes of Products (DiscontinuedProducts and ActiveProducts).")]
  819. public void LinqToEntities79()
  820. {
  821. var query = northwindContext.Products.OfType<Product>().Select(p => p);
  822. ObjectDumper.Write(query);
  823. }
  824. [Category("Inheritance")]
  825. [Title("Select by Type in Hierarchy - OfType 3")]
  826. [Description("Select only discontinued products.")]
  827. public void LinqToEntities80()
  828. {
  829. var query = northwindContext.Products.OfType<DiscontinuedProduct>();
  830. ObjectDumper.Write(query);
  831. }
  832. [Category("Inheritance")]
  833. [Title("Select by Type in Hierarchy - is")]
  834. [Description("Select only discontinued products.")]
  835. public void LinqToEntities81()
  836. {
  837. var query = northwindContext.Products.Where(p => p is DiscontinuedProduct);
  838. ObjectDumper.Write(query);
  839. }
  840. [Category("Inheritance")]
  841. [Title("Select by Subtype in Hierarchy - OfType")]
  842. [Description("Select all current employees.")]
  843. public void LinqToEntities87()
  844. {
  845. var query = northwindContext.Employees.OfType<CurrentEmployee>().ToList().Select(p => new { type = p.GetType().ToString(), p });
  846. ObjectDumper.Write(query);
  847. }
  848. #endregion
  849. #region Runtime behavior closure
  850. class MyClass {
  851. public static decimal Val = 50;
  852. public decimal GetVal()
  853. {
  854. return MyClass.Val;
  855. }
  856. }
  857. [Category("Runtime behavior example")]
  858. [Title("Static variable reference")]
  859. [Description("Uses a local variable as a query parameter.")]
  860. public void LinqToEntities91()
  861. {
  862. MyClass c = new MyClass();
  863. var query = northwindContext.Orders.Where(o => o.Freight > MyClass.Val).Select(o => new { o.Freight, o });
  864. ObjectDumper.Write(query);
  865. }
  866. [Category("Runtime behavior example")]
  867. [Title("Deferred execution")]
  868. [Description("Uses a the value of the local variable at query execution time.")]
  869. public void LinqToEntities92()
  870. {
  871. decimal x = 50;
  872. var query = northwindContext.Orders.Where(o => o.Freight > x).Select(o => new { o.Freight, o });
  873. x = 100;
  874. ObjectDumper.Write(query);
  875. }
  876. [Category("Runtime behavior example")]
  877. [Title("Closure")]
  878. [Description("Each execution uses the current value of the local variable.")]
  879. public void LinqToEntities93()
  880. {
  881. decimal x = 100;
  882. var query = northwindContext.Orders.Where(o => o.Freight > x).Select(o => new { o.Freight, o });
  883. ObjectDumper.Write(x);
  884. ObjectDumper.Write(query);
  885. x = 200;
  886. ObjectDumper.Write(x);
  887. ObjectDumper.Write(query);
  888. }
  889. #endregion
  890. #region Span
  891. [Category("Span")]
  892. [Title("Load Related Entities - One Level")]
  893. [Description("Load OrderDetails with Orders ")]
  894. public void LinqToEntities94()
  895. {
  896. var query = northwindContext.Orders.Include("OrderDetails").Select(o => o);
  897. ObjectDumper.Write(query);
  898. }
  899. [Category("Span")]
  900. [Title("Load Related Entities - Two Levels")]
  901. [Description("Load OrderDetails and Products with Orders ")]
  902. public void LinqToEntities95()
  903. {
  904. var query = northwindContext.Orders.Include("OrderDetails").Include("OrderDetails.Product").Select(o => o);
  905. ObjectDumper.Write(query);
  906. }
  907. #endregion
  908. #region Paging
  909. [Category("Paging")]
  910. [Title("Skip 1")]
  911. [Description("Skip the most recent 2 orders")]
  912. public void LinqToEntities96()
  913. {
  914. var query = northwindContext.Orders
  915. .OrderBy(o => o.OrderDate)
  916. .Skip(2).Select(o => o);
  917. ObjectDumper.Write(query);
  918. }
  919. [Category("Paging")]
  920. [Title("Take 1")]
  921. [Description("Take the 2 most recent Orders ")]
  922. public void LinqToEntities97()
  923. {
  924. var query = northwindContext.Orders
  925. .OrderBy(o => o.OrderDate)
  926. .Take(2).Select(o => o);
  927. ObjectDumper.Write(query);
  928. }
  929. [Category("Paging")]
  930. [Title("Take and Skip - 1")]
  931. [Description("Take the 10th to the 20th Orders, ordered by date ")]
  932. public void LinqToEntities98()
  933. {
  934. var query = northwindContext.Orders
  935. .OrderBy(o => o.OrderDate)
  936. .Skip(10).Take(10).Select(o => o);
  937. ObjectDumper.Write(query);
  938. }
  939. [Category("Paging")]
  940. [Title("Take and Skip - 2")]
  941. [Description("Use a page number variable to get the xth page")]
  942. public void LinqToEntities99()
  943. {
  944. int pageSize = 10;
  945. int pageNumber = 4;
  946. var query = northwindContext.Orders
  947. .OrderBy(o => o.OrderDate)
  948. .Skip(pageSize * pageNumber).Take(pageSize).Select(o => o);
  949. ObjectDumper.Write(query);
  950. }
  951. #endregion
  952. }
  953. }