/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
- //Copyright (C) Microsoft Corporation. All rights reserved.
-
- 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 EntityNorthwind;
- using System.Windows.Forms;
- using System.Data.SqlClient;
-
-
- // version erickt1
-
- namespace LINQToEntitiesSampleQueries
- {
- [Title("101 LINQ to Entities Samples")]
- [Prefix("LinqToEntities")]
- class LinqToEntitiesSamples : SampleHarness
- {
-
- private readonly static string dbPath = Path.GetFullPath(Path.Combine(Application.StartupPath, @"..\..\Data\NORTHWND.MDF"));
-
- private readonly static string sqlServerInstance = @".\SQLEXPRESS";
-
- private readonly static string sqlConnString = "Server=\"" + sqlServerInstance + "\";" + "AttachDBFileName=\"" + dbPath + "\";user instance=true;Integrated Security=SSPI;";
- static string connstr = @"Metadata=.\LINQ to Entities;Provider=System.Data.SqlClient;Provider Connection String='" + sqlConnString + "'";
-
- EntityNorthwindContext northwindContext;
-
- public LinqToEntitiesSamples()
- {
- northwindContext = new EntityNorthwindContext(connstr);
- }
-
- #region Restriction Operators
- [Category("Restriction Operators")]
- [Title("Where - Simple 1")]
- [Description("This sample uses WHERE to find all customers in Seattle.")]
- public void LinqToEntities1()
- {
-
- var query = from cust in northwindContext.Customers
- where cust.City == "Seattle"
- select cust;
-
- ObjectDumper.Write(query);
- }
-
-
- [Category("Restriction Operators")]
- [Title("Where - Simple 2")]
- [Description("This sample uses WHERE to find all orders placed in 1994.")]
- public void LinqToEntities2()
- {
- DateTime dt = new DateTime(1994, 1, 1);
- var query = from order in northwindContext.Orders
- where order.OrderDate > dt
- select order;
-
- ObjectDumper.Write(query);
- }
-
- [Category("Restriction Operators")]
- [Title("Where - Simple 3")]
- [Description("This sample uses WHERE to filter for Products that have stock below their reorder level and have a units on order of zero.")]
- public void LinqToEntities3()
- {
- var query = from p in northwindContext.Products
- where p.UnitsInStock < p.ReorderLevel && p.UnitsOnOrder == 0
- select p;
-
- ObjectDumper.Write(query);
- }
-
-
- [Category("Restriction Operators")]
- [Title("Where - Simple 4")]
- [Description("This sample uses WHERE to filter out Products that have a UnitPrice less than 10.")]
- public void LinqToEntities4()
- {
- var query = from p in northwindContext.Products
- where p.UnitPrice < 10
- select p;
-
- ObjectDumper.Write(query);
- }
-
- [Category("Restriction Operators")]
- [Title("Where - Related Entities 1")]
- [Description("This sample uses WHERE to get orders for Customers in Mexico.")]
- public void LinqToEntities5()
- {
- var query = from o in northwindContext.Orders
- where o.Customer.Country == "Mexico"
- select o;
-
- ObjectDumper.Write(query);
- }
-
- [Category("Restriction Operators")]
- [Title("Where - Related Entities 2")]
- [Description("This sample uses WHERE to get orders sold by employees in the UK.")]
- public void LinqToEntities6()
- {
- var query = from o in northwindContext.Orders
- where o.Employee.Country == "UK"
- select o;
-
- ObjectDumper.Write(query);
- }
-
- [Category("Restriction Operators")]
- [Title("Any - 1")]
- [Description("This sample uses ANY to get employees have sold an order.")]
- public void LinqToEntities7()
- {
- var query = from e in northwindContext.Employees
- where e.Orders.Any(o => o != null)
- select e;
-
- ObjectDumper.Write(query);
- }
-
- [Category("Restriction Operators")]
- [Title("Any - 2")]
- [Description("This sample uses ANY to check for any out-of-stock products.")]
- public void LinqToEntities8()
- {
- var query = northwindContext
- .Suppliers
- .Where(s => s.Product
- .Any(p => p.UnitsInStock == 0))
- .Select(s => s);
-
- ObjectDumper.Write(query);
- }
-
-
- [Category("Restriction Operators")]
- [Title("Any - Related Entities")]
- [Description("This sample uses WHERE and ANY to get employees who sold an order to any customer in Mexico.")]
- public void LinqToEntities9()
- {
- var query = from e in northwindContext.Employees
- where e.Orders.Any(o => o.Customer.Country == "Mexico")
- select e;
-
- ObjectDumper.Write(query);
- }
-
- [Category("Restriction Operators")]
- [Title("All - Simple")]
- [Description("This sample uses ALL to get employees who sold orders only to customers not in Canada.")]
- public void LinqToEntities10()
- {
- var query = from e in northwindContext.Employees
- where e.Orders.All(o => o.Customer.Country != "Canada")
- select e;
-
- ObjectDumper.Write(query);
- }
-
- #endregion
-
- #region Projection Operators
-
- [Category("Projection Operators")]
- [Title("Select - Simple 1")]
- [Description("This samples uses SELECT to get all Customers as Entity Objects.")]
- public void LinqToEntities11()
- {
- var query = from c in northwindContext.Customers
- select c;
-
- ObjectDumper.Write(query);
- }
-
- [Category("Projection Operators")]
- [Title("Select - Simple 2")]
- [Description("This samples uses SELECT to get all Customer Contact Names as Strings.")]
- public void LinqToEntities12()
- {
- var query = from c in northwindContext.Customers
- select c.ContactName;
-
- ObjectDumper.Write(query);
- }
-
- [Category("Projection Operators")]
- [Title("Select - Anonymous 1")]
- [Description("This samples uses SELECT to get all Customer Contact Names as an anonoymous type.")]
- public void LinqToEntities13()
- {
- var query = from c in northwindContext.Customers
- select new { c.ContactName };
-
- ObjectDumper.Write(query);
- }
-
-
- [Category("Projection Operators")]
- [Title("Select - Anonymous 2")]
- [Description("This sample uses SELECT to get all Orders as anonymous type")]
- public void LinqToEntities14()
- {
- var query = from o in northwindContext.Orders
- select new { o };
-
- ObjectDumper.Write(query, 1);
- }
-
- [Category("Projection Operators")]
- [Title("Select - Anonymous 3")]
- [Description("This sample uses SELECT to get all Orders and associated Employees as anonymous type")]
- public void LinqToEntities15()
- {
- var query = from o in northwindContext.Orders
- select new { o, o.Employee };
-
- ObjectDumper.Write(query, 1);
- }
-
- [Category("Projection Operators")]
- [Title("Select - Nested Collection ")]
- [Description("This sample uses SELECT to get all Customers, and those Orders for each customer with a freight > 5")]
- public void LinqToEntities15a()
- {
- var query = northwindContext.Customers.Select(c => new { Customer = c, Orders = c.Orders.Where(o => o.Freight > 5) });
-
- ObjectDumper.Write(query, 1);
- }
-
-
- [Category("Projection Operators")]
- [Title("SelectMany - Simple 1")]
- [Description("This sample uses SELECTMANY to get all Orders for a Customer as a flat result")]
- public void LinqToEntities16()
- {
- var query = from c in northwindContext.Customers
- where c.CustomerID == "ALFKI"
- from o in c.Orders
- select o;
-
- ObjectDumper.Write(query);
- }
-
- [Category("Projection Operators")]
- [Title("SelectMany - Simple 2")]
- [Description("This sample uses SELECTMANY to get all Orders for a Customer as a flat result using LINQ operators")]
- public void LinqToEntities17()
- {
- var query = northwindContext.Customers.Where(cust => cust.CustomerID == "ALFKI")
- .SelectMany(cust => cust.Orders);
-
- ObjectDumper.Write(query);
- }
-
- [Category("Projection Operators")]
- [Title("SelectMany - Simple 3")]
- [Description("This sample uses SELECTMANY to get all Orders for Customers in Denmark as a flat result")]
- public void LinqToEntities18()
- {
- var query = from c in northwindContext.Customers
- where c.Country == "Denmark"
- from o in c.Orders
- select o;
-
- ObjectDumper.Write(query);
- }
-
- [Category("Projection Operators")]
- [Title("SelectMany - Simple 4")]
- [Description("This sample uses SELECTMANY to get all Orders for Customers in Denmark as a flat result using LINQ operators")]
- public void LinqToEntities19()
- {
- var query = northwindContext.Customers.Where(cust => cust.Country == "Denmark")
- .SelectMany(cust => cust.Orders);
-
- ObjectDumper.Write(query);
- }
-
-
- [Category("Projection Operators")]
- [Title("SelectMany - Predicate 1")]
- [Description("This sample uses SELECTMANY to get all Orders for Customers in Denmark as a flat result")]
- public void LinqToEntities20()
- {
- var query = from c in northwindContext.Customers
- where c.Country == "Denmark"
- from o in c.Orders
- where o.Freight > 5
- select o;
-
- ObjectDumper.Write(query);
- }
-
- [Category("Projection Operators")]
- [Title("SelectMany - Predicate 2")]
- [Description("This sample uses SELECTMANY to get all Orders for Customers in Denmark as an anonymous type containing the Orders and Customer flat result")]
- public void LinqToEntities21()
- {
- var query = from c in northwindContext.Customers
- where c.Country == "Denmark"
- from o in c.Orders
- where o.Freight > 5
- select new { c, o };
-
- ObjectDumper.Write(query);
- }
-
- [Category("Projection Operators")]
- [Title("SelectMany - Predicate 3")]
- [Description("This sample uses SELECTMANY to get all Orders for Customers in Denmark as a flat result using LINQ opeartors")]
- public void LinqToEntities22()
- {
- var query = northwindContext.Customers.Where(cust => cust.Country == "Denmark")
- .SelectMany(cust => cust.Orders.Where(o => o.Freight > 5));
-
- ObjectDumper.Write(query);
- }
-
- #endregion
-
- #region Aggregate Operators
-
- [Category("Aggregate Operators")]
- [Title("Count - Simple")]
- [Description("This sample uses COUNT to get the number of Orders.")]
- public void LinqToEntities23()
- {
- var query = northwindContext.Orders.Count();
-
- ObjectDumper.Write(query);
- }
-
-
- [Category("Aggregate Operators")]
- [Title("Count - Predicate 1")]
- [Description("This sample uses COUNT to get the number of Orders placed by Customers in Mexico.")]
- public void LinqToEntities24()
- {
- var query = northwindContext.Orders.Where(o => o.Customer.Country == "Mexico").Count();
-
- ObjectDumper.Write(query);
- }
-
- [Category("Aggregate Operators")]
- [Title("Count - Predicate 2")]
- [Description("This sample uses COUNT to get the number of Orders shipped to Mexico.")]
- public void LinqToEntities25()
- {
- var query = northwindContext.Orders
- .Where(o => o.ShipCountry == "Mexico").Count();
-
- ObjectDumper.Write(query);
- }
-
- [Category("Aggregate Operators")]
- [Title("Sum - Simple 1")]
- [Description("This sample uses SUM to find the total freight over all Orders.")]
- public void LinqToEntities26()
- {
- var query = northwindContext.Orders.Select(o => o.Freight).Sum();
-
- ObjectDumper.Write(query);
- }
-
- [Category("Aggregate Operators")]
- [Title("Sum - Simple 2")]
- [Description("This sample uses SUM to find the total number of units on order over all Products.")]
- public void LinqToEntities27()
- {
- var query = northwindContext.Products.Sum(p => p.UnitsOnOrder);
-
- ObjectDumper.Write(query);
- }
-
- [Category("Aggregate Operators")]
- [Title("Sum - Simple 3")]
- [Description("This sample uses SUM to find the total number of units on order over all Products out-of-stock.")]
- public void LinqToEntities28()
- {
- var query = northwindContext.Products.Where(p => p.UnitsInStock == 0).Sum(p => p.UnitsOnOrder);
-
- ObjectDumper.Write(query);
- }
-
-
- [Category("Aggregate Operators")]
- [Title("Min - Simple 1")]
- [Description("This sample uses MIN to find the lowest unit price of any Product.")]
- public void LinqToEntities29()
- {
- var query = northwindContext.Products.Select(p => p.UnitPrice).Min();
-
- ObjectDumper.Write(query);
- }
-
- [Category("Aggregate Operators")]
- [Title("Min - Simple 2")]
- [Description("This sample uses MIN to find the lowest freight of any Order.")]
- public void LinqToEntities30()
- {
- var query = northwindContext.Orders.Min(o => o.Freight);
-
- ObjectDumper.Write(query);
- }
-
- [Category("Aggregate Operators")]
- [Title("Min - Predicate")]
- [Description("This sample uses MIN to find the lowest freight of any Order shipped to Mexico.")]
- public void LinqToEntities31()
- {
- var query = northwindContext.Orders.Where(o => o.ShipCountry == "Mexico").Min(o => o.Freight);
-
- ObjectDumper.Write(query);
- }
-
-
- [Category("Aggregate Operators")]
- [Title("Min - Grouping")]
- [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.")]
- public void LinqToEntities32()
- {
-
- var query = from p in northwindContext.Products
- group p by p.Category into g
- select new
- {
- CategoryID = g.Key,
- CheapestProducts =
- from p2 in g
- where p2.UnitPrice == g.Min(p3 => p3.UnitPrice)
- select p2
- };
-
- ObjectDumper.Write(query, 1);
- }
-
- [Category("Aggregate Operators")]
- [Title("Max - Simple 1")]
- [Description("This sample uses MAX to find the latest hire date of any Employee.")]
- public void LinqToEntities33()
- {
- var query = northwindContext.Employees.Select(e => e.HireDate).Max();
-
- ObjectDumper.Write(query);
- }
-
- [Category("Aggregate Operators")]
- [Title("Max - Simple 2")]
- [Description("This sample uses MAX to find the most units in stock of any Product.")]
- public void LinqToEntities34()
- {
- var query = northwindContext.Products.Max(p => p.UnitsInStock);
-
- ObjectDumper.Write(query);
- }
-
- [Category("Aggregate Operators")]
- [Title("Max - Predicate")]
- [Description("This sample uses MAX to find the most units in stock of any Product with CategoryID = 1.")]
- public void LinqToEntities35()
- {
- var query = northwindContext.Products.Where(p => p.Category.CategoryID == 1).Max(p => p.UnitsInStock);
- ObjectDumper.Write(query);
- }
-
- [Category("Aggregate Operators")]
- [Title("Max - Grouping")]
- [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.")]
- public void LinqToEntities36()
- {
- var query = from p in northwindContext.Products
- group p by p.Category into g
- select new
- {
- g.Key,
- MostExpensiveProducts =
- from p2 in g
- where p2.UnitPrice == g.Max(p3 => p3.UnitPrice)
- select p2
- };
-
- ObjectDumper.Write(query, 1);
- }
-
- [Category("Aggregate Operators")]
- [Title("Average - Simple 1")]
- [Description("This sample uses AVERAGE to find the average freight of all Orders.")]
- public void LinqToEntities37()
- {
- var query = northwindContext.Orders.Select(o => o.Freight).Average();
-
- ObjectDumper.Write(query);
- }
-
- [Category("Aggregate Operators")]
- [Title("Average - Simple 2")]
- [Description("This sample uses AVERAGE to find the average unit price of all Products.")]
- public void LinqToEntities38()
- {
- var query = northwindContext.Products.Average(p => p.UnitPrice);
-
- ObjectDumper.Write(query);
- }
-
- [Category("Aggregate Operators")]
- [Title("Average - Predicate")]
- [Description("This sample uses AVERAGE to find the average unit price of all Products with CategoryID = 1.")]
- public void LinqToEntities39()
- {
- var query = northwindContext.Products.Where(p => p.Category.CategoryID == 1).Average(p => p.UnitPrice);
-
- ObjectDumper.Write(query);
- }
-
- [Category("Aggregate Operators")]
- [Title("Average - Grouping 1")]
- [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.")]
- public void LinqToEntities40()
- {
- var query = from p in northwindContext.Products
- group p by p.Category into g
- select new
- {
- g.Key,
- ExpensiveProducts =
- from p2 in g
- where p2.UnitPrice > g.Average(p3 => p3.UnitPrice)
- select p2
- };
-
- ObjectDumper.Write(query, 1);
- }
-
- [Category("Aggregate Operators")]
- [Title("Average - Grouping 2")]
- [Description("This sample uses AVERAGE to find the average unit price of each category.")]
- public void LinqToEntities41()
- {
- var query = from p in northwindContext.Products
- group p by p.Category into g
- select new
- {
- g.Key,
- Average = g.Average(p => p.UnitPrice)
- };
-
- ObjectDumper.Write(query, 1);
- }
-
- #endregion
-
- #region Set Operators
-
- [Category("Set And Element Operators")]
- [Title("First - Simple")]
- [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.")]
- public void LinqToEntities42()
- {
- var query = from o in northwindContext.Orders
- where o.ShipCity == "Seattle"
- select o;
-
- // Feb CTP requires AsEnumerable()
- var result = query.AsEnumerable().First();
-
- ObjectDumper.Write(result);
- }
-
- [Category("Set And Element Operators")]
- [Title("First - Predicate")]
- [Description("This sample uses FIRST to get the first (database order) order that is shipped to Seattle. The predicate is evaluated on the client.")]
- public void LinqToEntities43()
- {
- var query = from o in northwindContext.Orders
- select o;
-
- // Feb CTP requires AsEnumerable()
- var result = query
- .AsEnumerable()
- .First(x => x.ShipCity == "Seattle");
-
- ObjectDumper.Write(result);
- }
-
- [Category("Set And Element Operators")]
- [Title("First - Ordered")]
- [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.")]
- public void LinqToEntities44()
- {
- var query = from o in northwindContext.Orders
- where o.ShipCity == "Seattle"
- orderby o.OrderDate
- select o;
-
- // Feb CTP requires AsEnumerable()
- var result = query.AsEnumerable().First();
-
- ObjectDumper.Write(result);
- }
-
-
- [Category("Set And Element Operators")]
- [Title("Distinct - Simple")]
- [Description("This sample uses DISTINCT to get all the categories of products.")]
- public void LinqToEntities45()
- {
- var query = northwindContext.Products.Select(o => o.Category).Distinct();
-
- ObjectDumper.Write(query);
- }
-
- [Category("Set And Element Operators")]
- [Title("Union - Simple")]
- [Description("This sample uses UNION to get all the orders where the shipping country was Mexico or Canada.")]
- public void LinqToEntities46()
- {
- var mexico = northwindContext.Orders.Where(o => o.ShipCountry == "Mexico").Select(o => o);
- var canada = northwindContext.Orders.Where(o => o.ShipCountry == "Canada").Select(o => o);
- var query = mexico.Union(canada);
-
- ObjectDumper.Write(query);
- }
-
- [Category("Set And Element Operators")]
- [Title("Union - With Distinct")]
- [Description("This sample uses UNION and DISTINCT to get all the employees from orders where the shipping country was Mexico or Canada.")]
- public void LinqToEntities47()
- {
- var mexico = northwindContext.Orders.Where(o => o.ShipCountry == "Mexico").Select(o => o);
- var canada = northwindContext.Orders.Where(o => o.ShipCountry == "Canada").Select(o => o);
- var union = mexico.Union(canada).Select(o => o.Employee);
-
- var query = union.Distinct();
-
- ObjectDumper.Write(query);
- }
-
- [Category("Set And Element Operators")]
- [Title("Concat - Simple")]
- [Description("This sample uses CONCAT to get all orders where the shipping country was Mexico or Canada.")]
- public void LinqToEntities48()
- {
- var mexico = northwindContext.Orders.Where(o => o.ShipCountry == "Mexico").Select(o => o);
- var canada = northwindContext.Orders.Where(o => o.ShipCountry == "Canada").Select(o => o);
-
- var query = mexico.Concat(canada);
-
- ObjectDumper.Write(query);
- }
-
- [Category("Set And Element Operators")]
- [Title("Intersect - Simple 1")]
- [Description("This sample uses INTERSECT to get common employees where an order was shipped to Mexico or Canada.")]
- public void LinqToEntities49()
- {
- var mexico = northwindContext.Orders.Where(o => o.ShipCountry == "Mexico").Select(o => o.Employee);
- var canada = northwindContext.Orders.Where(o => o.ShipCountry == "Canada").Select(o => o.Employee);
-
- var query = mexico.Intersect(canada);
-
- ObjectDumper.Write(query);
- }
-
- [Category("Set And Element Operators")]
- [Title("Intersect - Simple 2")]
- [Description("This sample uses INTERSECT to get common employees where an order was shipped to Mexico or Canada in one consolidated query.")]
- public void LinqToEntities50()
- {
- 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));
-
- ObjectDumper.Write(query);
- }
-
- [Category("Set And Element Operators")]
- [Title("Except - Simple 1")]
- [Description("This sample uses EXCEPT to get employees who shipped orders to Mexico but not Canada.")]
- public void LinqToEntities51()
- {
- 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));
-
- ObjectDumper.Write(query);
- }
-
- [Category("Set And Element Operators")]
- [Title("Except - Simple 2")]
- [Description("This sample uses EXCEPT to get employees with no orders sent to Mexico.")]
- public void LinqToEntities52()
- {
- var query = northwindContext.Employees.Select(e => e)
- .Except(northwindContext.Orders.Where(o => o.ShipCountry == "Mexico").Select(o => o.Employee));
-
- ObjectDumper.Write(query);
- }
-
-
- #endregion
-
-
- #region Ordering and Grouping
-
- [Category("Ordering and Grouping")]
- [Title("OrderBy - Simple 1")]
- [Description("Select all customers ordered by ContactName.")]
- public void LinqToEntities53()
- {
- var query = from c in northwindContext.Customers
- orderby c.ContactName
- select c;
-
- ObjectDumper.Write(query);
- }
-
- [Category("Ordering and Grouping")]
- [Title("OrderBy - Simple 2")]
- [Description("Select all customers ordered by ContactName descending.")]
- public void LinqToEntities54()
- {
- var query = from c in northwindContext.Customers
- orderby c.CompanyName descending
- select c;
-
- ObjectDumper.Write(query);
- }
-
- [Category("Ordering and Grouping")]
- [Title("OrderBy - Simple 3")]
- [Description("Select an anonoymous type with all product IDs ordered by UnitInStock.")]
- public void LinqToEntities55()
- {
- var query = from p in northwindContext.Products
- orderby p.UnitsInStock
- select new { p.ProductID };
-
- ObjectDumper.Write(query);
- }
-
- [Category("Ordering and Grouping")]
- [Title("OrderBy - Simple 4")]
- [Description("Select an anonoymous type with all product IDs ordered by UnitInStock using LINQ operators.")]
- public void LinqToEntities56()
- {
- var query = northwindContext.Products.OrderBy(p => p.UnitsInStock)
- .Select(p2 => new { p2.ProductID });
-
- ObjectDumper.Write(query);
- }
-
-
- [Category("Ordering and Grouping")]
- [Title("OrderByDescending - Simple 1")]
- [Description("Select all customers ordered by the descending region.")]
- public void LinqToEntities57()
- {
- var query = from c in northwindContext.Customers
- orderby c.Region descending
- select c;
-
- ObjectDumper.Write(query);
- }
-
- [Category("Ordering and Grouping")]
- [Title("OrderByDescending - Simple 2")]
- [Description("Select all customers ordered by the descending region using LINQ operators.")]
- public void LinqToEntities58()
- {
- var query = northwindContext.Customers.Select(c => c).OrderByDescending(c2 => c2.Region);
-
- ObjectDumper.Write(query);
- }
-
- [Category("Ordering and Grouping")]
- [Title("OrderBy with ThenBy")]
- [Description("Select all customers ordered by the region, then the contact name.")]
- public void LinqToEntities59()
- {
- var query = northwindContext.Customers.Select(c => c).OrderBy(c => c.Region).ThenBy(c => c.ContactName);
-
- ObjectDumper.Write(query);
- }
-
- [Category("Ordering and Grouping")]
- [Title("OrderByDescending with ThenBy")]
- [Description("Select all customers ordered by the region in descending order, then the contact name.")]
- public void LinqToEntities60()
- {
- var query = northwindContext.Customers.Select(c => c).OrderByDescending(c => c.Region).ThenBy(c => c.ContactName);
-
- ObjectDumper.Write(query);
- }
-
- [Category("Ordering and Grouping")]
- [Title("OrderBy with ThenByDescending")]
- [Description("Select all customers ordered by the region then the contact name in descending order.")]
- public void LinqToEntities61()
- {
- var query = northwindContext.Customers.Select(c => c).OrderBy(c => c.Region).ThenByDescending(c => c.ContactName);
-
- ObjectDumper.Write(query);
- }
-
-
- [Category("Ordering and Grouping")]
- [Title("OrderByDescending - Simple 3")]
- [Description("Select all products ordered by the descending unit price.")]
- public void LinqToEntities62()
- {
- var query = from p in northwindContext.Products
- orderby p.UnitPrice descending
- select p;
-
- ObjectDumper.Write(query);
- }
-
-
- [Category("Ordering and Grouping")]
- [Title("OrderBy - FK Collection")]
- [Description("Select all orders for a customer ordered by date that the order was placed.")]
- public void LinqToEntities63()
- {
- var query = northwindContext.Customers.Where(cust => cust.CustomerID == "ALFKI")
- .SelectMany(c => c.Orders.Select(o => o))
- .OrderBy(o2 => o2.OrderDate);
-
- foreach (var order in query)
- {
- ObjectDumper.Write(order);
- }
- }
-
- [Category("Ordering and Grouping")]
- [Title("Grouping - Simple 1")]
- [Description("Select all Regions with a customer.")]
- public void LinqToEntities64()
- {
- var query = from c in northwindContext.Customers
- group c by c.Region into regions
- select new { regions.Key };
-
- ObjectDumper.Write(query);
- }
-
- [Category("Ordering and Grouping")]
- [Title("Grouping - Simple 2")]
- [Description("Select all dates with orders placed.")]
- public void LinqToEntities65()
- {
- var query = from o in northwindContext.Orders
- group o by o.OrderDate into dates
- select new { dates.Key };
-
- ObjectDumper.Write(query);
- }
-
- [Category("Ordering and Grouping")]
- [Title("Grouping - Join 1")]
- [Description("Select all Regions and customer count for each region.")]
- public void LinqToEntities66()
- {
- var query = from c in northwindContext.Customers
- group c by c.Region into regions
- select new { Region = regions.Key, Count = regions.Count() };
-
- ObjectDumper.Write(query);
- }
-
- [Category("Ordering and Grouping")]
- [Title("Grouping on Key")]
- [Description("Select all Regions and customer count for each region using LINQ operator.")]
- public void LinqToEntities67()
- {
- var query = northwindContext.Customers.GroupBy(c => c.Region).Select(r => new { region = r.Key, count = r.Count() });
-
- ObjectDumper.Write(query);
- }
-
- [Category("Ordering and Grouping")]
- [Title("Grouping with a join on Key 1")]
- [Description("Select all Customer Regions with the total Freight on all orders for Customers in that Region.")]
- public void LinqToEntities68()
- {
- var query = from c in northwindContext.Customers
- group c by c.Region into regions
- join c2 in northwindContext.Customers on regions.Key equals c2.Region
- select new { region = regions.Key, total = c2.Orders.Sum(o => o.Freight) };
-
- ObjectDumper.Write(query);
- }
-
- [Category("Ordering and Grouping")]
- [Title("Grouping with a Key 2")]
- [Description("Select all Customer Regions with the total Freight on all orders for Customers in that Region using LINQ operators.")]
- public void LinqToEntities69()
- {
- var query = northwindContext.Customers.GroupBy(c => c.Region)
- .Select(g => new
- {
- Region = g.Key, FreightTotal = g
- .SelectMany(c2 => c2.Orders)
- .Sum(o => o.Freight)
- });
-
- ObjectDumper.Write(query);
- }
-
-
- #endregion
-
- #region Relationship Navigation
-
- [Category("Relationship Navigation")]
- [Title("Select - FK Collection 1")]
- [Description("Select a sequence of all the orders for a customer using Select.")]
- public void LinqToEntities70()
- {
- var query = northwindContext.Customers.Where(cust => cust.CustomerID == "ALFKI")
- .Select(c => c.Orders.Select(o => o));
-
- foreach (var order in query)
- {
- ObjectDumper.Write(order);
- }
- }
-
- [Category("Relationship Navigation")]
- [Title("Select - FK Collection 2")]
- [Description("Select all the orders for a customer using SelectMany.")]
- public void LinqToEntities71()
- {
- var query = northwindContext.Customers.Where(cust => cust.CustomerID == "ALFKI").SelectMany(c => c.Orders);
-
- ObjectDumper.Write(query);
- }
-
- [Category("Relationship Navigation")]
- [Title("Select - FK Collection property")]
- [Description("Select all Employee IDs, and the count of the their orders.")]
- public void LinqToEntities72()
- {
- var query = from e in northwindContext.Employees
- select new { e, orders = e.Orders.Select(o => o) };
-
- ObjectDumper.Write(query);
- }
-
- /* not enabled for Feb CTP
- [Category("Relationship Navigation")]
- [Title("Select - FK Collection property 2")]
- [Description("Select number of orders placed in 2002 for a customer.")]
- public void LinqToEntities74()
- {
- var query = northwindContext.Customers
- .Where(cust => cust.CustomerID == "ALFKI")
- .SelectMany(c => c.Orders)
- .Where(o => o.OrderDate.Year == 2002);
-
- ObjectDumper.Write(query);
- }
- */
-
- [Category("Relationship Navigation")]
- [Title("Select - FK Collection Aggregate property")]
- [Description("Select a customer and the sum of the freight of thier orders.")]
- public void LinqToEntities73()
- {
- var query = northwindContext.Customers.Where(cust => cust.CustomerID == "ALFKI")
- .Select(c => c.Orders.Sum(o => o.Freight));
-
- ObjectDumper.Write(query);
- }
-
- [Category("Relationship Navigation")]
- [Title("Select - FK collection predicate")]
- [Description("Select customers with an order where the shipping address is the same as the customers.")]
- public void LinqToEntities75()
- {
- var query = northwindContext.Customers.Where(cust => cust.Orders.Any(o => o.ShipAddress == cust.Address)).Select(c2 => c2);
-
- ObjectDumper.Write(query);
- }
-
- [Category("Relationship Navigation")]
- [Title("Select - FK collection Grouping")]
- [Description("Selects all regions with a customer, and shows the sum of orders for customers for each region.")]
- public void LinqToEntities76()
- {
- var query = from c in northwindContext.Customers
- group c by c.Region into regions
- join c2 in northwindContext.Customers on regions.Key equals c2.Region
- select new { region = regions.Key, total = c2.Orders.Sum(o => o.Freight) };
-
- ObjectDumper.Write(query);
- }
-
- #endregion
-
- #region Inheritance
-
- [Category("Table per Hierarchy Inheritance")]
- [Title("One Level Hierarchy - Simple")]
- [Description("Select all products, both active and discontinued products, and shows the type.")]
- public void LinqToEntities77()
- {
- var query = northwindContext
- .Products
- .Select(p => p);
-
- // we need AsEnumerable to force execution, as GetType is not defined in store
- var query2 = query
- .AsEnumerable()
- .Select(p => new { type = p.GetType().ToString(), prod = p });
-
- ObjectDumper.Write(query2);
- }
-
-
- [Category("Table per Hierarchy Inheritance")]
- [Title("One Level Hierarchy - OfType - Simple 1")]
- [Description("Select only discontinued products.")]
- public void LinqToEntities78()
- {
- var query = northwindContext.Products.OfType<DiscontinuedProduct>().Select(p => p);
-
- ObjectDumper.Write(query);
- }
-
-
- [Category("Table per Hierarchy Inheritance")]
- [Title("One Level Hierarchy - OfType - Simple 2")]
- [Description("Select only products, which will reutrn all Products and subtypes of Products (DiscontinuedProducts and ActiveProducts).")]
- public void LinqToEntities79()
- {
- var query = northwindContext.Products.OfType<Product>().Select(p => p);
-
- ObjectDumper.Write(query);
- }
-
- [Category("Table per Hierarchy Inheritance")]
- [Title("One Level Hierarchy - Getting Supertype - OfType")]
- [Description("Select only active products.")]
- public void LinqToEntities80()
- {
- var query = northwindContext.Products.OfType<ActiveProduct>();
-
- ObjectDumper.Write(query);
- }
-
- [Category("Table per Hierarchy Inheritance")]
- [Title("One Level Hierarchy - Getting Supertype - Local")]
- [Description("Select only discontinued products.")]
- public void LinqToEntities81()
- {
- var query = northwindContext.Products.Where(p => p is DiscontinuedProduct);
-
- ObjectDumper.Write(query);
- }
-
-
- [Category("Table per Hierarchy Inheritance")]
- [Title("Complex Hierarchy - Simple")]
- [Description("Select all contacts and show the type of each.")]
- public void LinqToEntities82()
- {
- var query = northwindContext
- .Contacts
- .Select(c => c);
-
- // we need AsEnumerable to force execution, as GetType is not defined in store
- var query2 = query
- .AsEnumerable()
- .Select(c => new { type = c.GetType().ToString() });
-
- ObjectDumper.Write(query2);
- }
-
- [Category("Table per Hierarchy Inheritance")]
- [Title("Complex Hierarchy - OfType 1")]
- [Description("Select all Shipper contacts.")]
- public void LinqToEntities83()
- {
- var query = northwindContext
- .Contacts
- .OfType<ShipperContact>()
- .Select(c => c);
-
- ObjectDumper.Write(query);
- }
-
- [Category("Table per Hierarchy Inheritance")]
- [Title("Complex Hierarchy - OfType 2")]
- [Description("Select all Full contacts, which includes suppliers, customers, and employees.")]
- public void LinqToEntities84()
- {
- var query = northwindContext
- .Contacts
- .OfType<FullContact>()
- .Select(c => c);
-
- ObjectDumper.Write(query);
- }
-
- /* not enabled for Feb CTP
- [Category("Table per Hierarchy Inheritance")]
- [Title("Complex Hierarchy - using supertype")]
- [Description("Select all Customers and Employees, cast as FullContacts to allow join.")]
- public void LinqToEntities85()
- {
- var query = northwindContext
- .Contacts
- .OfType<CustomerContact>()
- .Cast<FullContact>()
- .Union(northwindContext.Contacts.OfType<EmployeeContact>().Cast<FullContact>().Select(ec => ec ))
- .AsEnumerable()
- .Select(c => new {type = c.GetType().ToString(), companyName = c.CompanyName } );
-
- ObjectDumper.Write(query);
- }*/
-
- [Category("Table per Concrete Type Inheritance")]
- [Title("Simple")]
- [Description("Select all federated products and display thier types.")]
- public void LinqToEntities86()
- {
- var query = northwindContext.ProductsFedarated.AsEnumerable().Select(p => new { type = p.GetType().ToString(), p });
-
- ObjectDumper.Write(query);
- }
-
- [Category("Table per Concrete Type Inheritance")]
- [Title("OfType")]
- [Description("Select all discontinued federated products.")]
- public void LinqToEntities87()
- {
- var query = northwindContext.ProductsFedarated.OfType<DiscontinuedProductFedarated>().AsEnumerable().Select(p => new { type = p.GetType().ToString(), p });
-
- ObjectDumper.Write(query);
- }
-
- [Category("Table per Type Inheritance")]
- [Title("Simple")]
- [Description("Select all contacts and shows their types.")]
- public void LinqToEntities88()
- {
- var query = northwindContext.ContactsSplit.AsEnumerable().Select(c => new { type = c.GetType().ToString(), c });
-
- ObjectDumper.Write(query);
- }
-
- [Category("Table per Type Inheritance")]
- [Title("OfType 1")]
- [Description("Select all Customers.")]
- public void LinqToEntities89()
- {
- var query = northwindContext
- .ContactsSplit
- .OfType<CustomerContactSplit>()
- .AsEnumerable()
- .Select(c => new { type = c.GetType().ToString(), c });
-
- ObjectDumper.Write(query);
- }
-
-
- /*
- [Category("Table per Type Inheritance")]
- [Title("OfType 2")]
- [Description("Select all Customers who are also employees, both as the base ContactSplit type (empty set).")]
- public void LinqToEntities90()
- {
- var query = northwindContext
- .ContactsSplit
- .OfType<CustomerContactSplit>()
- .Cast<ContactSplit>()
- .Intersect(northwindContext.ContactsSplit.OfType<EmployeeContactSplit>().Cast<ContactSplit>())
- .AsEnumerable()
- .Select(c => new { type = c.GetType().ToString(), c });
-
- ObjectDumper.Write(query);
- }
- */
- #endregion
-
-
- #region Runtime behavior closure
-
- class MyClass {
- public static decimal Val = 50;
-
- public decimal GetVal()
- {
- return MyClass.Val;
- }
- }
-
- [Category("Runtime behavior example")]
- [Title("Static variable reference")]
- [Description("Uses a local variable as a query parameter.")]
- public void LinqToEntities91()
- {
- MyClass c = new MyClass();
- var query = northwindContext.Orders.Where(o => o.Freight > MyClass.Val).Select(o => new { o.Freight, o });
-
- ObjectDumper.Write(query);
- }
-
- [Category("Runtime behavior example")]
- [Title("Query Parameters")]
- [Description("Uses a the value of the local variable at query execution time.")]
- public void LinqToEntities92()
- {
- decimal x = 50;
-
- var query = northwindContext.Orders.Where(o => o.Freight > x).Select(o => new { o.Freight, o });
-
- x = 100;
-
- ObjectDumper.Write(query);
- }
-
- [Category("Runtime behavior example")]
- [Title("Deferred Execution and Query Parameters")]
- [Description("Each execution uses the current value of the local variable.")]
- public void LinqToEntities93()
- {
- decimal x = 100;
-
- var query = northwindContext.Orders.Where(o => o.Freight > x).Select(o => new { o.Freight, o });
-
- ObjectDumper.Write(x);
- ObjectDumper.Write(query);
-
- x = 200;
- ObjectDumper.Write(x);
- ObjectDumper.Write(query);
- }
-
- #endregion
-
-
- #region Data Manipulation
-
- [Category("Data Manipulation")]
- [Title("Create single Entity")]
- [Description("Create a new customer")]
- public void LinqToEntitiesData1()
- {
- Customer newCust = new Customer();
-
- newCust.CustomerID = "IGERB";
- newCust.ContactName = "Igvar Gerber";
-
- northwindContext.AddObject(newCust);
- northwindContext.SaveChanges();
-
- // display new Entity
- var query = northwindContext.Customers.Where(c => c.CustomerID == "IGERB");
- ObjectDumper.Write(query);
-
- // cleanup
- UndoLinqToEntitiesData1();
- }
-
- void UndoLinqToEntitiesData1()
- {
- var query = northwindContext.Customers.Where(c => c.ContactName == "Igvar Gerber");
-
- foreach (var res in query)
- {
- res.Delete();
- }
- northwindContext.SaveChanges();
- }
-
- [Category("Data Manipulation")]
- [Title("Create multiple Entities")]
- [Description("Create two new customers.")]
- public void LinqToEntitiesData2()
- {
- Customer newCust = new Customer();
-
- newCust.CustomerID = "IGERB";
- newCust.ContactName = "Igvar Gerber";
-
- northwindContext.AddObject(newCust);
-
- Customer newCust2 = new Customer();
- newCust2.CustomerID = "JSMIT";
- newCust2.ContactName = "John Q Smith";
- northwindContext.AddObject(newCust2);
- northwindContext.SaveChanges();
-
- // display new Entity
- var query = northwindContext.Customers.Where(c => c.CustomerID == "IGERB" || c.CustomerID == "JSMIT");
- ObjectDumper.Write(query);
-
- // cleanup
- UndoLinqToEntitiesData2();
- }
-
- void UndoLinqToEntitiesData2()
- {
- var query = northwindContext.Customers.Where(c => c.ContactName == "Igvar Gerber" || c.ContactName == "John Q Smith");
-
- foreach (var res in query)
- {
- res.Delete();
- }
- northwindContext.SaveChanges();
- }
-
- [Category("Data Manipulation")]
- [Title("Create an Entity with multiple tables - 1")]
- [Description("Create a new Employee Contact, which u…
Large files files are truncated, but you can click here to view the full file