//The following example performs a join over the Product and SalesOrderHeader tables, grouping the results by contact ID. public void join2() { using (AdventureWorks2014Entities context = new AdventureWorks2014Entities()) { var query = context.Products.Join( context.SalesOrderHeaders, order => order.ProductID, card => card.CreditCardID, (card, order) => new { ProductID = card.ProductID, CreditCardID = order.CreditCardID, TotalDue = order.TotalDue }) .GroupBy(record => record.ProductID); foreach (var group in query) { foreach (var progroup in group) { Console.WriteLine("Product ID: {0}" + "\t CreditCardID ID: {1}" + "\t TotalDue: {2}", progroup.ProductID, progroup.CreditCardID, progroup.TotalDue); } } } }
//The following example uses the Take method to get the first three addresses in Seattle. public void take2() { using (AdventureWorks2014Entities context = new AdventureWorks2014Entities()) { string seattle = "Seattle"; var query = (from address in context.Addresses from order in context.SalesOrderHeaders where address.AddressID == order.Address.AddressID && address.City == seattle select new { City = address.City, OrderID = order.SalesOrderID, OrderDate = order.OrderDate }).Take(3); Console.WriteLine("First 3 order in Seattle"); foreach (var order in query) { Console.WriteLine("City: {0} \t Order ID: {1} \t Order Date: {2}", order.City, order.OrderID, order.OrderDate); } } }
//The following example performs a GroupJoin over the Contact and SalesOrderHeader tables to find the number of orders per contact. //The order count and IDs for each contact are displayed. public void groupjoin2() { using (AdventureWorks2014Entities context = new AdventureWorks2014Entities()) { var query = context.Products.GroupJoin(context.SalesOrderDetails, product => product.ProductID, order => order.ProductID, (product, productgroup) => new { ProductID = product.ProductID, OrderCount = productgroup.Count(), Orders = productgroup }); foreach (var group in query) { Console.WriteLine("ProductID: {0}", group.ProductID); Console.WriteLine("Order Count: {0}", group.OrderCount); foreach (var orderinfo in group.Orders) { Console.WriteLine("Sales ID: {0}", orderinfo.SalesOrderID); } Console.WriteLine(""); } } }
//JOIN //The following example performs a join over the Product and SalesOrderHeader tables. public void join() { using (AdventureWorks2014Entities context = new AdventureWorks2014Entities()) { var query = context.Products.Join( context.SalesOrderHeaders, order => order.ProductID, sales => sales.CreditCardID, (sales, order) => new { ProductID = sales.ProductID, CreditCardID = order.CreditCardID, TotalDue = order.TotalDue }); foreach (var order in query) { Console.WriteLine("Product ID: {0}" + "\t Credit Card ID: {1}" + "\t Total Due: {2}", order.ProductID, order.CreditCardID, order.TotalDue); } } }
//MAX //The following example uses the Max method to get the largest total due. public void max() { using (AdventureWorks2014Entities context = new AdventureWorks2014Entities()) { Decimal maxtotaldue = context.SalesOrderHeaders.Max(m => m.TotalDue); Console.WriteLine("The Maximum TotalDue is {0}", maxtotaldue); } }
//MIN //The following example uses the Min method to get the smallest total due. public void min() { using (AdventureWorks2014Entities context = new AdventureWorks2014Entities()) { Decimal smalltotaldue = context.SalesOrderHeaders.Min(total => total.TotalDue); Console.WriteLine("The Smallest TotalDue is {0}", smalltotaldue); } }
//coUNT //The following example uses the Count method to return the number of products in the Product table. public void count() { using (AdventureWorks2014Entities context = new AdventureWorks2014Entities()) { int numProduct = context.Products.Count(); Console.WriteLine("There are {0} products.", numProduct); } }
//SUM //The following example uses the Min method to get the orders with the smallest total due for each contact. public void sum() { using (AdventureWorks2014Entities context = new AdventureWorks2014Entities()) { double totalorderqty = context.SalesOrderDetails.Sum(o => o.OrderQty); Console.WriteLine("There are {0} total of OrderQty", totalorderqty); } }
//LONG COUNT //The following example gets the product count as a long integer. public void longcount() { using (AdventureWorks2014Entities context = new AdventureWorks2014Entities()) { long productnumber = context.Products.LongCount(); Console.WriteLine("There are {0} Products", productnumber); } }
//The following example uses the Average method to find the average list price of the products of each style. public void Average3() { using (AdventureWorks2014Entities context = new AdventureWorks2014Entities()) { Decimal averageTotalDue = context.SalesOrderHeaders.Average(o => o.TotalDue); Console.WriteLine("The average TotalDue is {0} ", averageTotalDue); } }
//TO DICTIONARY //The following example uses the ToDictionary method to immediately //evaluate a sequence and a related key expression into a dictionary. public void todictionary() { using (AdventureWorks2014Entities context = new AdventureWorks2014Entities()) { Dictionary <String, Product> ScoreRecordDic = context.Products. ToDictionary(record => record.Name); Console.WriteLine("Top Tube's ProductID: {0}", ScoreRecordDic["Top Tube"].Name); } }
//The following example uses the Average method to find the average list price of the products. public void Average() { using (AdventureWorks2014Entities context = new AdventureWorks2014Entities()) { Decimal averageListPrice = context.Products.Average(p => p.ListPrice); Console.WriteLine("The average list price of all the product is ${0}", averageListPrice); } }
//TAKE //The following example uses the Take method to get only the first five contacts from the Contact table. public void take() { using (AdventureWorks2014Entities context = new AdventureWorks2014Entities()) { Console.WriteLine("First 5 Product"); foreach (Product product in context.Products.Take(5)) { Console.WriteLine("Product ID: {0} \t Product Number: {1}", product.ProductID, product.ProductNumber); } } }
//FIRST //The following example uses the First method to find the first e-mail address that starts with 'caroline'. public void first() { string name = "mountain"; using (AdventureWorks2014Entities context = new AdventureWorks2014Entities()) { Product query = context.Products.First( prod => prod.Name.StartsWith(name)); Console.WriteLine("A Product starting with 'Mountain': {0}", query.Name); } }
//WHERE COUNTAINS //The following example uses an array as part of a Where…Contains clause to find //all products that have a ProductModelID that matches a value in the array. public void contains1() { using (AdventureWorks2014Entities AWEntities = new AdventureWorks2014Entities()) { int?[] productModelIds = { 19, 26, 118 }; var products = AWEntities.Products .Where(p => productModelIds.Contains(p.ProductModelID)); foreach (var p in products) { Console.WriteLine("ModelID: {0} = ID: {1}", p.ProductModelID, p.ProductID); } } }
//SELECT //The following example uses the Select method to return all the rows from the Product table and display the product names. public void select() { using (AdventureWorks2014Entities context = new AdventureWorks2014Entities()) { IQueryable <Product> ProdQeury = from product in context.Products select product; Console.WriteLine("Product Names: "); Thread.Sleep(1000); foreach (var prod in ProdQeury) { Console.WriteLine(prod.Name); } } }
//TO LIST //The following example uses the ToList method to immediately evaluate a sequence into a List<T>, where T is of type DataRow. public void tolist() { using (AdventureWorks2014Entities context = new AdventureWorks2014Entities()) { List <Product> query = (from product in context.Products orderby product.Name select product).ToList(); Console.WriteLine("The Product list, ordered by product name: "); foreach (Product product in query) { Console.WriteLine(product.Name.ToLower(CultureInfo.InvariantCulture)); } } }
//The following example uses Select to return a sequence of only product names. public void select2() { using (AdventureWorks2014Entities context = new AdventureWorks2014Entities()) { IQueryable <string> Prodname = from p in context.Products select p.Name; Console.WriteLine("Product Names: "); Thread.Sleep(1000); foreach (String prod in Prodname) { Console.WriteLine(prod); } } }
//ASCENDING //he following example in method-based query syntax uses OrderBy and ThenBy to return //a list of contacts ordered by last name and then by first name. public void thenby1() { using (AdventureWorks2014Entities context = new AdventureWorks2014Entities()) { IQueryable <ContactType> sortedContact = context.ContactTypes .OrderBy(c => c.Name) .ThenBy(c => c.ModifiedDate); Console.WriteLine("The list of the contact sorted by Name then Modified Date: "); foreach (ContactType sorted in sortedContact) { Console.WriteLine(sorted.Name + "," + sorted.ModifiedDate); } } }
//TO ARRAY //The following example uses the ToArray method to immediately evaluate a sequence into an array. public void toarray() { using (AdventureWorks2014Entities context = new AdventureWorks2014Entities()) { Product[] prodArray = ( from product in context.Products orderby product.ListPrice descending select product).ToArray(); Console.WriteLine("Price From Higher to Lowest"); foreach (Product p in prodArray) { Console.WriteLine("Product Name: {0} \t Price: {1}", p.Name, p.ListPrice); } } }
//WHERE //The following example returns all online orders. public void where1() { using (AdventureWorks2014Entities context = new AdventureWorks2014Entities()) { var onlineOrder = context.SalesOrderHeaders .Where(order => order.OnlineOrderFlag == true) .Select(s => new { s.SalesOrderID, s.OrderDate, s.SalesOrderNumber }); foreach (var oo in onlineOrder) { Console.WriteLine("Order ID: {0} Order Date: {1} Order Number: {2}" , oo.SalesOrderID , oo.OrderDate , oo.SalesOrderNumber); } } }
//DESCENDING //The following example uses the OrderBy and ThenByDescending methods to first sort by list price, //and then perform a descending sort of the product names. public void thenbydescending() { using (AdventureWorks2014Entities context = new AdventureWorks2014Entities()) { IQueryable <Product> query = context.Products .OrderBy(p => p.ListPrice) .ThenByDescending(p => p.Name); foreach (Product product in query) { Console.WriteLine("Product ID: {0} Product Name: {1} List Price {2}" , product.ProductID , product.Name , product.ListPrice); } } }
//GROUP //The following example uses the GroupBy method to return Address objects that are grouped by postal code. //The results are projected into an anonymous type. public void group() { using (AdventureWorks2014Entities context = new AdventureWorks2014Entities()) { var query = context.Addresses .GroupBy(address => address.PostalCode); foreach (IGrouping <string, Address> addressGroup in query) { Console.WriteLine("Postal Code: {0}", addressGroup.Key); foreach (Address add in addressGroup) { Console.WriteLine("\t" + add.AddressLine1 + add.AddressLine2); } } } }
//SKIP //The following example uses the Skip method to get all but the first five contacts of the Contact table. public void skip() { using (AdventureWorks2014Entities context = new AdventureWorks2014Entities()) { //LINQ to entities only supports SKIP on ordered collections IOrderedQueryable <Product> product = context.Products .OrderBy(p => p.ListPrice); IQueryable <Product> AllAboutFirst3Product = product.Skip(3); Console.WriteLine("All about first 3 product: "); foreach (Product productS in AllAboutFirst3Product) { Console.WriteLine("Name: {0} \t ID: {1}", productS.Name, productS.ProductID); } } }
//The following example uses the Average method to find the average list price of the products of each style. public void Average2() { using (AdventureWorks2014Entities context = new AdventureWorks2014Entities()) { var query = from product in context.Products group product by product.Style into g select new { style = g.Key, averageListPrice = g.Average(s => s.ListPrice) }; foreach (var prodyct in query) { Console.WriteLine("Product Style: {0} \t Average List Price: {1}", prodyct.style, prodyct.averageListPrice); } } }
//The following example uses the Count method to return a list of Product and how many orders each has. public void count2() { using (AdventureWorks2014Entities context = new AdventureWorks2014Entities()) { var query = from product in context.Products select new { productID = context.Products, orderCount = context.SalesOrderHeaders.Count() }; foreach (var product in query) { Console.WriteLine("Product = {0} \t Order Count = {1}", product.productID, product.orderCount); } } }
//The following example groups products by color and uses the Count method to return the number of products in each color group. public void count3() { using (AdventureWorks2014Entities context = new AdventureWorks2014Entities()) { var query = from products in context.Products group products by products.Color into g select new { color = g.Key, productcount = g.Count() }; foreach (var product in query) { Console.WriteLine("Color = {0} \t\t Product Count = {1}", product.color, product.productcount); } } }
//The following example declares and initializes arrays in a Where…Contains clause to find //all products that have a ProductModelID or a Size that matches a value in the arrays. public void contains2() { using (AdventureWorks2014Entities AWEntities = new AdventureWorks2014Entities()) { var products = AWEntities.Products .Where(p => (new int?[] { 19, 26, 18 }).Contains(p.ProductModelID) || (new string[] { "L", "XL", "M", "S" }).Contains(p.Size)); foreach (var P in products) { Console.WriteLine("ID {0}: ModelID {1}, Size {2}" , P.ProductID , P.ProductModelID , P.Size); } } }
//The following example uses the Sum method to get the total due for each Creditcard ID. public void sum1() { using (AdventureWorks2014Entities context = new AdventureWorks2014Entities()) { var query = from order in context.SalesOrderHeaders group order by order.CreditCardID into g select new { category = g.Key, totaldue = g.Sum(a => a.TotalDue) }; foreach (var order in query) { Console.WriteLine("CreditCardID {0} \t TotalDue Sum : {1}", order.category, order.totaldue); } } }
//The following example uses the Min method to get the smallest total due for each contact ID. public void min2() { using (AdventureWorks2014Entities context = new AdventureWorks2014Entities()) { var query = from order in context.SalesOrderHeaders group order by order.CreditCardID into g select new { category = g.Key, smalltotaldue = g.Min(total => total.TotalDue) }; foreach (var order in query) { Console.WriteLine("CreditCard ID {0} \t Minimum TotalDUe = {1}", order.category, order.smalltotaldue); } } }