PageRenderTime 45ms CodeModel.GetById 14ms RepoModel.GetById 0ms app.codeStats 0ms

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

#
C# | 610 lines | 415 code | 112 blank | 83 comment | 1 complexity | 601a7b71a3b5f4f5f808153767d0a814 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("Query Builder Method Samples")]
  21. [Prefix("ObjectServices")]
  22. class BuilderMethodSamples : SampleHarness
  23. {
  24. NorthwindEFModel.NorthwindEntities northwindContext;
  25. public BuilderMethodSamples()
  26. {
  27. northwindContext = new NorthwindEFModel.NorthwindEntities();
  28. }
  29. public override void CleanUp()
  30. {
  31. northwindContext = new NorthwindEFModel.NorthwindEntities();
  32. }
  33. #region Where
  34. [Category("Restriction")]
  35. [Title("Where - Simple")]
  36. [Description("This sample loads Categories with the CategoryName equal to Beverages.")]
  37. public void ObjectServicesRestriction1()
  38. {
  39. var query = northwindContext.Categories.Where("it.CategoryName = 'Beverages'");
  40. ObjectDumper.Write(query);
  41. }
  42. [Category("Restriction")]
  43. [Title("Where - Wildcard")]
  44. [Description("This sample loads Categories where the CategoryName starts with C.")]
  45. public void ObjectServicesRestriction2()
  46. {
  47. var query = northwindContext.Categories.Where("it.CategoryName LIKE 'C%'");
  48. ObjectDumper.Write(query);
  49. }
  50. [Category("Restriction")]
  51. [Title("Where - Related Simple")]
  52. [Description("This sample loads Order Details where the related Product is Aniseed Syrup.")]
  53. public void ObjectServicesRestriction3()
  54. {
  55. var query = northwindContext.OrderDetails.Where("it.Product.ProductName = 'Aniseed Syrup'");
  56. ObjectDumper.Write(query);
  57. }
  58. [Category("Restriction")]
  59. [Title("Where - Related Wilcard")]
  60. [Description("This sample loads Order Details where the related Product starts with C.")]
  61. public void ObjectServicesRestriction4()
  62. {
  63. var query = northwindContext.OrderDetails.Where("it.Product.ProductName LIKE 'C%'");
  64. ObjectDumper.Write(query);
  65. }
  66. [Category("Restriction")]
  67. [Title("Where - Parameter")]
  68. [Description("This sample loads Categories where the CategoryName starts with C.")]
  69. public void ObjectServicesRestriction5()
  70. {
  71. string name = "Suyama";
  72. var query = northwindContext.Employees.Where("it.LastName = @name", new ObjectParameter("name", name));
  73. ObjectDumper.Write(query);
  74. }
  75. #endregion
  76. #region Set
  77. [Category("Set operations")]
  78. [Title("Union - Entity Type")]
  79. [Description("This sample shows the union of Products with UnitPrice less than 10 and greater than 50.")]
  80. public void ObjectServicesSet1()
  81. {
  82. var query1 = northwindContext.Products.Where("it.UnitPrice < 10");
  83. var query2 = northwindContext.Products.Where("it.UnitPrice > 50");
  84. var query = query1.Union(query2);
  85. ObjectDumper.Write(query);
  86. }
  87. [Category("Set operations")]
  88. [Title("Union - Primitive Type")]
  89. [Description("This sample shows the union of the LastName of Employees with a title that starts with Sales and the FirstName of Employees with a title that starts with Vice.")]
  90. public void ObjectServicesSet2()
  91. {
  92. var query1 = northwindContext.Employees.Where("it.Title LIKE 'Sales%'").Select("it.LastName");
  93. var query2 = northwindContext.Employees.Where("it.Title LIKE 'Vice%'").Select("it.FirstName");
  94. var query = query1.Union(query2);
  95. ObjectDumper.Write(query);
  96. }
  97. [Category("Set operations")]
  98. [Title("Union - Primitive Type with filter")]
  99. [Description("This sample shows the union of the LastName of Employees with a title that starts with Sales and the FirstName of Employees with a title that starts with Vice where the result is greater than 6 characters long.")]
  100. public void ObjectServicesSet3()
  101. {
  102. var query1 = northwindContext.Employees.Where("it.Title LIKE 'Sales%'").Select("it.LastName");
  103. var query2 = northwindContext.Employees.Where("it.Title LIKE 'Vice%'").Select("it.FirstName");
  104. // The result column is named LastName, as that is the first column that we selected
  105. var query = query1.Union(query2).Where("EDM.Length(it.LastName) > 6");
  106. ObjectDumper.Write(query);
  107. }
  108. [Category("Set operations")]
  109. [Title("Intersect - Entity Type")]
  110. [Description("This sample shows the insection of Products with a UnitPrice of less than 10 and Chocolate in the ProductName.")]
  111. public void ObjectServicesSet4()
  112. {
  113. var query1 = northwindContext.Products.Where("it.UnitPrice < 10");
  114. var query2 = northwindContext.Products.Where("it.ProductName LIKE '%Chocolate%'");
  115. var query = query1.Intersect(query2);
  116. ObjectDumper.Write(query);
  117. }
  118. [Category("Set operations")]
  119. [Title("Intersect - Primative Type")]
  120. [Description("This sample shows the insection of ProductIDs with a UnitPrice of less than 10 and Chocolate in the ProductName.")]
  121. public void ObjectServicesSet5()
  122. {
  123. var query1 = northwindContext.Products.Where("it.UnitPrice < 10").Select("it.ProductID");
  124. var query2 = northwindContext.Products.Where("it.ProductName LIKE '%Chocolate%'").Select("it.ProductID"); ;
  125. var query = query1.Intersect(query2);
  126. ObjectDumper.Write(query);
  127. }
  128. [Category("Set operations")]
  129. [Title("Except")]
  130. [Description("This sample shows the all Products Except for Products with a UnitPrice of less than 10.")]
  131. public void ObjectServicesSet6()
  132. {
  133. var query1 = northwindContext.Products;
  134. var query2 = northwindContext.Products.Where("it.UnitPrice < 10");
  135. var query = query1.Except(query2);
  136. ObjectDumper.Write(query);
  137. }
  138. [Category("Set operations")]
  139. [Title("Exists")]
  140. [Description("This sample shows if a Product exists with a UnitPrice of less than 10.")]
  141. public void ObjectServicesSet7()
  142. {
  143. var query = northwindContext.Products.Where("it.UnitPrice < 10").Exists();
  144. ObjectDumper.Write(query);
  145. }
  146. [Category("Set operations")]
  147. [Title("Union All")]
  148. [Description("This sample shows the union all of all Products with a UnitPrice of less than 10 or less than 20 UnitsInStock.")]
  149. public void ObjectServicesSet8()
  150. {
  151. var query1 = northwindContext.Products.Where("it.UnitPrice < 10");
  152. var query2 = northwindContext.Products.Where("it.UnitsInStock < 20");
  153. var query = query1.UnionAll(query2);
  154. ObjectDumper.Write(query);
  155. }
  156. #endregion
  157. #region Ordering operations
  158. [Category("Ordering operations")]
  159. [Title("OrderBy - Simple Ascending")]
  160. [Description("This sample shows all Products ordered by the ProductName.")]
  161. public void ObjectServicesOrdering1()
  162. {
  163. var query = northwindContext.Products.OrderBy("it.ProductName");
  164. ObjectDumper.Write(query);
  165. }
  166. [Category("Ordering operations")]
  167. [Title("OrderBy - Simple Descending")]
  168. [Description("This sample shows all Products ordered by the ProductName in descending order.")]
  169. public void ObjectServicesOrdering2()
  170. {
  171. var query = northwindContext.Products.OrderBy("it.ProductName DESC");
  172. ObjectDumper.Write(query);
  173. }
  174. [Category("Ordering operations")]
  175. [Title("OrderBy - Related")]
  176. [Description("This sample shows all Products ordered by their CategoryName.")]
  177. public void ObjectServicesOrdering3()
  178. {
  179. var query = northwindContext.Products.OrderBy("it.Category.CategoryName");
  180. ObjectDumper.Write(query);
  181. }
  182. [Category("Ordering operations")]
  183. [Title("OrderBy - Multiple")]
  184. [Description("This sample shows all Products ordered by the Category and then by ProductName.")]
  185. public void ObjectServicesOrdering4()
  186. {
  187. var query = northwindContext.Products.OrderBy("it.Category.CategoryName, it.ProductName");
  188. ObjectDumper.Write(query);
  189. }
  190. [Category("Ordering operations")]
  191. [Title("OrderBy - Function")]
  192. [Description("This sample shows all Products ordered by the first letter of the ProductName.")]
  193. public void ObjectServicesOrdering5()
  194. {
  195. var query = northwindContext.Products.OrderBy("Left(it.ProductName,1) desc");
  196. ObjectDumper.Write(query);
  197. }
  198. [Category("Ordering operations")]
  199. [Title("OrderBy - Parameters")]
  200. [Description("This sample shows all Products ordered by the UnitPrice modulus 3.")]
  201. public void ObjectServicesOrdering6()
  202. {
  203. var query = northwindContext.Products.OrderBy("it.UnitPrice % @col", new ObjectParameter("col", 3));
  204. ObjectDumper.Write(query);
  205. }
  206. #endregion
  207. #region Paging
  208. [Category("Paging operations")]
  209. [Title("Skip")]
  210. [Description("This sample shows all Categories with the first two skipped.")]
  211. public void ObjectServicesPaging1()
  212. {
  213. var query = northwindContext.Categories.Skip("it.CategoryName", "2");
  214. ObjectDumper.Write(query);
  215. }
  216. [Category("Paging operations")]
  217. [Title("Skip with Parameter")]
  218. [Description("This sample shows all Products, with the first ten skipped.")]
  219. public void ObjectServicesPaging2()
  220. {
  221. int skipNumber = 10;
  222. var query = northwindContext.Categories.Skip("it.CategoryName", "@count", new ObjectParameter("count", skipNumber));
  223. ObjectDumper.Write(query);
  224. }
  225. [Category("Paging operations")]
  226. [Title("Top")]
  227. [Description("This sample shows the first 10 Products.")]
  228. public void ObjectServicesPaging3()
  229. {
  230. var query = northwindContext.Categories.Top("10");
  231. ObjectDumper.Write(query);
  232. }
  233. [Category("Paging operations")]
  234. [Title("Top with Parameter")]
  235. [Description("This sample shows the first 10 Products.")]
  236. public void ObjectServicesPaging4()
  237. {
  238. int topNumber = 10;
  239. var query = northwindContext.Categories.Top("@count", new ObjectParameter("count", topNumber));
  240. ObjectDumper.Write(query);
  241. }
  242. #endregion
  243. #region Misc
  244. [Category("Misc")]
  245. [Title("First")]
  246. [Description("This sample shows the first Category.")]
  247. public void ObjectServicesMisc1()
  248. {
  249. var query = northwindContext.Categories.First();
  250. ObjectDumper.Write(query);
  251. }
  252. [Category("Misc")]
  253. [Title("FirstOrDefault")]
  254. [Description("This sample gets the first or default of an empty set.")]
  255. public void ObjectServicesMisc2()
  256. {
  257. Category result;
  258. bool hasResults = northwindContext.Categories.Where("it.CategoryName = 'none'").FirstOrDefault(out result);
  259. if (hasResults)
  260. {
  261. ObjectDumper.Write(result);
  262. }
  263. else
  264. {
  265. ObjectDumper.Write("No Result");
  266. }
  267. }
  268. [Category("Misc")]
  269. [Title("GetList")]
  270. [Description("This sample gets all Employees as an IList.")]
  271. public void ObjectServicesMisc3()
  272. {
  273. var query = northwindContext.Employees.GetList();
  274. ObjectDumper.Write(query);
  275. }
  276. [Category("Misc")]
  277. [Title("GetResultType")]
  278. [Description("This sample gets the result type of a query.")]
  279. public void ObjectServicesMisc4()
  280. {
  281. var query = northwindContext.Employees.GetResultType();
  282. ObjectDumper.Write(query);
  283. }
  284. [Category("Misc")]
  285. [Title("Name")]
  286. [Description("This sample gets the name of the local name of the current result set.")]
  287. public void ObjectServicesMisc5()
  288. {
  289. var query = northwindContext.Employees.Name;
  290. ObjectDumper.Write(query);
  291. }
  292. [Category("Misc")]
  293. [Title("OfType")]
  294. [Description("This sample gets all the PreviousEmployees.")]
  295. public void ObjectServicesMisc6()
  296. {
  297. var query = northwindContext.Employees.OfType<PreviousEmployee>();
  298. ObjectDumper.Write(query);
  299. }
  300. #endregion
  301. #region Grouping
  302. [Category("Grouping")]
  303. [Title("Count")]
  304. [Description("This sample gets the count of all Employees, grouped by HireDate.")]
  305. public void ObjectServicesGrouping1()
  306. {
  307. var query = northwindContext.Employees.GroupBy("it.HireDate", "count(it.EmployeeID)");
  308. ObjectDumper.Write(query);
  309. }
  310. [Category("Grouping")]
  311. [Title("Min with Relationship")]
  312. [Description("This sample gets Minimum of all UnitsInStock for each Category, and display the number and the CategoryName.")]
  313. public void ObjectServicesGrouping2()
  314. {
  315. var query = northwindContext.Products.GroupBy("it.Category.CategoryName", "Min(it.UnitsInStock), it.Category.CategoryName");
  316. ObjectDumper.Write(query);
  317. }
  318. [Category("Grouping")]
  319. [Title("Min Composite")]
  320. [Description("This sample gets smallest order line total from each order.")]
  321. public void ObjectServicesGrouping3()
  322. {
  323. var query = northwindContext.OrderDetails.GroupBy("it.OrderID", "it.OrderID, Min((it.UnitPrice * it.Quantity))");
  324. ObjectDumper.Write(query);
  325. }
  326. [Category("Grouping")]
  327. [Title("Average")]
  328. [Description("This sample gets average of order lines total from each order.")]
  329. public void ObjectServicesGrouping4()
  330. {
  331. var query = northwindContext.OrderDetails.GroupBy("it.OrderID", "it.OrderID, Avg((it.UnitPrice * it.Quantity))");
  332. ObjectDumper.Write(query);
  333. }
  334. #endregion
  335. #region Include
  336. [Category("Include")]
  337. [Title("Single")]
  338. [Description("This sample loads all OrderDetails with the Order, and loads the top Order.")]
  339. public void ObjectServicesInclude1()
  340. {
  341. var query = northwindContext.Orders.Include("OrderDetails").Top("1");
  342. ObjectDumper.Write(query,1);
  343. }
  344. [Category("Include")]
  345. [Title("Single Counter Example")]
  346. [Description("This sample loads all OrderDetails with the Order, and loads the top Order.")]
  347. public void ObjectServicesInclude2()
  348. {
  349. //northwindContext.Orders.Include("");
  350. var query = northwindContext.Orders.Top("1");
  351. ObjectDumper.Write(query, 1);
  352. }
  353. #endregion
  354. #region Object Context
  355. [Category("Object Context")]
  356. [Title("Loading Options - NoTracking")]
  357. [Description("This sample loads the top 5 Orders, and does not track them.")]
  358. public void ObjectServicesOC1()
  359. {
  360. var query = northwindContext.Orders.Top("5").Execute(MergeOption.NoTracking);
  361. // we update the OrderDate
  362. foreach (Order o in query)
  363. {
  364. o.OrderDate = DateTime.Now;
  365. try
  366. {
  367. // check for the State Entry for each of the objects
  368. var state = northwindContext.ObjectStateManager.GetObjectStateEntry(o);
  369. ObjectDumper.Write(state);
  370. }
  371. catch (Exception ex)
  372. {
  373. // we don't have a State Entry for the Order, because we loaded with NoTracking
  374. ObjectDumper.Write(ex);
  375. }
  376. }
  377. }
  378. [Category("Object Context")]
  379. [Title("Loading Options - AppendOnly")]
  380. [Description("This sample loads the top 5 Orders into the Object Context and updates them locally. Another query is run with the AppendOnly option, which preserves local client values.")]
  381. public void ObjectServicesOC2()
  382. {
  383. var startQuery = northwindContext.Orders.Top("5");
  384. // we update the OrderDate
  385. foreach (Order o in startQuery)
  386. {
  387. o.OrderDate = DateTime.Now;
  388. }
  389. var query = northwindContext.Orders.Top("5").Execute(MergeOption.AppendOnly) ;
  390. // note that the OrderDate is still the client updated value
  391. ObjectDumper.Write(query);
  392. }
  393. [Category("Object Context")]
  394. [Title("Loading Options - OverwriteChanges")]
  395. [Description("This sample loads the top 5 Orders into the Object Context and updates them locally. Another query is run with the OverwriteChanges option, which overwrites the local client values.")]
  396. public void ObjectServicesOC3()
  397. {
  398. var startQuery = northwindContext.Orders.Top("5");
  399. // we update the OrderDate
  400. foreach (Order o in startQuery)
  401. {
  402. o.OrderDate = DateTime.Now;
  403. }
  404. var query = northwindContext.Orders.Top("5").Execute(MergeOption.OverwriteChanges);
  405. // note that the OrderDate is now the server value
  406. ObjectDumper.Write(query);
  407. }
  408. [Category("Object Context")]
  409. [Title("Loading Options - PreserveChanges")]
  410. [Description("This sample loads the top 5 Orders into the Object Context and updates them locally. Another query is run with the PreserveChanges option, which keeps the local client values.")]
  411. public void ObjectServicesOC4()
  412. {
  413. var startQuery = northwindContext.Orders.Top("5");
  414. // we update the OrderDate
  415. foreach (Order o in startQuery)
  416. {
  417. o.OrderDate = DateTime.Now;
  418. }
  419. var query = northwindContext.Orders.Top("5").Execute(MergeOption.PreserveChanges);
  420. // note that the OrderDate is still the client updated value
  421. ObjectDumper.Write(query);
  422. }
  423. #endregion
  424. #region Object Context
  425. /*
  426. [Category("Object Context and Metadata")]
  427. [Title("Loading and State Management 1")]
  428. [Description("This sample loads Entities without state management")]
  429. public void LinqToEntitiesOC1()
  430. {
  431. //--- execute a query for some instances without management in the state manager:
  432. IEnumerable<Category> categories = northwindContext.Categories.Execute(MergeOption.NoTracking);
  433. //--- print the category and the entity state : the state will be detached which indicates that it is not being managed in the state manager
  434. foreach (Category category in categories)
  435. {
  436. Console.WriteLine("Category Name: {0}; \n - State Manager Reference:{1}", category.CategoryName, category.EntityState);
  437. }
  438. }
  439. [Category("Object Context and Metadata")]
  440. [Title("Loading and State Management 2")]
  441. [Description("This sample loads Entities with AppendOnly")]
  442. public void LinqToEntitiesOC2()
  443. {
  444. //--- execute a query for some instances append them to the state manager
  445. IEnumerable<Category> categories = northwindContext.Categories.Execute(MergeOption.AppendOnly);
  446. //--- print the category and the entity state : the state will be unchanged which indicates that the instances are in the state manager but not changed
  447. foreach (Category category in categories)
  448. {
  449. Console.WriteLine("Category Name: {0}; \n - State Manager Reference:{1}", category.CategoryName, category.EntityState);
  450. }
  451. }
  452. [Category("Object Context and Metadata")]
  453. [Title("Loading and State Management 3")]
  454. [Description("This sample loads Entities overwriting in-memory edits")]
  455. public void LinqToEntitiesOC3()
  456. {
  457. //--- get a category to change
  458. Category cat = northwindContext.Categories.ToList().First();
  459. cat.CategoryName = "wwwww";
  460. Console.WriteLine("Category changed to have the name: {0} with ID: {1} \n", cat.CategoryName, cat.CategoryID);
  461. //--- execute a query for some instances append them to the state manager
  462. Console.WriteLine("retrieving categories and overwriting any changes in the state manager...\n");
  463. IEnumerable<Category> categories = northwindContext.Categories.Execute(MergeOption.OverwriteChanges);
  464. //--- print the category and the entity state : the state will be unchanged which indicates that the instances are in the state manager but not changed
  465. foreach (Category category in categories)
  466. {
  467. Console.WriteLine("Category ID: {0}, CategoryName: {1}, Entity State:{2}", category.CategoryID, category.CategoryName, category.EntityState);
  468. }
  469. }
  470. [Category("Object Context and Metadata")]
  471. [Title("Loading and State Management 4")]
  472. [Description("This sample loads Entities saving in-memory edits")]
  473. public void LinqToEntitiesOC4()
  474. {
  475. //--- get a category to change
  476. Category cat = northwindContext.Categories.ToList().First();
  477. cat.CategoryName = "wwwww";
  478. Console.WriteLine("Category changed to have the name: {0} with ID: {1} \n", cat.CategoryName, cat.CategoryID);
  479. //--- execute a query for some instances append them to the state manager
  480. Console.WriteLine("retrieving categories without overwriting any changes in the state manager...\n");
  481. IEnumerable<Category> categories = northwindContext.Categories.Execute(MergeOption.PreserveChanges);
  482. //--- print the category and the entity state : note that the changed entity remains changed
  483. foreach (Category category in categories)
  484. {
  485. Console.WriteLine("Category ID: {0}, CategoryName: {1}, Entity State:{2}", category.CategoryID, category.CategoryName, category.EntityState);
  486. }
  487. //--- note we are not invoking save changes so even though the change remains in-memory we wont push this to the store
  488. //--- calling save changes would push the change to the store but we want to leave the store in its original state
  489. */
  490. #endregion
  491. }
  492. }