05 第五讲 LINQ to Entities.docx
《05 第五讲 LINQ to Entities.docx》由会员分享,可在线阅读,更多相关《05 第五讲 LINQ to Entities.docx(24页珍藏版)》请在冰豆网上搜索。
05第五讲LINQtoEntities
第五讲LINQtoEntities
通过对象服务,可以使用语言集成查询(LINQ)、EntitySQL或查询生成器方法对实体数据模型(EDM)执行查询并以对象的形式返回数据。
●LINQtoEntities:
LINQtoEntities允许开发人员通过使用LINQ表达式和LINQ标准查询运算符针对实体数据模型(EDM)对象上下文创建灵活的强类型查询。
这样,用户就可以直接从开发环境中以Transact-SQL类语法编写强类型的可编写查询。
●查询生成器方法:
ObjectQuery实现了一组查询生成器方法,这些方法可用于按顺序构造等效于EntitySQL的查询命令。
●EntitySQL:
EntitySQL是ADO.NET实体框架提供的SQL类语言,用于支持实体数据模型(EDM)。
EDM将应用程序数据表示为映射到已定义数据源的一组实体和关系。
EntitySQL支持EDM构造,从而使用户可以有效地查询实体模型所表示的数据。
下面举例说明三种查询方式:
LINQtoEntities:
using(AdventureWorksEntitiescontext=newAdventureWorksEntities())
{
//Specifytheorderamount.
intorderCost=2500;
try
{
//DefineaLINQquerythatreturnsonlyonlineorders
//morethanthespecifiedamount.
varonlineOrders=
fromorderincontext.SalesOrderHeader
whereorder.OnlineOrderFlag==true&&
order.TotalDue>orderCost
selectorder;
//Printorderinformation.
foreach(varonlineOrderinonlineOrders)
{
Console.WriteLine("OrderID:
{0}Orderdate:
"
+"{1:
d}Ordernumber:
{2}",
onlineOrder.SalesOrderID,
onlineOrder.OrderDate,
onlineOrder.SalesOrderNumber);
}
}
catch(EntitySqlExceptionex)
{
Console.WriteLine(ex.ToString());
}
}
EntitySQL:
using(AdventureWorksEntitiescontext=newAdventureWorksEntities())
{
//Specifytheorderamount.
decimalorderCost=2500;
//SpecifytheEntitySQLquerythatreturnsonlyonlineorders
//morethanthespecifiedamount.
stringqueryString=@"SELECTVALUEoFROMSalesOrderHeaderASo
WHEREo.OnlineOrderFlag=TRUEANDo.TotalDue>@ordercost";
try
{
//DefineanObjectQueryandpassthemaxOrderCostparameter.
ObjectQueryonlineOrders=
newObjectQuery(queryString,context);
onlineOrders.Parameters.Add(
newObjectParameter("ordercost",orderCost));
//Printorderinformation.
foreach(varonlineOrderinonlineOrders)
{
Console.WriteLine("OrderID:
{0}Orderdate:
"
+"{1:
d}Ordernumber:
{2}",
onlineOrder.SalesOrderID,
onlineOrder.OrderDate,
onlineOrder.SalesOrderNumber);
}
}
catch(EntitySqlExceptionex)
{
Console.WriteLine(ex.ToString());
}
}
查询生成器方法:
using(AdventureWorksEntitiescontext=newAdventureWorksEntities())
{
//Specifytheorderamount.
intorderCost=2500;
try
{
//DefineanObjectQuerythatreturnsonlyonlineorders
//morethanthespecifiedamount.
ObjectQueryonlineOrders=
context.SalesOrderHeader
.Where("it.OnlineOrderFlag=TRUEANDit.TotalDue>@ordercost",
newObjectParameter("ordercost",orderCost));
//Printorderinformation.
foreach(varonlineOrderinonlineOrders)
{
varitems=(fromitemincontext.SalesOrderDetail
whereitem.SalesOrderHeader==onlineOrder
selectitem).Take(3);
Console.WriteLine("OrderID:
{0}Orderdate:
"
+"{1:
d}Ordernumber:
{2}",
onlineOrder.SalesOrderID,
onlineOrder.OrderDate,
onlineOrder.SalesOrderNumber);
}
}
catch(EntitySqlExceptionex)
{
Console.WriteLine(ex.ToString());
}
}
本讲我们重点介绍lINQtoEntities查询。
5.1投影
1.以下示例使用Select方法以返回Product表中的所有行并显示产品名称。
using(AdventureWorksEntitiesAWEntities=newAdventureWorksEntities())
{
ObjectQueryproducts=AWEntities.Product;
IQueryableproductsQuery=fromproductinproducts
selectproduct;
Console.WriteLine("ProductNames:
");
foreach(varprodinproductsQuery)
{
Console.WriteLine(prod.Name);
}
}
2.以下示例使用Select以只返回一系列产品名称。
using(AdventureWorksEntitiesAWEntities=newAdventureWorksEntities())
{
ObjectQueryproducts=AWEntities.Product;
IQueryableproductNames=
frompinproducts
selectp.Name;
Console.WriteLine("ProductNames:
");
foreach(StringproductNameinproductNames)
{
Console.WriteLine(productName);
}
}
3.以下示例使用Select方法以将Product.Name和Product.ProductID属性投影到一系列匿名类型。
using(AdventureWorksEntitiesAWEntities=newAdventureWorksEntities())
{
ObjectQueryproducts=AWEntities.Product;
varquery=
fromproductinproducts
selectnew
{
ProductId=product.ProductID,
ProductName=product.Name
};
Console.WriteLine("ProductInfo:
");
foreach(varproductInfoinquery)
{
Console.WriteLine("ProductId:
{0}Productname:
{1}",
productInfo.ProductId,productInfo.ProductName);
}
}
4.以下示例使用From…From…(与SelectMany方法等效)以选择TotalDue低于500.00的所有订单。
using(AdventureWorksEntitiesAWEntities=newAdventureWorksEntities())
{
ObjectQuerycontacts=AWEntities.Contact;
ObjectQueryorders=AWEntities.SalesOrderHeader;
//注意三者是等效的
//利用筛选
varquery=
fromcontactincontacts
fromorderinorders
wherecontact.ContactID==order.Contact.ContactID
&&order.TotalDue<200000.00M
selectnew
{
ContactID=contact.ContactID,
LastName=contact.LastName,
FirstName=contact.FirstName,
OrderID=order.SalesOrderID,
Total=order.TotalDue
};
//利用导航属性
varquery1=
fromcontactincontacts
fromorderincontact.SalesOrderHeader
whereorder.TotalDue>200000.00M
selectnew
{
ContactID=contact.ContactID,
LastName=contact.LastName,
FirstName=contact.FirstName,
OrderID=order.SalesOrderID,
Total=order.TotalDue
};
//使用关联
varquery2=
fromcontactincontacts
joinorinordersoncontact.ContactIDequalsor.Contact.ContactID
whereor.TotalDue>200000.00M
selectnew
{
ContactID=contact.ContactID,
LastName=contact.LastName,
FirstName=contact.FirstName,
OrderID=or.SalesOrderID,
Total=or.TotalDue
};
foreach(varsmallOrderinquery)
{
Console.WriteLine("ContactID:
{0}Name:
{1},{2}OrderID:
{3}TotalDue:
${4}",
smallOrder.ContactID,smallOrder.LastName,smallOrder.FirstName,
smallOrder.OrderID,smallOrder.Total);
}
}
5.以下示例使用From…From…(与SelectMany方法等效)以选择订单总计高于10000.00的所有订单并使用From赋值以避免两次请求总计。
using(AdventureWorksEntitiesAWEntities=newAdventureWorksEntities())
{
ObjectQuerycontacts=AWEntities.Contact;
ObjectQueryorders=AWEntities.SalesOrderHeader;
varquery=
fromcontactincontacts
fromorderinorders
lettotal=order.TotalDue
wherecontact.ContactID==order.Contact.ContactID
&&total>=10000.0M
selectnew
{
ContactID=contact.ContactID,
LastName=contact.LastName,
OrderID=order.SalesOrderID,
total
};
foreach(varorderinquery)
{
Console.WriteLine("ContactID:
{0}Lastname:
{1}OrderID:
{2}Total:
{3}",
order.ContactID,order.LastName,order.OrderID,order.total);
}
}
5.2限制
1.以下示例返回订单数量大于2且小于6的订单。
using(AdventureWorksEntitiesAWEntities=newAdventureWorksEntities())
{
ObjectQueryorders=AWEntities.SalesOrderDetail;
varquery=
fromorderinorders
whereorder.OrderQty>2&&order.OrderQty<6
selectnew
{
SalesOrderID=order.SalesOrderID,
OrderQty=order.OrderQty
};
foreach(varorderinquery)
{
Console.WriteLine("OrderID:
{0}Orderquantity:
{1}",
order.SalesOrderID,order.OrderQty);
}
}
2.以下示例使用Where方法以查找在2003年12月1日之后生成的订单,然后使用order.SalesOrderDetail导航属性以获取每个订单的详细信息。
using(AdventureWorksEntitiesAWEntities=newAdventureWorksEntities())
{
ObjectQueryorders=AWEntities.SalesOrderHeader;
IQueryablequery=
fromorderinorders
whereorder.OrderDate>=newDateTime(2003,12,1)
selectorder;
Console.WriteLine("OrdersthatweremadeafterDecember1,2003:
");
foreach(SalesOrderHeaderorderinquery)
{
Console.WriteLine("OrderID{0}Orderdate:
{1:
d}",
order.SalesOrderID,order.OrderDate);
foreach(SalesOrderDetailorderDetailinorder.SalesOrderDetail)
{
Console.WriteLine("ProductID:
{0}UnitPrice{1}",
orderDetail.ProductID,orderDetail.UnitPrice);
}
}
}
5.3排序
1.以下示例使用OrderBy以返回按姓氏排序的联系人列表。
using(AdventureWorksEntitiesAWEntities=newAdventureWorksEntities())
{
ObjectQuerycontacts=AWEntities.Contact;
IQueryablesortedNames=
fromnincontacts
orderbyn.LastName
selectn;
Console.WriteLine("Thesortedlistoflastnames:
");
foreach(ContactninsortedNames)
{
Console.WriteLine(n.LastName);
}
}
2.以下示例使用orderby…descending,以按照从高到低的顺序对价目表排序。
using(AdventureWorksEntitiesAWEntities=newAdventureWorksEntities())
{
ObjectQueryproducts=AWEntities.Product;
IQueryablesortedPrices=
frompinproducts
orderbyp.ListPricedescending
selectp.ListPrice;
Console.WriteLine("Thelistpricefromhighesttolowest:
");
foreach(DecimalpriceinsortedPrices)
{
Console.WriteLine(price);
}
}
3.以下示例使用OrderBy和ThenBy以返回先按姓氏后按名字排序的联系人列表。
using(AdventureWorksEntitiesAWEntities=newAdventureWorksEntities())
{
ObjectQuerycontacts=AWEntities.Contact;
IQueryablesortedContacts=
fromcontactincontacts
orderbycontact.LastName,contact.FirstName
selectcontact;
Console.WriteLine("Thelistofcontactssortedbylastnamethenbyfirstname:
");
foreach(ContactsortedContactinsortedContacts)
{
Console.WriteLine(sortedContact.LastName+","+sortedContact.FirstName);
}
}
5.4聚合运算符
1.以下示例使用Average方法以查找每种样式的产品的平均标价。
using(AdventureWorksEntitiesAWEntities=newAdventureWorksEntities())
{
ObjectQueryproducts=AWEntities.Product;
varquery=fromproductinproducts
groupproductbyproduct.Styleintog
selectnew
{
Style=g.Key,
AverageListPrice=
g.Average(product=>product.ListPrice)
};
foreach(varproductinquery)
{
Console.WriteLine("Productstyle:
{0}Averagelistprice:
{1}",
product.Style,product.AverageListPrice);
}
}
2.以下示例使用Average以获取每个联系人ID的平均应付款总计。
using(AdventureWorksEntitiesAWEntities=newAdventureWorksEntities())
{
ObjectQueryorders=AWEntities.SalesOr