//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);
}
}
}
