// Use Orderby or thenby
static void Main(string[] args)
{
// Using ThenBy
//using (AdventureWorks2014Entities entities = new AdventureWorks2014Entities())
//{
// IQueryable<Person> sortedPersons = entities.Person
// .OrderBy(p => p.LastName)
// .ThenBy(p => p.FirstName);
// Console.WriteLine("The list of person sorted by last name then by first name:");
// foreach (Person sortedPerson in sortedPersons)
// {
// Console.WriteLine(sortedPerson.LastName + ", " + sortedPerson.FirstName);
// }
// Console.ReadKey();
//}
// Using ThenByDescending
using (AdventureWorks2014Entities entities = new AdventureWorks2014Entities())
{
IOrderedQueryable <Product> query = entities.Product
.OrderBy(product => product.ListPrice)
.ThenByDescending(product => product.Name);
foreach (Product product in query)
{
Console.WriteLine("Product ID: {0} Product Name: {1} List Price {2}",
product.ProductID,
product.Name,
product.ListPrice);
}
Console.ReadKey();
}
}
static void Main(string[] args)
{
using (AdventureWorks2014Entities entities = new AdventureWorks2014Entities())
{
IQueryable <string> productNames = entities.Product
.Select(p => p.Name);
Console.WriteLine("Product Names:");
foreach (String productName in productNames)
{
Console.WriteLine(productName);
}
Console.ReadKey();
}
//using (AdventureWorks2014Entities entities = new AdventureWorks2014Entities())
//{
// var query = entities.Product
// .Select(product => new
// {
// ProductId = product.ProductID,
// ProductName = product.Name
// });
// Console.WriteLine("Product Info:");
// foreach (var productInfo in query)
// {
// Console.WriteLine("Product Id: {0} Product name: {1} ", productInfo.ProductId, productInfo.ProductName);
// }
// Console.ReadKey();
//}
}
static void Main(string[] args)
{
//using (AdventureWorks2014Entities entities = new AdventureWorks2014Entities())
//{
// var query = entities.Address
// .GroupBy(address => address.PostalCode);
// foreach (IGrouping<string, Address> addressGroup in query)
// {
// Console.WriteLine("Postal Code: {0}", addressGroup.Key);
// foreach (Address address in addressGroup)
// {
// Console.WriteLine("\t" + address.AddressLine1 +
// address.AddressLine2);
// }
// }
// Console.ReadKey();
//}
//using (AdventureWorks2014Entities entities = new AdventureWorks2014Entities())
//{
// var query = entities.Person
// .GroupBy(p => p.LastName.Substring(0, 1))
// .OrderBy(p => p.Key);
// foreach (IGrouping<string, Person> group in query)
// {
// Console.WriteLine("Last names that start with the letter '{0}':",
// group.Key);
// foreach (Person person in group)
// {
// Console.WriteLine(person.LastName);
// }
// }
// Console.ReadKey();
//}
using (AdventureWorks2014Entities entities = new AdventureWorks2014Entities())
{
var query = entities.SalesOrderHeader
.GroupBy(order => order.CustomerID);
foreach (IGrouping <int, SalesOrderHeader> group in query)
{
Console.WriteLine("Customer ID: {0}", group.Key);
Console.WriteLine("Order Count: {0}", group.Count());
foreach (SalesOrderHeader sale in group)
{
Console.WriteLine(" Sale ID: {0}", sale.SalesOrderID);
}
Console.Write("");
}
Console.ReadKey();
}
}
static void Main(string[] args)
{
// SKIP
//using (AdventureWorks2014Entities context = new AdventureWorks2014Entities())
//{
// var addresses = context.Address;
// var orders = context.SalesOrderHeader;
// //LINQ to Entities only supports Skip on ordered collections.
// var query = (from address in addresses
// from order in orders
// where address.AddressID == order.Address.AddressID && address.City == "Seattle"
// orderby order.SalesOrderID
// select new
// {
// City = address.City,
// OrderID = order.SalesOrderID,
// OrderDate = order.OrderDate
// }).Skip(2);
// Console.WriteLine("All but first 2 orders in Seattle:");
// foreach (var order in query)
// {
// Console.WriteLine("City: {0} Order ID: {1} Total Due: {2:d}",
// order.City, order.OrderID, order.OrderDate);
// }
// Console.ReadKey();
//}
// TAKE
String city = "Seattle";
using (AdventureWorks2014Entities context = new AdventureWorks2014Entities())
{
var addresses = context.Address;
var orders = context.SalesOrderHeader;
var query = (from address in addresses
from order in orders
where address.AddressID == order.Address.AddressID && address.City == city
select new
{
City = address.City,
OrderID = order.SalesOrderID,
OrderDate = order.OrderDate
}).Take(3);
Console.WriteLine("First 3 orders in Seattle:");
foreach (var order in query)
{
Console.WriteLine("City: {0} Order ID: {1} Total Due: {2:d}",
order.City, order.OrderID, order.OrderDate);
}
Console.ReadKey();
}
}
static void Main(string[] args)
{
// ToArray
//using (AdventureWorks2014Entities entities = new AdventureWorks2014Entities())
//{
// var products = entities.Product;
// Product[] prodArray = (from product in products
// orderby product.ListPrice descending
// select product).ToArray();
// Console.WriteLine("Every price from highest to lowest:");
// foreach (Product product in prodArray)
// {
// Console.WriteLine(product.ListPrice);
// }
// Console.ReadKey();
//}
// ToDictionary
//using (AdventureWorks2014Entities entities = new AdventureWorks2014Entities())
//{
// var products = entities.Product;
// Dictionary<String, Product> scoreRecordsDict = products.
// ToDictionary(record => record.Name);
// Console.WriteLine("Top Tube's ProductID: {0}",
// scoreRecordsDict["Top Tube"].ProductID);
// Console.ReadKey();
//}
// ToList
using (AdventureWorks2014Entities entities = new AdventureWorks2014Entities())
{
var products = entities.Product;
List <Product> query = (from product in 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));
}
Console.ReadKey();
}
}
static void Main(string[] args)
{
// FIRST
string name = "caroline";
using (AdventureWorks2014Entities entities = new AdventureWorks2014Entities())
{
var email = entities.EmailAddress;
var query = email.First(em => em.EmailAddress1.StartsWith(name));
Console.WriteLine("An email address starting with 'caroline': {0}", query.EmailAddress1);
Console.ReadKey();
}
}
static void Main(string[] args)
{
using (AdventureWorks2014Entities entities = new AdventureWorks2014Entities())
{
var query = entities.Product
.Select(product => new
{
ProductId = product.ProductID,
ProductName = product.Name
});
Console.WriteLine("Product Info:");
foreach (var productInfo in query)
{
Console.WriteLine("Product Id: {0} Product name: {1} ", productInfo.ProductId, productInfo.ProductName);
}
Console.ReadKey();
}
}
static void Main(string[] args)
{
// FIRST
string firstName = "Brooke";
using (AdventureWorks2014Entities context = new AdventureWorks2014Entities())
{
var persons = context.Person;
Person query = (from person in persons
where person.FirstName == firstName
select person).First();
Console.WriteLine("FirstName: " + query.FirstName);
Console.WriteLine("LastName: " + query.LastName);
Console.ReadKey();
}
}
static void Main(string[] args)
{
// GroupJoin
//using (AdventureWorks2014Entities entities = new AdventureWorks2014Entities())
//{
// var orders = entities.SalesOrderHeader;
// var details = entities.SalesOrderDetail;
// var query = orders.GroupJoin(details,
// order => order.SalesOrderID,
// detail => detail.SalesOrderID,
// (order, orderGroup) => new
// {
// CustomerID = order.SalesOrderID,
// OrderCount = orderGroup.Count()
// });
// foreach (var order in query)
// {
// Console.WriteLine("CustomerID: {0} Orders Count: {1}",
// order.CustomerID, order.OrderCount);
// }
// Console.ReadKey();
//}
//using (AdventureWorks2014Entities entities = new AdventureWorks2014Entities())
//{
// var customers = entities.Customer;
// var orders = entities.SalesOrderHeader;
// var query = customers.GroupJoin(orders,
// customer => customer.CustomerID,
// order => order.Customer.CustomerID,
// (customer, customerGroup) => new
// {
// CustomerID = customer.CustomerID,
// OrderCount = customerGroup.Count(),
// Orders = customerGroup
// }).Take(30);
// foreach (var group in query)
// {
// Console.WriteLine("CustomerID: {0}", group.CustomerID);
// Console.WriteLine("Order Count: {0}", group.OrderCount);
// foreach (var orderInfo in group.Orders)
// {
// Console.WriteLine(" Sale ID: {0}", orderInfo.SalesOrderID);
// }
// Console.Write("");
// }
// Console.ReadKey();
//}
// JOIN
//using (AdventureWorks2014Entities entities = new AdventureWorks2014Entities())
//{
// var customers = entities.Customer;
// var orders = entities.SalesOrderHeader;
// var query = customers.Join(orders,
// order => order.CustomerID,
// customer => customer.Customer.CustomerID,
// (customer, order) => new
// {
// CustomerID = customer.CustomerID,
// SalesOrderID = order.SalesOrderID,
// FirstName = customer.Person.FirstName,
// LastName = customer.Person.LastName,
// TotalDue = order.TotalDue
// }).Take(20); // <-- Limit the query
// foreach (var customer_order in query)
// {
// Console.WriteLine("CustomerID: {0} "
// + "SalesOrderID: {1} "
// + "FirstName: {2} "
// + "LastName: {3} "
// + "TotalDue: {4}",
// customer_order.CustomerID,
// customer_order.SalesOrderID,
// customer_order.FirstName,
// customer_order.LastName,
// customer_order.TotalDue);
// }
// Console.ReadKey();
//}
using (AdventureWorks2014Entities entities = new AdventureWorks2014Entities())
{
var customers = entities.Customer;
var orders = entities.SalesOrderHeader;
var query = customers.Join(orders,
order => order.CustomerID,
contact => contact.Customer.CustomerID,
(customer, order) => new
{
CustomerID = customer.CustomerID,
SalesOrderID = order.SalesOrderID,
FirstName = customer.Person.FirstName,
Lastname = customer.Person.LastName,
TotalDue = order.TotalDue
}).GroupBy(record => record.CustomerID);
foreach (var group in query)
{
foreach (var customer_order in group)
{
Console.WriteLine("CustomerID: {0} "
+ "SalesOrderID: {1} "
+ "FirstName: {2} "
+ "LastName: {3} "
+ "TotalDue: {4}",
customer_order.CustomerID,
customer_order.SalesOrderID,
customer_order.FirstName,
customer_order.Lastname,
customer_order.TotalDue);
}
}
Console.ReadKey();
}
}
static void Main(string[] args)
{
// USING AVERAGE
//using (AdventureWorks2014Entities entities = new AdventureWorks2014Entities())
//{
// var products = entities.Product;
// var query = (from product in products
// group product by product.Style into g
// select new
// {
// Style = g.Key,
// AverageListPrice = g.Average(product => product.ListPrice)
// });
// foreach (var product in query)
// {
// Console.WriteLine("Product style: {0} Average list price: {1}",
// product.Style, product.AverageListPrice);
// }
// Console.ReadKey();
//}
//using (AdventureWorks2014Entities entities = new AdventureWorks2014Entities())
//{
// var orders = entities.SalesOrderHeader;
// var query = (from order in orders
// group order by order.Customer.CustomerID into g
// select new
// {
// Category = g.Key,
// averageTotalDue = g.Average(order => order.TotalDue)
// }).Take(20);
// foreach (var order in query)
// {
// Console.WriteLine("ContactID = {0} \t Average TotalDue = {1}",
// order.Category, order.averageTotalDue);
// }
// Console.ReadKey();
//}
//using (AdventureWorks2014Entities entities = new AdventureWorks2014Entities())
//{
// var orders = entities.SalesOrderHeader;
// var query = (from order in orders
// group order by order.Customer.CustomerID into g
// let averageTotalDue = g.Average(order => order.TotalDue)
// select new
// {
// Category = g.Key,
// CheapestProducts = g.Where(order => order.TotalDue == averageTotalDue)
// }).Take(20);
// foreach (var orderGroup in query)
// {
// Console.WriteLine("ContactID: {0}", orderGroup.Category);
// foreach (var order in orderGroup.CheapestProducts)
// {
// Console.WriteLine("Average total due for SalesOrderID {1} is: {0}",
// order.TotalDue, order.SalesOrderID);
// }
// Console.Write("\n");
// }
// Console.ReadKey();
//}
// COUNT
//using (AdventureWorks2014Entities entities = new AdventureWorks2014Entities())
//{
// var customers = entities.Customer;
// var query = (from customer in customers
// select new
// {
// CustomerID = customer.CustomerID,
// OrderCount = customer.SalesOrderHeader.Count()
// });
// foreach (var customer in query)
// {
// Console.WriteLine("CustomerID = {0} \t OrderCount = {1}",
// customer.CustomerID, customer.OrderCount);
// }
// Console.ReadKey();
//}
//using (AdventureWorks2014Entities entities = new AdventureWorks2014Entities())
//{
// var products = entities.Product;
// var query = from product in products
// group product by product.Color into g
// select new { Color = g.Key, ProductCount = g.Count() };
// foreach (var product in query)
// {
// Console.WriteLine("Color = {0} \t ProductCount = {1}",
// product.Color, product.ProductCount);
// }
// Console.ReadKey();
//}
// MAX
//using (AdventureWorks2014Entities entities = new AdventureWorks2014Entities())
//{
// var orders = entities.SalesOrderHeader;
// var query = (from order in orders
// group order by order.Customer.CustomerID into g
// select new
// {
// Category = g.Key,
// maxTotalDue = g.Max(order => order.TotalDue)
// }).Take(20);
// foreach (var order in query)
// {
// Console.WriteLine("CustomerID = {0} \t Maximum TotalDue = {1}",
// order.Category, order.maxTotalDue);
// }
// Console.ReadKey();
//}
//using (AdventureWorks2014Entities entities = new AdventureWorks2014Entities())
//{
// var orders = entities.SalesOrderHeader;
// var query = (from order in orders
// group order by order.Customer.CustomerID into g
// let maxTotalDue = g.Max(order => order.TotalDue)
// select new
// {
// Category = g.Key,
// CheapestProducts = g.Where(order => order.TotalDue == maxTotalDue)
// }).Take(20);
// foreach (var orderGroup in query)
// {
// Console.WriteLine("CustomerID: {0}", orderGroup.Category);
// foreach (var order in orderGroup.CheapestProducts)
// {
// Console.WriteLine("MaxTotalDue {0} for SalesOrderID {1}: ",
// order.TotalDue, order.SalesOrderID);
// }
// }
// Console.ReadKey();
//}
// MIN
//using (AdventureWorks2014Entities entities = new AdventureWorks2014Entities())
//{
// var orders = entities.SalesOrderHeader;
// var query = (from order in orders
// group order by order.Customer.CustomerID into g
// select new
// {
// Category = g.Key,
// smallestTotalDue = g.Min(order => order.TotalDue)
// }).Take(20);
// foreach (var order in query)
// {
// Console.WriteLine("CustomerID = {0} \t Minimum TotalDue = {1}",
// order.Category, order.smallestTotalDue);
// }
// Console.ReadKey();
//}
//using (AdventureWorks2014Entities context = new AdventureWorks2014Entities())
//{
// var orders = context.SalesOrderHeader;
// var query = (from order in orders
// group order by order.Customer.CustomerID into g
// let minTotalDue = g.Min(order => order.TotalDue)
// select new
// {
// Category = g.Key,
// smallestTotalDue = g.Where(order => order.TotalDue == minTotalDue)
// }).Take(20);
// foreach (var orderGroup in query)
// {
// Console.WriteLine("ContactID: {0}", orderGroup.Category);
// foreach (var order in orderGroup.smallestTotalDue)
// {
// Console.WriteLine("Mininum TotalDue {0} for SalesOrderID {1}: ",
// order.TotalDue, order.SalesOrderID);
// }
// Console.Write("\n");
// }
// Console.ReadKey();
//}
// SUM
using (AdventureWorks2014Entities context = new AdventureWorks2014Entities())
{
var orders = context.SalesOrderHeader;
var query = (from order in orders
group order by order.Customer.CustomerID into g
select new
{
Category = g.Key,
TotalDue = g.Sum(order => order.TotalDue)
}).Take(20);
foreach (var order in query)
{
Console.WriteLine("ContactID = {0} \t TotalDue sum = {1}",
order.Category, order.TotalDue);
}
Console.ReadKey();
}
}
static void Main(string[] args)
{
// WHERE
//using (AdventureWorks2014Entities entities = new AdventureWorks2014Entities())
//{
// var onlineOrders = (from order in entities.SalesOrderHeader
// where order.OnlineOrderFlag == true
// select new
// {
// SalesOrderID = order.SalesOrderID,
// OrderDate = order.OrderDate,
// SalesOrderNumber = order.SalesOrderNumber
// }).Take(20);
// foreach (var onlineOrder in onlineOrders)
// {
// Console.WriteLine("Order ID: {0} Order Date: {1:d} Order Number: {2}",
// onlineOrder.SalesOrderID, onlineOrder.OrderDate, onlineOrder.SalesOrderNumber);
// }
// Console.ReadKey();
//}
int orderQtyMin = 2;
int orderQtyMax = 6;
//using (AdventureWorks2014Entities entities = new AdventureWorks2014Entities())
//{
// var query = (from order in entities.SalesOrderDetail
// where order.OrderQty > orderQtyMin && order.OrderQty < orderQtyMax
// select new
// {
// SalesOrderID = order.SalesOrderID,
// OrderQty = order.OrderQty
// }).Take(20);
// foreach (var order in query)
// {
// Console.WriteLine("Order ID: {0} Order Quantity: {1}",
// order.SalesOrderID, order.OrderQty);
// }
// Console.ReadKey();
//}
String color = "Red";
//using (AdventureWorks2014Entities entities = new AdventureWorks2014Entities())
//{
// var query = (from product in entities.Product
// where product.Color == color
// select new
// {
// Name = product.Name,
// ProductNumber = product.ProductNumber,
// ListPrice = product.ListPrice
// }).Take(10);
// foreach (var product in query)
// {
// Console.WriteLine("Name: {0}", product.Name);
// Console.WriteLine("Product number: {0}", product.ProductNumber);
// Console.WriteLine("List price: ${0}", product.ListPrice);
// Console.WriteLine("");
// }
// Console.ReadKey();
//}
// WHERE...CONTAINS
//using (AdventureWorks2014Entities entities = new AdventureWorks2014Entities())
//{
// IQueryable<SalesOrderHeader> query = (from order in entities.SalesOrderHeader
// where order.OrderDate >= new DateTime(2003, 12, 1)
// select order).Take(10);
// Console.WriteLine("Orders that were made after December 1, 2003:");
// foreach (SalesOrderHeader order in query)
// {
// Console.WriteLine("OrderID {0} Order date: {1:d} ",
// order.SalesOrderID, order.OrderDate);
// foreach (SalesOrderDetail orderDetail in order.SalesOrderDetail)
// {
// Console.WriteLine(" Product ID: {0} Unit Price {1}",
// orderDetail.ProductID, orderDetail.UnitPrice);
// }
// }
// Console.ReadKey();
//}
//using (AdventureWorks2014Entities entities = new AdventureWorks2014Entities())
//{
// int?[] productModelIds = { 19, 26, 118 };
// var products = from p in entities.Product
// where productModelIds.Contains(p.ProductModelID)
// select p;
// foreach (var product in products)
// {
// Console.WriteLine("{0}: {1}", product.ProductModelID, product.ProductID);
// }
// Console.ReadKey();
//}
using (AdventureWorks2014Entities entities = new AdventureWorks2014Entities())
{
var products = from p in entities.Product
where (new int?[] { 19, 26, 18 }).Contains(p.ProductModelID) ||
(new string[] { "L", "XL" }).Contains(p.Size)
select p;
foreach (var product in products)
{
Console.WriteLine("{0}: {1}, {2}", product.ProductID, product.ProductModelID, product.Size);
}
Console.ReadKey();
}
}
static void Main(string[] args)
{
// ORDER...BY
//using (AdventureWorks2014Entities entities = new AdventureWorks2014Entities())
//{
// IQueryable<Person> sortedNames = (from n in entities.Person
// orderby n.LastName
// select n).Take(20);
// Console.WriteLine("The sorted list of last names:");
// foreach (Person n in sortedNames)
// {
// Console.WriteLine(n.LastName);
// }
// Console.ReadKey();
//}
//using (AdventureWorks2014Entities entities = new AdventureWorks2014Entities())
//{
// IQueryable<Person> sortedNames = (from n in entities.Person
// orderby n.LastName.Length // <-- by lenght
// select n).Take(20);
// Console.WriteLine("The sorted list of last names (by lenght):");
// foreach (Person n in sortedNames)
// {
// Console.WriteLine(n.LastName);
// }
// Console.ReadKey();
//}
// ORDERBYDESCENDING
//using (AdventureWorks2014Entities entities = new AdventureWorks2014Entities())
//{
// IQueryable<Decimal> sortedPrices = (from p in entities.Product
// orderby p.ListPrice descending
// select p.ListPrice).Take(20);
// Console.WriteLine("The list price from highest to lowest:");
// foreach (Decimal price in sortedPrices)
// {
// Console.WriteLine(price);
// }
// Console.ReadKey();
//}
// THENBY
//using (AdventureWorks2014Entities entities = new AdventureWorks2014Entities())
//{
// IQueryable<Person> sortedPersons = (from person in entities.Person
// orderby person.LastName, person.FirstName
// select person).Take(20);
// Console.WriteLine("The list of contacts sorted by last name then by first name:");
// foreach (Person sortedPerson in sortedPersons)
// {
// Console.WriteLine(sortedPerson.LastName + ", " + sortedPerson.FirstName);
// }
// Console.ReadKey();
//}
// THENBYDESCEDING
using (AdventureWorks2014Entities entities = new AdventureWorks2014Entities())
{
IQueryable<Product> query = (from product in entities.Product
orderby product.Name, product.ListPrice descending
select product).Take(20);
foreach (Product product in query)
{
Console.WriteLine("Product ID: {0} Product Name: {1} List Price {2}",
product.ProductID, product.Name, product.ListPrice);
}
Console.ReadKey();
}
}
static void Main(string[] args)
{
// GROUPJOIN
//using (AdventureWorks2014Entities context = new AdventureWorks2014Entities())
//{
// var orders = context.SalesOrderHeader;
// var details = context.SalesOrderDetail;
// var query = (from order in orders
// join detail in details
// on order.SalesOrderID
// equals detail.SalesOrderID into orderGroup
// select new
// {
// CustomerID = order.SalesOrderID,
// OrderCount = orderGroup.Count()
// }).Take(20);
// foreach (var order in query)
// {
// Console.WriteLine("CustomerID: {0} Orders Count: {1}",
// order.CustomerID, order.OrderCount);
// }
// Console.ReadKey();
//}
//using (AdventureWorks2014Entities context = new AdventureWorks2014Entities())
//{
// var customers = context.Customer;
// var orders = context.SalesOrderHeader;
// var query = (from customer in customers
// join order in orders
// on customer.CustomerID
// equals order.Customer.CustomerID into customerGroup
// select new
// {
// CustomerID = customer.CustomerID,
// OrderCount = customerGroup.Count(),
// Orders = customerGroup
// }).Take(20);
// foreach (var group in query)
// {
// Console.WriteLine("ContactID: {0}", group.CustomerID);
// Console.WriteLine("Order count: {0}", group.OrderCount);
// foreach (var orderInfo in group.Orders)
// {
// Console.WriteLine(" Sale ID: {0}", orderInfo.SalesOrderID);
// }
// Console.WriteLine("");
// }
// Console.ReadKey();
//}
//using (AdventureWorks2014Entities context = new AdventureWorks2014Entities())
//{
// var customers = context.Customer;
// var orders = context.SalesOrderHeader;
// var query = (from customer in customers
// join order in orders
// on customer.CustomerID
// equals order.Customer.CustomerID into customerGroup
// select new
// {
// ContactID = customer.CustomerID,
// OrderCount = customerGroup.Count(),
// Orders = customerGroup
// }).Take(20);
// foreach (var group in query)
// {
// Console.WriteLine("ContactID: {0}", group.ContactID);
// Console.WriteLine("Order count: {0}", group.OrderCount);
// foreach (var orderInfo in group.Orders)
// {
// Console.WriteLine(" Sale ID: {0}", orderInfo.SalesOrderID);
// }
// Console.WriteLine("");
// }
// Console.ReadKey();
//}
using (AdventureWorks2014Entities context = new AdventureWorks2014Entities())
{
var orders = context.SalesOrderHeader;
var details = context.SalesOrderDetail;
var query = (from order in orders
join detail in details
on order.SalesOrderID equals detail.SalesOrderID
where order.OnlineOrderFlag == true &&
order.OrderDate.Month == 8
select new
{
SalesOrderID = order.SalesOrderID,
SalesOrderDetailID = detail.SalesOrderDetailID,
OrderDate = order.OrderDate,
ProductID = detail.ProductID
}).Take(20);
foreach (var order in query)
{
Console.WriteLine("{0}\t{1}\t{2:d}\t{3}",
order.SalesOrderID,
order.SalesOrderDetailID,
order.OrderDate,
order.ProductID);
}
Console.ReadKey();
}
}
static void Main(string[] args)
{
//using (AdventureWorks2014Entities context = new AdventureWorks2014Entities())
//{
// var query = (from address in context.Address
// group address by address.PostalCode into addressGroup
// select new
// {
// PostalCode = addressGroup.Key,
// AddressLine = addressGroup
// }).Take(20);
// foreach (var addressGroup in query)
// {
// Console.WriteLine("Postal Code: {0}", addressGroup.PostalCode);
// foreach (var address in addressGroup.AddressLine)
// {
// Console.WriteLine("\t" + address.AddressLine1 + address.AddressLine2);
// }
// }
// Console.ReadKey();
//}
//using (AdventureWorks2014Entities context = new AdventureWorks2014Entities())
//{
// var query = (from person in context.Person
// group person by person.LastName.Substring(0, 1) into personGroup
// select new
// {
// FirstLetter = personGroup.Key,
// Names = personGroup
// }).OrderBy(letter => letter.FirstLetter).Take(20);
// foreach (var person in query)
// {
// Console.WriteLine("Last names that start with the letter '{0}':",
// person.FirstLetter);
// foreach (var name in person.Names)
// {
// Console.WriteLine(name.LastName);
// }
// }
// Console.ReadKey();
//}
using (AdventureWorks2014Entities entities = new AdventureWorks2014Entities())
{
var query = (from order in entities.SalesOrderHeader
group order by order.CustomerID into idGroup
select new
{
CustomerID = idGroup.Key,
OrderCount = idGroup.Count(),
Sales = idGroup
}).Take(20);
foreach (var orderGroup in query)
{
Console.WriteLine("Customer ID: {0}", orderGroup.CustomerID);
Console.WriteLine("Order Count: {0}", orderGroup.OrderCount);
foreach (SalesOrderHeader sale in orderGroup.Sales)
{
Console.WriteLine(" Sale ID: {0}", sale.SalesOrderID);
}
Console.WriteLine("");
}
Console.ReadKey();
}
}
// Query syntax examples Filtering
// Menambahkan where di query linq
static void Main(string[] args)
{
//using (AdventureWorks2014Entities context = new AdventureWorks2014Entities())
//{
// var emailPro = context.Person
// .Where(order => order.EmailPromotion == 2)
// .Select(s => new { s.BusinessEntityID, s.FirstName, s.LastName });
// foreach (var emailProm in emailPro)
// {
// Console.WriteLine("Bussiness ID: {0} Name: {1}, {2}",
// emailProm.BusinessEntityID,
// emailProm.FirstName,
// emailProm.LastName);
// }
// Console.ReadKey();
//}
//int orderQtyMin = 2;
//int orderQtyMax = 6;
//using (AdventureWorks2014Entities entities = new AdventureWorks2014Entities())
//{
// var query = entities.WorkOrder
// .Where(order => order.OrderQty > orderQtyMin && order.OrderQty < orderQtyMax)
// .Select(s => new { s.WorkOrderID, s.OrderQty });
// foreach (var order in query)
// {
// Console.WriteLine("Order ID: {0} Order quantity: {1}",
// order.WorkOrderID, order.OrderQty);
// }
// Console.ReadKey();
//}
//String color = "Silver";
//using (AdventureWorks2014Entities entities = new AdventureWorks2014Entities())
//{
// var query = entities.Product
// .Where(products => products.Color == color)
// .Select(p => new { p.Name, p.ProductNumber, p.ListPrice });
// foreach (var product in query)
// {
// Console.WriteLine("Name : {0}", product.Name);
// Console.WriteLine("Product Number : {0}", product.ProductNumber);
// Console.WriteLine("List Price : {0}", product.ListPrice);
// Console.WriteLine("");
// }
// Console.ReadKey();
//}
// with date
//using (AdventureWorks2014Entities entities = new AdventureWorks2014Entities())
//{
// IQueryable<SalesOrderHeader> query = entities.SalesOrderHeader
// .Where(order => order.OrderDate >= new DateTime(2003, 12, 1)).Take(10);
// Console.WriteLine("Orders that were made after December 1, 2003:");
// foreach (SalesOrderHeader order in query)
// {
// Console.WriteLine("Order ID {0} Order date: {1:d}",
// order.SalesOrderID, order.OrderDate);
// foreach(SalesOrderDetail orderDetail in order.SalesOrderDetail)
// {
// Console.WriteLine(" Product ID : {0} Unit Price {1}",
// orderDetail.ProductID, orderDetail.UnitPrice);
// }
// }
// Console.ReadKey();
//}
// Where....Contains 1
//using (AdventureWorks2014Entities entities = new AdventureWorks2014Entities())
//{
// int?[] productModelIds = { 19, 26, 118, };
// var products = entities.Product
// .Where(p => productModelIds.Contains(p.ProductModelID));
// foreach (var product in products)
// {
// Console.WriteLine("{0}: {1}", product.ProductModelID, product.ProductID);
// }
// Console.ReadKey();
//}
// where....Contains 2
using (AdventureWorks2014Entities entities = new AdventureWorks2014Entities())
{
var products = entities.Product.
Where(p => (new int?[] { 19, 26, 18 }).Contains(p.ProductModelID) ||
(new string[] { "L", "XL" }).Contains(p.Size));
foreach (var product in products)
{
Console.WriteLine("{0}: {1}, {2}", product.ProductID,
product.ProductModelID,
product.Size);
}
Console.ReadKey();
}
}
static void Main(string[] args)
{
//using (AdventureWorks2014Entities entities = new AdventureWorks2014Entities())
//{
// IQueryable<Product> productsQuery = from product in entities.Product
// select product;
// Console.WriteLine("Product Names:");
// foreach (var prod in productsQuery)
// {
// Console.WriteLine(prod.Name);
// }
// Console.ReadKey();
//}
//using (AdventureWorks2014Entities entities = new AdventureWorks2014Entities())
//{
// IQueryable<string> productNames = from p in entities.Product
// select p.Name;
// Console.WriteLine("Product Names:");
// foreach (String productName in productNames)
// {
// Console.WriteLine(productName);
// }
// Console.ReadKey();
//}
//using (AdventureWorks2014Entities entities = new AdventureWorks2014Entities())
//{
// var query = from product in entities.Product
// select new
// {
// ProductId = product.ProductID,
// ProductName = product.Name
// };
// Console.WriteLine("Product Info:");
// foreach (var productInfo in query)
// {
// Console.WriteLine("Product Id: {0} Product name: {1} ",
// productInfo.ProductId, productInfo.ProductName);
// }
// Console.ReadKey();
//}
// From … From … (SelectMany)
// decimal totalDue = 500.00M;
//using (AdventureWorks2014Entities entities = new AdventureWorks2014Entities())
//{
// var customers = entities.Customer;
// var orders = entities.SalesOrderHeader;
// var query = (from customer in customers
// from order in orders
// where customer.CustomerID == order.Customer.CustomerID && order.TotalDue < totalDue
// select new
// {
// CustomerID = customer.CustomerID,
// LastName = customer.Person.LastName,
// FirstName = customer.Person.FirstName,
// OrderID = order.SalesOrderID,
// Total = order.TotalDue
// }).Take(10);
// foreach (var smallOrder in query)
// {
// Console.WriteLine("Customer ID: {0} Name: {1}, {2} Order ID: {3} Total Due: ${4} ",
// smallOrder.CustomerID, smallOrder.LastName, smallOrder.FirstName,
// smallOrder.OrderID, smallOrder.Total);
// }
// Console.ReadKey();
//}
//using (AdventureWorks2014Entities entities = new AdventureWorks2014Entities())
//{
// var customers = entities.Customer;
// var orders = entities.SalesOrderHeader;
// var query = (from customer in customers
// from order in orders
// where customer.CustomerID == order.Customer.CustomerID && order.OrderDate >= new DateTime(2002, 10, 1)
// select new
// {
// CustomerID = customer.CustomerID,
// LastName = customer.Person.LastName,
// FirstName = customer.Person.FirstName,
// OrderID = order.SalesOrderID,
// OrderDate = order.OrderDate
// }).Take(10);
// foreach (var order in query)
// {
// Console.WriteLine("Customer ID: {0} Name: {1}, {2} Order ID: {3} Order Date: {4:d}",
// order.CustomerID, order.LastName, order.FirstName,
// order.OrderID, order.OrderDate);
// }
// Console.ReadKey();
//}
decimal totalDue = 10000.0M;
using (AdventureWorks2014Entities entities = new AdventureWorks2014Entities())
{
var customers = entities.Customer;
var orders = entities.SalesOrderHeader;
var query = (from customer in customers
from order in orders
let total = order.TotalDue
where customer.CustomerID == order.Customer.CustomerID && total >= totalDue
select new
{
CustomerID = customer.CustomerID,
LastName = customer.Person.LastName,
OrderID = order.SalesOrderID,
total
}).Take(20);
foreach (var order in query)
{
Console.WriteLine("Customer ID: {0} Last name: {1} Order ID: {2} Total: {3}",
order.CustomerID, order.LastName, order.OrderID, order.total);
}
Console.ReadKey();
}
}
static void Main(string[] args)
{
// AVERAGE
//using (AdventureWorks2014Entities entities = new AdventureWorks2014Entities())
//{
// var products = entities.Product;
// Decimal averageListPrice = products.Average(product => product.ListPrice);
// Console.WriteLine("The average list price of all the products is ${0}",
// averageListPrice);
// Console.ReadKey();
//}
//using (AdventureWorks2014Entities entities = new AdventureWorks2014Entities())
//{
// var products = entities.Product;
// var query = from product in products
// group product by product.Style into g
// select new
// {
// Style = g.Key,
// AverageListPrice = g.Average(product => product.ListPrice)
// };
// foreach (var product in query)
// {
// Console.WriteLine("Product style: {0} Average list price: {1}",
// product.Style, product.AverageListPrice);
// }
// Console.ReadKey();
//}
//using (AdventureWorks2014Entities entities = new AdventureWorks2014Entities())
//{
// var orders = entities.SalesOrderHeader;
// Decimal averageTotalDue = orders.Average(order => order.TotalDue);
// Console.WriteLine("The average TotalDue is {0}.", averageTotalDue);
// Console.ReadKey();
//}
//using (AdventureWorks2014Entities entities = new AdventureWorks2014Entities())
//{
// var orders = entities.SalesOrderHeader;
// var query = from order in orders
// group order by order.AccountNumber into g
// select new
// {
// Category = g.Key,
// averageTotalDue = g.Average(order => order.TotalDue)
// };
// foreach (var order in query)
// {
// Console.WriteLine("AccountNumber = {0} \t Average TotalDue = {1}",
// order.Category, order.averageTotalDue);
// }
// Console.ReadKey();
//}
//using (AdventureWorks2014Entities entities = new AdventureWorks2014Entities())
//{
// var orders = entities.SalesOrderHeader;
// var query = from order in orders
// group order by order.Customer.PersonID into g
// let averageTotalDue = g.Average(order => order.TotalDue)
// select new
// {
// Category = g.Key,
// CheapestProducts =
// g.Where(order => order.TotalDue == averageTotalDue)
// };
// foreach (var orderGroup in query)
// {
// Console.WriteLine("PersonID: {0}", orderGroup.Category);
// foreach (var order in orderGroup.CheapestProducts)
// {
// Console.WriteLine("Average total due for SalesOrderID {1} is: {0}",
// order.TotalDue, order.SalesOrderID);
// }
// Console.Write("\n");
// }
// Console.ReadKey();
//}
// COUNT
//using (AdventureWorks2014Entities entities = new AdventureWorks2014Entities())
//{
// var products = entities.Product;
// int numProducts = products.Count();
// Console.WriteLine("There are {0} products.", numProducts);
// Console.ReadKey();
//}
//using (AdventureWorks2014Entities entities = new AdventureWorks2014Entities())
//{
// var products = entities.Product;
// var query = from product in products
// group product by product.Color into g
// select new { Color = g.Key, ProductCount = g.Count() };
// foreach (var product in query)
// {
// Console.WriteLine("Color = {0} \t ProductCount = {1}",
// product.Color,
// product.ProductCount);
// }
// Console.ReadKey();
//}
// LONG COUNT
//using (AdventureWorks2014Entities entities = new AdventureWorks2014Entities())
//{
// var persons = entities.Person;
// long numberOfPerson = persons.LongCount();
// Console.WriteLine("There are {0} Persons", numberOfPerson);
// Console.ReadKey();
//}
// MAX
//using (AdventureWorks2014Entities entities = new AdventureWorks2014Entities())
//{
// var orders = entities.SalesOrderHeader;
// Decimal maxTotalDue = orders.Max(w => w.TotalDue);
// Console.WriteLine("The maximum TotalDue is {0}.", maxTotalDue);
// Console.ReadKey();
//}
//using (AdventureWorks2014Entities entities = new AdventureWorks2014Entities())
//{
// var orders = entities.SalesOrderHeader;
// var query = from order in orders
// group order by order.Customer.PersonID into g
// select new
// {
// Category = g.Key,
// maxTotalDue = g.Max(order => order.TotalDue)
// };
// foreach (var order in query)
// {
// Console.WriteLine("PersonID = {0} \t Maximum TotalDue = {1}",
// order.Category, order.maxTotalDue);
// }
// Console.ReadKey();
//}
//using (AdventureWorks2014Entities entities = new AdventureWorks2014Entities())
//{
// var orders = entities.SalesOrderHeader;
// var query = from order in orders
// group order by order.Customer.PersonID into g
// let maxTotalDue = g.Max(order => order.TotalDue)
// select new
// {
// Category = g.Key,
// CheapestProducts = g.Where(order => order.TotalDue == maxTotalDue)
// };
// foreach (var orderGroup in query)
// {
// Console.WriteLine("PersonID: {0}", orderGroup.Category);
// foreach (var order in orderGroup.CheapestProducts)
// {
// Console.WriteLine("MaxTotalDue {0} for SalesOrderID {1}: ",
// order.TotalDue,
// order.SalesOrderID);
// }
// Console.Write("\n");
// }
// Console.ReadKey();
//}
// MIN
//using (AdventureWorks2014Entities entities = new AdventureWorks2014Entities())
//{
// var orders = entities.SalesOrderHeader;
// Decimal smallestTotalDue = orders.Min(totalDue => totalDue.TotalDue);
// Console.WriteLine("The smallest TotalDue is {0}.",
// smallestTotalDue);
// Console.ReadKey();
//}
//using (AdventureWorks2014Entities entities = new AdventureWorks2014Entities())
//{
// var orders = entities.SalesOrderHeader;
// var query = from order in orders
// group order by order.Customer.PersonID into g
// select new
// {
// Category = g.Key,
// smallestTotalDue = g.Min(order => order.TotalDue)
// };
// foreach (var order in query)
// {
// Console.WriteLine("PersonID = {0} \t Minimum TotalDue = {1}",
// order.Category, order.smallestTotalDue);
// }
// Console.ReadKey();
//}
//using (AdventureWorks2014Entities entities = new AdventureWorks2014Entities())
//{
// var orders = entities.SalesOrderHeader;
// var query = from order in orders
// group order by order.Customer.PersonID into g
// let minTotalDue = g.Min(order => order.TotalDue)
// select new
// {
// Category = g.Key,
// smallestTotalDue = g.Where(order => order.TotalDue == minTotalDue)
// };
// foreach (var orderGroup in query)
// {
// Console.WriteLine("PersonID: {0}", orderGroup.Category);
// foreach (var order in orderGroup.smallestTotalDue)
// {
// Console.WriteLine("Minimum TotalDue {0} for SalesOrderID {1}: ",
// order.TotalDue,
// order.SalesOrderID);
// }
// Console.Write("\n");
// }
// Console.ReadKey();
//}
// SUM
//using (AdventureWorks2014Entities entities = new AdventureWorks2014Entities())
//{
// var orders = entities.SalesOrderDetail;
// double totalOrderQty = orders.Sum(o => o.OrderQty);
// Console.WriteLine("There are a total of {0} OrderQty.",
// totalOrderQty);
// Console.ReadKey();
//}
using (AdventureWorks2014Entities entities = new AdventureWorks2014Entities())
{
var orders = entities.SalesOrderHeader;
var query = from order in orders
group order by order.Customer.PersonID into g
select new
{
Category = g.Key,
TotalDue = g.Sum(order => order.TotalDue)
};
foreach (var order in query)
{
Console.WriteLine("PersonID = {0} \t TotalDue sum = {1}",
order.Category, order.TotalDue);
}
Console.ReadKey();
}
}
static void Main(string[] args)
{
// SKIP
//using (AdventureWorks2014Entities entities = new AdventureWorks2014Entities())
//{
// // LINQ to Entities only support Skip on ordered collections.
// IOrderedQueryable<Product> products = entities.Product
// .OrderBy(p => p.ListPrice);
// IQueryable<Product> allButFirst3Products = products.Skip(3);
// Console.WriteLine("All but first 3 products: ");
// foreach (Product product in allButFirst3Products)
// {
// Console.WriteLine("Name: {0} \t ID: {1}", product.Name, product.ProductID);
// }
// Console.ReadKey();
//}
//using (AdventureWorks2014Entities entities = new AdventureWorks2014Entities())
//{
// var addresses = entities.Address;
// var orders = entities.SalesOrderHeader;
// // LINQ to Entities only supports Skip on ordered collections.
// var query = (from address in addresses
// from order in orders
// where address.AddressID == order.Address.AddressID && address.City == "Seattle"
// orderby order.SalesOrderID
// select new
// {
// City = address.City,
// OrderID = order.SalesOrderID,
// OrderDate = order.OrderDate
// }).Skip(2).Take(10); // <-- This for limit (.Take(10))
// Console.WriteLine("All but first 2 orders in Seattle");
// foreach (var order in query)
// {
// Console.WriteLine("City: {0} Order ID: {1} Total Due: {2:d}",
// order.City, order.OrderID, order.OrderDate);
// }
// Console.ReadKey();
//}
// TAKE
//using (AdventureWorks2014Entities entities = new AdventureWorks2014Entities())
//{
// IQueryable<Person> firstPerson = entities.Person.Take(5);
// Console.WriteLine("First 5 persons :");
// foreach(Person person in firstPerson)
// {
// Console.WriteLine("Title = {0} \t FirstName = {1} \t LastName = {2}",
// person.Title,
// person.FirstName,
// person.LastName);
// }
// Console.ReadKey();
//}
String city = "Seattle";
using (AdventureWorks2014Entities entities = new AdventureWorks2014Entities())
{
var addresses = entities.Address;
var orders = entities.SalesOrderHeader;
var query = (from address in addresses
from order in orders
where address.AddressID == order.Address.AddressID && address.City == city
select new
{
City = address.City,
OrderID = order.SalesOrderID,
OrderDate = order.OrderDate
}).Take(3);
Console.WriteLine("First 3 orders in Seattle:");
foreach (var order in query)
{
Console.WriteLine("City: {0} Order ID: {1} Order Date {2:d}",
order.City, order.OrderID, order.OrderDate);
}
Console.ReadKey();
}
}
static void Main(string[] args)
{
string lastName = "Zhou";
//using (AdventureWorks2014Entities entities = new AdventureWorks2014Entities())
//{
// IQueryable<SalesOrderHeader> ordersQuery = entities.Customer
// .Where(c => c.Person.LastName == lastName)
// .SelectMany(c => c.SalesOrderHeader);
// foreach (var order in ordersQuery)
// {
// Console.WriteLine("Order ID: {0}, Order Date: {1}, Total Due: {2}",
// order.SalesOrderID, order.OrderDate, order.TotalDue);
// }
// Console.ReadKey();
//}
//using (AdventureWorks2014Entities entities = new AdventureWorks2014Entities())
//{
// var ordersQuery = entities.Customer
// .Where(c => c.Person.LastName == lastName)
// .Select(c => new
// {
// CustomerID = c.CustomerID,
// Total = c.SalesOrderHeader.Sum(o => o.TotalDue)
// });
// foreach (var customer in ordersQuery)
// {
// Console.WriteLine("Customer ID: {0} Orders total: {1}", customer.CustomerID, customer.Total);
// }
// Console.ReadKey();
//}
//using (AdventureWorks2014Entities entities = new AdventureWorks2014Entities())
//{
// var ordersQuery = entities.Customer
// .Where(c => c.Person.LastName == lastName)
// .Select(c => new
// {
// LastName = c.Person.LastName,
// Orders = c.SalesOrderHeader
// });
// foreach (var order in ordersQuery)
// {
// Console.WriteLine("Name: {0}", order.LastName);
// foreach (SalesOrderHeader orderInfo in order.Orders)
// {
// Console.WriteLine("Order ID: {0}, Order date: {1}, Total Due: {2}",
// orderInfo.SalesOrderID, orderInfo.OrderDate, orderInfo.TotalDue);
// }
// Console.Write("");
// }
// Console.ReadKey();
//}
string city = "Seattle";
//using (AdventureWorks2014Entities entities = new AdventureWorks2014Entities())
//{
// var ordersQuery = entities.SalesOrderHeader
// .Where(o => o.Address.City == city)
// .Select(o => new
// {
// CustomerLastName = o.Customer.Person.LastName,
// CustomerFirstName = o.Customer.Person.FirstName,
// StreetAddress = o.Address.AddressLine1,
// OrderNumber = o.SalesOrderNumber,
// TotalDue = o.TotalDue
// });
// foreach (var orderInfo in ordersQuery)
// {
// Console.WriteLine("Name: {0}, {1}", orderInfo.CustomerLastName, orderInfo.CustomerFirstName);
// Console.WriteLine("Street address: {0}", orderInfo.StreetAddress);
// Console.WriteLine("Order number: {0}", orderInfo.OrderNumber);
// Console.WriteLine("Total Due: {0}", orderInfo.TotalDue);
// Console.WriteLine("");
// }
// Console.ReadKey();
//}
using (AdventureWorks2014Entities context = new AdventureWorks2014Entities())
{
IQueryable <SalesOrderHeader> query =
(from order in context.SalesOrderHeader
where order.OrderDate >= new DateTime(2003, 12, 1)
select order).Take(20);
Console.WriteLine("Orders that were made after December 1, 2003:");
foreach (SalesOrderHeader order in query)
{
Console.WriteLine("OrderID {0} Order date: {1:d} ",
order.SalesOrderID, order.OrderDate);
foreach (SalesOrderDetail orderDetail in order.SalesOrderDetail)
{
Console.WriteLine(" Product ID: {0} Unit Price {1}",
orderDetail.ProductID, orderDetail.UnitPrice);
}
}
Console.ReadKey();
}
}
