PageRenderTime 55ms CodeModel.GetById 20ms RepoModel.GetById 1ms app.codeStats 0ms

/EntityFramework/Beta1/LINQ101/C#/SampleQueries/LinqToEntitiesSamples.cs

#
C# | 1815 lines | 1315 code | 346 blank | 154 comment | 79 complexity | 5a0c0326cfd909acc36f9a5b70d613d1 MD5 | raw file

Large files files are truncated, but you can click here to view the full file

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

Large files files are truncated, but you can click here to view the full file