using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Objects;
using System.Globalization;
using System.Data.EntityClient;
using System.Data.SqlClient;
using System.Data.Common;
using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Objects;
using System.Globalization;
using System.Data.EntityClient;
using System.Data.SqlClient;
using System.Data.Common;
1、投影
本主题中的示例演示如何使用 Select 和 SelectMany 方法通过使用基于方法的查询语法以查询 AdventureWorks 销售模型。
这些示例中使用的 AdventureWorks 销售模型从 AdventureWorks 示例数据库中的 Contact、Address、Product、SalesOrderHeader 和 SalesOrderDetail 等表生成。
以下示例使用 Select 方法以将 Product.Name 和 Product.ProductID 属性投影到一系列匿名类型。
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
var query = context.Products
.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);
}
}
以下示例使用 Select 方法以只返回一系列产品名称。
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
IQueryable<string> productNames = context.Products
.Select(p => p.Name);
Console.WriteLine("Product Names:");
foreach (String productName in productNames)
{
Console.WriteLine(productName);
}
}
以下示例使用 SelectMany 方法以选择 TotalDue 低于 500.00 的所有订单。
decimal totalDue = 500.00M;
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
ObjectSet<Contact> contacts = context.Contacts;
ObjectSet<SalesOrderHeader> orders = context.SalesOrderHeaders;
var query =
contacts.SelectMany(
contact => orders.Where(order =>
(contact.ContactID == order.Contact.ContactID)
&& order.TotalDue < totalDue)
.Select(order => new
{
ContactID = contact.ContactID,
LastName = contact.LastName,
FirstName = contact.FirstName,
OrderID = order.SalesOrderID,
Total = order.TotalDue
}));
foreach (var smallOrder in query)
{
Console.WriteLine("Contact ID: {0} Name: {1}, {2} Order ID: {3} Total Due: ${4} ",
smallOrder.ContactID, smallOrder.LastName, smallOrder.FirstName,
smallOrder.OrderID, smallOrder.Total);
}
}
以下示例使用 SelectMany 方法以选择在 2002 年 10 月 1 或此日期之后发出的所有订单。
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
ObjectSet<Contact> contacts = context.Contacts;
ObjectSet<SalesOrderHeader> orders = context.SalesOrderHeaders;
var query =
contacts.SelectMany(
contact => orders.Where(order =>
(contact.ContactID == order.Contact.ContactID)
&& order.OrderDate >= new DateTime(2002, 10, 1))
.Select(order => new
{
ContactID = contact.ContactID,
LastName = contact.LastName,
FirstName = contact.FirstName,
OrderID = order.SalesOrderID,
OrderDate = order.OrderDate
}));
foreach (var order in query)
{
Console.WriteLine("Contact ID: {0} Name: {1}, {2} Order ID: {3} Order date: {4:d} ",
order.ContactID, order.LastName, order.FirstName,
order.OrderID, order.OrderDate);
}
}
2、筛选
本主题中的示例演示如何使用 Where 和 Where…Contains 方法,利用基于方法的查询语法来查询 AdventureWorks 销售模型。 请注意,Where…Contains 不能用作已编译的查询的一部分。
这些示例中使用的 AdventureWorks 销售模型基于 AdventureWorks 示例数据库中的 Contact、Address、Product、SalesOrderHeader 和 SalesOrderDetail 等表生成。
以下示例返回所有联机订单。using (AdventureWorksEntities context = new AdventureWorksEntities())
{
var onlineOrders = context.SalesOrderHeaders
.Where(order => order.OnlineOrderFlag == true)
.Select(s => new { s.SalesOrderID, s.OrderDate, s.SalesOrderNumber });
foreach (var onlineOrder in onlineOrders)
{
Console.WriteLine("Order ID: {0} Order date: {1:d} Order number: {2}",
onlineOrder.SalesOrderID,
onlineOrder.OrderDate,
onlineOrder.SalesOrderNumber);
}
}
以下示例返回订单数量大于 2 且小于 6 的订单。
int orderQtyMin = 2;
int orderQtyMax = 6;
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
var query = context.SalesOrderDetails
.Where(order => order.OrderQty > orderQtyMin && order.OrderQty < orderQtyMax)
.Select(s => new { s.SalesOrderID, s.OrderQty });
foreach (var order in query)
{
Console.WriteLine("Order ID: {0} Order quantity: {1}",
order.SalesOrderID, order.OrderQty);
}
}
以下示例将一个数组用作 Where Contains 子句的一部分,以查找 ProductModelID 与数组中的值匹配的所有产品。
using (AdventureWorksEntities AWEntities = new AdventureWorksEntities())
{
int?[] productModelIds = { 19, 26, 118 };
var products = AWEntities.Products.
Where(p => productModelIds.Contains(p.ProductModelID));
foreach (var product in products)
{
Console.WriteLine("{0}: {1}", product.ProductModelID, product.ProductID);
}
}
3、排序
本主题中的示例演示如何使用 ThenBy 方法,通过基于方法的查询语法查询 AdventureWorks 销售模型。 这些示例中使用的 AdventureWorks 销售模型从 AdventureWorks 示例数据库中的 Contact、Address、Product、SalesOrderHeader 和 SalesOrderDetail 等表生成。
采用基于方法的查询语法的以下示例使用 OrderBy 和 ThenBy 以返回先按姓氏后按名字排序的联系人列表。
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
IQueryable<Contact> sortedContacts = context.Contacts
.OrderBy(c => c.LastName)
.ThenBy(c => c.FirstName);
Console.WriteLine("The list of contacts sorted by last name then by first name:");
foreach (Contact sortedContact in sortedContacts)
{
Console.WriteLine(sortedContact.LastName + ", " + sortedContact.FirstName);
}
}
以下示例使用 OrderBy 和 ThenByDescending 方法以首先按标价排序,然后执行产品名称的降序排序。
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
IOrderedQueryable<Product> query = context.Products
.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);
}
}
4、聚合计算
以下示例使用 Average 方法来查找产品的平均标价。
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
ObjectSet<Product> products = context.Products;
Decimal averageListPrice =
products.Average(product => product.ListPrice);
Console.WriteLine("The average list price of all the products is ${0}",
averageListPrice);
}
以下示例使用 Average 方法以查找每种样式的产品的平均标价。
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
ObjectSet<Product> products = context.Products;
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);
}
}
以下示例使用 Average 方法以查找平均应付款总计。
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
ObjectSet<SalesOrderHeader> orders = context.SalesOrderHeaders;
Decimal averageTotalDue = orders.Average(order => order.TotalDue);
Console.WriteLine("The average TotalDue is {0}.", averageTotalDue);
}
以下示例使用 Average 方法以获取每个联系人 ID 的平均应付款总计。
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
ObjectSet<SalesOrderHeader> orders = context.SalesOrderHeaders;
var query =
from order in orders
group order by order.Contact.ContactID into g
select new
{
Category = g.Key,
averageTotalDue = g.Average(order => order.TotalDue)
};
foreach (var order in query)
{
Console.WriteLine("ContactID = {0} \t Average TotalDue = {1}",
order.Category, order.averageTotalDue);
}
}
以下示例使用 Average 方法以针对每个联系人获取具有平均应付款总计的订单。
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
ObjectSet<SalesOrderHeader> orders = context.SalesOrderHeaders;
var query =
from order in orders
group order by order.Contact.ContactID 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("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");
}
}
以下示例使用 Count 方法以返回 Product 表中的产品数量。
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
ObjectSet<Product> products = context.Products;
int numProducts = products.Count();
Console.WriteLine("There are {0} products.", numProducts);
}
以下示例使用 Count 方法以返回联系人 ID 的列表和每个联系人 ID 所具有的订单数。
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
ObjectSet<Contact> contacts = context.Contacts;
//Can't find field SalesOrderContact
var query =
from contact in contacts
select new
{
CustomerID = contact.ContactID,
OrderCount = contact.SalesOrderHeaders.Count()
};
foreach (var contact in query)
{
Console.WriteLine("CustomerID = {0} \t OrderCount = {1}",
contact.CustomerID,
contact.OrderCount);
}
}
以下示例按颜色对产品进行分组,并使用 Count 方法以返回每个颜色组中的产品数量。
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
ObjectSet<Product> products = context.Products;
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);
}
}
以下示例以长整型获取联系人计数。
using (AdventureWorksEntities context = new AdventureWorksEntities()) { ObjectSet<Contact> contacts = context.Contacts; long numberOfContacts = contacts.LongCount(); Console.WriteLine("There are {0} Contacts", numberOfContacts); }以下示例使用 Max 方法以获取最大应付款总计。
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
ObjectSet<SalesOrderHeader> orders = context.SalesOrderHeaders;
Decimal maxTotalDue = orders.Max(w => w.TotalDue);
Console.WriteLine("The maximum TotalDue is {0}.",
maxTotalDue);
}
以下示例使用 Max 方法以获取每个联系人 ID 的最大应付款总计。
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
ObjectSet<SalesOrderHeader> orders = context.SalesOrderHeaders;
var query =
from order in orders
group order by order.Contact.ContactID into g
select new
{
Category = g.Key,
maxTotalDue =
g.Max(order => order.TotalDue)
};
foreach (var order in query)
{
Console.WriteLine("ContactID = {0} \t Maximum TotalDue = {1}",
order.Category, order.maxTotalDue);
}
}
以下示例使用 Min 方法以获取每个联系人 ID 的最小应付款总计。
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
ObjectSet<SalesOrderHeader> orders = context.SalesOrderHeaders;
var query =
from order in orders
group order by order.Contact.ContactID into g
select new
{
Category = g.Key,
smallestTotalDue =
g.Min(order => order.TotalDue)
};
foreach (var order in query)
{
Console.WriteLine("ContactID = {0} \t Minimum TotalDue = {1}",
order.Category, order.smallestTotalDue);
}
}
以下示例使用 Sum 方法以获取每个联系人 ID 的应付款总计。
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
ObjectSet<SalesOrderHeader> orders = context.SalesOrderHeaders;
var query =
from order in orders
group order by order.Contact.ContactID into g
select new
{
Category = g.Key,
TotalDue = g.Sum(order => order.TotalDue)
};
foreach (var order in query)
{
Console.WriteLine("ContactID = {0} \t TotalDue sum = {1}",
order.Category, order.TotalDue);
}
}