/EntityFramework/Beta2/Entity Framework Query Samples/BuilderMethodSamples.cs
C# | 610 lines | 415 code | 112 blank | 83 comment | 1 complexity | 601a7b71a3b5f4f5f808153767d0a814 MD5 | raw file
- using System;
- using System.Collections.Generic;
- using System.Data;
- using System.Data.Metadata.Edm;
- using System.Data.Common;
- using System.Data.Objects;
- using System.Data.Linq;
- using System.Data.EntityClient;
- using System.Diagnostics;
- //using System.Linq;
- using SampleSupport;
- using System.Xml.Linq;
- using System.Text;
- using System.IO;
- using System.Windows.Forms;
- using System.Data.SqlClient;
- using NorthwindEFModel;
-
- namespace QuerySamples
- {
- [Title("Query Builder Method Samples")]
- [Prefix("ObjectServices")]
- class BuilderMethodSamples : SampleHarness
- {
- NorthwindEFModel.NorthwindEntities northwindContext;
-
- public BuilderMethodSamples()
- {
- northwindContext = new NorthwindEFModel.NorthwindEntities();
- }
-
- public override void CleanUp()
- {
- northwindContext = new NorthwindEFModel.NorthwindEntities();
- }
-
- #region Where
-
- [Category("Restriction")]
- [Title("Where - Simple")]
- [Description("This sample loads Categories with the CategoryName equal to Beverages.")]
- public void ObjectServicesRestriction1()
- {
- var query = northwindContext.Categories.Where("it.CategoryName = 'Beverages'");
-
- ObjectDumper.Write(query);
- }
-
- [Category("Restriction")]
- [Title("Where - Wildcard")]
- [Description("This sample loads Categories where the CategoryName starts with C.")]
- public void ObjectServicesRestriction2()
- {
- var query = northwindContext.Categories.Where("it.CategoryName LIKE 'C%'");
-
- ObjectDumper.Write(query);
- }
-
- [Category("Restriction")]
- [Title("Where - Related Simple")]
- [Description("This sample loads Order Details where the related Product is Aniseed Syrup.")]
- public void ObjectServicesRestriction3()
- {
- var query = northwindContext.OrderDetails.Where("it.Product.ProductName = 'Aniseed Syrup'");
-
- ObjectDumper.Write(query);
- }
-
- [Category("Restriction")]
- [Title("Where - Related Wilcard")]
- [Description("This sample loads Order Details where the related Product starts with C.")]
- public void ObjectServicesRestriction4()
- {
- var query = northwindContext.OrderDetails.Where("it.Product.ProductName LIKE 'C%'");
-
- ObjectDumper.Write(query);
- }
-
- [Category("Restriction")]
- [Title("Where - Parameter")]
- [Description("This sample loads Categories where the CategoryName starts with C.")]
- public void ObjectServicesRestriction5()
- {
- string name = "Suyama";
- var query = northwindContext.Employees.Where("it.LastName = @name", new ObjectParameter("name", name));
-
- ObjectDumper.Write(query);
- }
-
-
- #endregion
-
- #region Set
-
- [Category("Set operations")]
- [Title("Union - Entity Type")]
- [Description("This sample shows the union of Products with UnitPrice less than 10 and greater than 50.")]
- public void ObjectServicesSet1()
- {
- var query1 = northwindContext.Products.Where("it.UnitPrice < 10");
- var query2 = northwindContext.Products.Where("it.UnitPrice > 50");
-
- var query = query1.Union(query2);
-
- ObjectDumper.Write(query);
- }
-
- [Category("Set operations")]
- [Title("Union - Primitive Type")]
- [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.")]
- public void ObjectServicesSet2()
- {
- var query1 = northwindContext.Employees.Where("it.Title LIKE 'Sales%'").Select("it.LastName");
- var query2 = northwindContext.Employees.Where("it.Title LIKE 'Vice%'").Select("it.FirstName");
-
- var query = query1.Union(query2);
-
- ObjectDumper.Write(query);
- }
-
- [Category("Set operations")]
- [Title("Union - Primitive Type with filter")]
- [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.")]
- public void ObjectServicesSet3()
- {
- var query1 = northwindContext.Employees.Where("it.Title LIKE 'Sales%'").Select("it.LastName");
- var query2 = northwindContext.Employees.Where("it.Title LIKE 'Vice%'").Select("it.FirstName");
-
- // The result column is named LastName, as that is the first column that we selected
- var query = query1.Union(query2).Where("EDM.Length(it.LastName) > 6");
-
- ObjectDumper.Write(query);
- }
-
- [Category("Set operations")]
- [Title("Intersect - Entity Type")]
- [Description("This sample shows the insection of Products with a UnitPrice of less than 10 and Chocolate in the ProductName.")]
- public void ObjectServicesSet4()
- {
- var query1 = northwindContext.Products.Where("it.UnitPrice < 10");
- var query2 = northwindContext.Products.Where("it.ProductName LIKE '%Chocolate%'");
-
- var query = query1.Intersect(query2);
-
- ObjectDumper.Write(query);
- }
-
- [Category("Set operations")]
- [Title("Intersect - Primative Type")]
- [Description("This sample shows the insection of ProductIDs with a UnitPrice of less than 10 and Chocolate in the ProductName.")]
- public void ObjectServicesSet5()
- {
- var query1 = northwindContext.Products.Where("it.UnitPrice < 10").Select("it.ProductID");
- var query2 = northwindContext.Products.Where("it.ProductName LIKE '%Chocolate%'").Select("it.ProductID"); ;
-
- var query = query1.Intersect(query2);
-
- ObjectDumper.Write(query);
- }
-
- [Category("Set operations")]
- [Title("Except")]
- [Description("This sample shows the all Products Except for Products with a UnitPrice of less than 10.")]
- public void ObjectServicesSet6()
- {
-
- var query1 = northwindContext.Products;
- var query2 = northwindContext.Products.Where("it.UnitPrice < 10");
-
- var query = query1.Except(query2);
-
- ObjectDumper.Write(query);
- }
-
- [Category("Set operations")]
- [Title("Exists")]
- [Description("This sample shows if a Product exists with a UnitPrice of less than 10.")]
- public void ObjectServicesSet7()
- {
- var query = northwindContext.Products.Where("it.UnitPrice < 10").Exists();
-
- ObjectDumper.Write(query);
- }
-
- [Category("Set operations")]
- [Title("Union All")]
- [Description("This sample shows the union all of all Products with a UnitPrice of less than 10 or less than 20 UnitsInStock.")]
- public void ObjectServicesSet8()
- {
- var query1 = northwindContext.Products.Where("it.UnitPrice < 10");
- var query2 = northwindContext.Products.Where("it.UnitsInStock < 20");
-
- var query = query1.UnionAll(query2);
-
- ObjectDumper.Write(query);
- }
-
- #endregion
-
- #region Ordering operations
-
- [Category("Ordering operations")]
- [Title("OrderBy - Simple Ascending")]
- [Description("This sample shows all Products ordered by the ProductName.")]
- public void ObjectServicesOrdering1()
- {
- var query = northwindContext.Products.OrderBy("it.ProductName");
-
- ObjectDumper.Write(query);
- }
-
- [Category("Ordering operations")]
- [Title("OrderBy - Simple Descending")]
- [Description("This sample shows all Products ordered by the ProductName in descending order.")]
- public void ObjectServicesOrdering2()
- {
- var query = northwindContext.Products.OrderBy("it.ProductName DESC");
-
- ObjectDumper.Write(query);
- }
-
- [Category("Ordering operations")]
- [Title("OrderBy - Related")]
- [Description("This sample shows all Products ordered by their CategoryName.")]
- public void ObjectServicesOrdering3()
- {
- var query = northwindContext.Products.OrderBy("it.Category.CategoryName");
-
- ObjectDumper.Write(query);
- }
-
- [Category("Ordering operations")]
- [Title("OrderBy - Multiple")]
- [Description("This sample shows all Products ordered by the Category and then by ProductName.")]
- public void ObjectServicesOrdering4()
- {
- var query = northwindContext.Products.OrderBy("it.Category.CategoryName, it.ProductName");
-
- ObjectDumper.Write(query);
- }
-
- [Category("Ordering operations")]
- [Title("OrderBy - Function")]
- [Description("This sample shows all Products ordered by the first letter of the ProductName.")]
- public void ObjectServicesOrdering5()
- {
- var query = northwindContext.Products.OrderBy("Left(it.ProductName,1) desc");
-
- ObjectDumper.Write(query);
- }
-
- [Category("Ordering operations")]
- [Title("OrderBy - Parameters")]
- [Description("This sample shows all Products ordered by the UnitPrice modulus 3.")]
- public void ObjectServicesOrdering6()
- {
- var query = northwindContext.Products.OrderBy("it.UnitPrice % @col", new ObjectParameter("col", 3));
-
- ObjectDumper.Write(query);
- }
-
-
- #endregion
-
- #region Paging
-
- [Category("Paging operations")]
- [Title("Skip")]
- [Description("This sample shows all Categories with the first two skipped.")]
- public void ObjectServicesPaging1()
- {
- var query = northwindContext.Categories.Skip("it.CategoryName", "2");
-
- ObjectDumper.Write(query);
- }
-
- [Category("Paging operations")]
- [Title("Skip with Parameter")]
- [Description("This sample shows all Products, with the first ten skipped.")]
- public void ObjectServicesPaging2()
- {
- int skipNumber = 10;
- var query = northwindContext.Categories.Skip("it.CategoryName", "@count", new ObjectParameter("count", skipNumber));
-
- ObjectDumper.Write(query);
- }
-
- [Category("Paging operations")]
- [Title("Top")]
- [Description("This sample shows the first 10 Products.")]
- public void ObjectServicesPaging3()
- {
- var query = northwindContext.Categories.Top("10");
-
- ObjectDumper.Write(query);
- }
-
- [Category("Paging operations")]
- [Title("Top with Parameter")]
- [Description("This sample shows the first 10 Products.")]
- public void ObjectServicesPaging4()
- {
- int topNumber = 10;
- var query = northwindContext.Categories.Top("@count", new ObjectParameter("count", topNumber));
-
- ObjectDumper.Write(query);
- }
-
-
- #endregion
-
- #region Misc
-
- [Category("Misc")]
- [Title("First")]
- [Description("This sample shows the first Category.")]
- public void ObjectServicesMisc1()
- {
- var query = northwindContext.Categories.First();
-
- ObjectDumper.Write(query);
- }
-
-
- [Category("Misc")]
- [Title("FirstOrDefault")]
- [Description("This sample gets the first or default of an empty set.")]
- public void ObjectServicesMisc2()
- {
- Category result;
- bool hasResults = northwindContext.Categories.Where("it.CategoryName = 'none'").FirstOrDefault(out result);
-
- if (hasResults)
- {
- ObjectDumper.Write(result);
- }
- else
- {
- ObjectDumper.Write("No Result");
- }
- }
-
- [Category("Misc")]
- [Title("GetList")]
- [Description("This sample gets all Employees as an IList.")]
- public void ObjectServicesMisc3()
- {
- var query = northwindContext.Employees.GetList();
-
- ObjectDumper.Write(query);
- }
-
- [Category("Misc")]
- [Title("GetResultType")]
- [Description("This sample gets the result type of a query.")]
- public void ObjectServicesMisc4()
- {
- var query = northwindContext.Employees.GetResultType();
-
- ObjectDumper.Write(query);
- }
-
- [Category("Misc")]
- [Title("Name")]
- [Description("This sample gets the name of the local name of the current result set.")]
- public void ObjectServicesMisc5()
- {
- var query = northwindContext.Employees.Name;
-
- ObjectDumper.Write(query);
- }
-
- [Category("Misc")]
- [Title("OfType")]
- [Description("This sample gets all the PreviousEmployees.")]
- public void ObjectServicesMisc6()
- {
- var query = northwindContext.Employees.OfType<PreviousEmployee>();
-
- ObjectDumper.Write(query);
- }
-
- #endregion
-
- #region Grouping
-
- [Category("Grouping")]
- [Title("Count")]
- [Description("This sample gets the count of all Employees, grouped by HireDate.")]
- public void ObjectServicesGrouping1()
- {
- var query = northwindContext.Employees.GroupBy("it.HireDate", "count(it.EmployeeID)");
-
- ObjectDumper.Write(query);
- }
-
- [Category("Grouping")]
- [Title("Min with Relationship")]
- [Description("This sample gets Minimum of all UnitsInStock for each Category, and display the number and the CategoryName.")]
- public void ObjectServicesGrouping2()
- {
- var query = northwindContext.Products.GroupBy("it.Category.CategoryName", "Min(it.UnitsInStock), it.Category.CategoryName");
-
- ObjectDumper.Write(query);
- }
-
- [Category("Grouping")]
- [Title("Min Composite")]
- [Description("This sample gets smallest order line total from each order.")]
- public void ObjectServicesGrouping3()
- {
- var query = northwindContext.OrderDetails.GroupBy("it.OrderID", "it.OrderID, Min((it.UnitPrice * it.Quantity))");
-
- ObjectDumper.Write(query);
- }
-
- [Category("Grouping")]
- [Title("Average")]
- [Description("This sample gets average of order lines total from each order.")]
- public void ObjectServicesGrouping4()
- {
- var query = northwindContext.OrderDetails.GroupBy("it.OrderID", "it.OrderID, Avg((it.UnitPrice * it.Quantity))");
-
- ObjectDumper.Write(query);
- }
- #endregion
-
- #region Include
-
- [Category("Include")]
- [Title("Single")]
- [Description("This sample loads all OrderDetails with the Order, and loads the top Order.")]
- public void ObjectServicesInclude1()
- {
- var query = northwindContext.Orders.Include("OrderDetails").Top("1");
-
- ObjectDumper.Write(query,1);
- }
-
- [Category("Include")]
- [Title("Single Counter Example")]
- [Description("This sample loads all OrderDetails with the Order, and loads the top Order.")]
- public void ObjectServicesInclude2()
- {
- //northwindContext.Orders.Include("");
- var query = northwindContext.Orders.Top("1");
-
- ObjectDumper.Write(query, 1);
- }
-
- #endregion
-
- #region Object Context
-
- [Category("Object Context")]
- [Title("Loading Options - NoTracking")]
- [Description("This sample loads the top 5 Orders, and does not track them.")]
- public void ObjectServicesOC1()
- {
- var query = northwindContext.Orders.Top("5").Execute(MergeOption.NoTracking);
-
- // we update the OrderDate
- foreach (Order o in query)
- {
- o.OrderDate = DateTime.Now;
-
- try
- {
- // check for the State Entry for each of the objects
- var state = northwindContext.ObjectStateManager.GetObjectStateEntry(o);
- ObjectDumper.Write(state);
- }
- catch (Exception ex)
- {
- // we don't have a State Entry for the Order, because we loaded with NoTracking
- ObjectDumper.Write(ex);
- }
- }
- }
-
- [Category("Object Context")]
- [Title("Loading Options - AppendOnly")]
- [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.")]
- public void ObjectServicesOC2()
- {
- var startQuery = northwindContext.Orders.Top("5");
- // we update the OrderDate
- foreach (Order o in startQuery)
- {
- o.OrderDate = DateTime.Now;
- }
-
- var query = northwindContext.Orders.Top("5").Execute(MergeOption.AppendOnly) ;
- // note that the OrderDate is still the client updated value
- ObjectDumper.Write(query);
- }
-
- [Category("Object Context")]
- [Title("Loading Options - OverwriteChanges")]
- [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.")]
- public void ObjectServicesOC3()
- {
- var startQuery = northwindContext.Orders.Top("5");
- // we update the OrderDate
- foreach (Order o in startQuery)
- {
- o.OrderDate = DateTime.Now;
- }
-
- var query = northwindContext.Orders.Top("5").Execute(MergeOption.OverwriteChanges);
- // note that the OrderDate is now the server value
- ObjectDumper.Write(query);
- }
-
- [Category("Object Context")]
- [Title("Loading Options - PreserveChanges")]
- [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.")]
- public void ObjectServicesOC4()
- {
- var startQuery = northwindContext.Orders.Top("5");
- // we update the OrderDate
- foreach (Order o in startQuery)
- {
- o.OrderDate = DateTime.Now;
- }
-
- var query = northwindContext.Orders.Top("5").Execute(MergeOption.PreserveChanges);
- // note that the OrderDate is still the client updated value
- ObjectDumper.Write(query);
- }
-
- #endregion
-
- #region Object Context
-
- /*
- [Category("Object Context and Metadata")]
- [Title("Loading and State Management 1")]
- [Description("This sample loads Entities without state management")]
- public void LinqToEntitiesOC1()
- {
- //--- execute a query for some instances without management in the state manager:
- IEnumerable<Category> categories = northwindContext.Categories.Execute(MergeOption.NoTracking);
- //--- print the category and the entity state : the state will be detached which indicates that it is not being managed in the state manager
- foreach (Category category in categories)
- {
- Console.WriteLine("Category Name: {0}; \n - State Manager Reference:{1}", category.CategoryName, category.EntityState);
- }
- }
-
- [Category("Object Context and Metadata")]
- [Title("Loading and State Management 2")]
- [Description("This sample loads Entities with AppendOnly")]
- public void LinqToEntitiesOC2()
- {
- //--- execute a query for some instances append them to the state manager
- IEnumerable<Category> categories = northwindContext.Categories.Execute(MergeOption.AppendOnly);
- //--- 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
- foreach (Category category in categories)
- {
- Console.WriteLine("Category Name: {0}; \n - State Manager Reference:{1}", category.CategoryName, category.EntityState);
- }
- }
-
- [Category("Object Context and Metadata")]
- [Title("Loading and State Management 3")]
- [Description("This sample loads Entities overwriting in-memory edits")]
- public void LinqToEntitiesOC3()
- {
- //--- get a category to change
- Category cat = northwindContext.Categories.ToList().First();
- cat.CategoryName = "wwwww";
- Console.WriteLine("Category changed to have the name: {0} with ID: {1} \n", cat.CategoryName, cat.CategoryID);
-
- //--- execute a query for some instances append them to the state manager
- Console.WriteLine("retrieving categories and overwriting any changes in the state manager...\n");
- IEnumerable<Category> categories = northwindContext.Categories.Execute(MergeOption.OverwriteChanges);
- //--- 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
- foreach (Category category in categories)
- {
- Console.WriteLine("Category ID: {0}, CategoryName: {1}, Entity State:{2}", category.CategoryID, category.CategoryName, category.EntityState);
- }
- }
-
- [Category("Object Context and Metadata")]
- [Title("Loading and State Management 4")]
- [Description("This sample loads Entities saving in-memory edits")]
- public void LinqToEntitiesOC4()
- {
- //--- get a category to change
- Category cat = northwindContext.Categories.ToList().First();
- cat.CategoryName = "wwwww";
- Console.WriteLine("Category changed to have the name: {0} with ID: {1} \n", cat.CategoryName, cat.CategoryID);
-
- //--- execute a query for some instances append them to the state manager
- Console.WriteLine("retrieving categories without overwriting any changes in the state manager...\n");
- IEnumerable<Category> categories = northwindContext.Categories.Execute(MergeOption.PreserveChanges);
- //--- print the category and the entity state : note that the changed entity remains changed
- foreach (Category category in categories)
- {
- Console.WriteLine("Category ID: {0}, CategoryName: {1}, Entity State:{2}", category.CategoryID, category.CategoryName, category.EntityState);
- }
- //--- note we are not invoking save changes so even though the change remains in-memory we wont push this to the store
- //--- calling save changes would push the change to the store but we want to leave the store in its original state
-
- */
- #endregion
- }
-
- }