Entity SQL示例代码

本示例使用的数据库为Northwind

经测试全部运行正确；

 

后台代码如下：

 

1 using (var edm = new NorthwindEntities())
2 {
3 // 1.请查询Customer 的CustomerID = “ALFKI” 所拥有的Order 数量
4 // string esql = @"select COUNT(0) as count from Orders as o where o.CustomerID ='ALFKI' ";
5
6
7 // 2.请查询Employees 中的男士个数和女士个数
8 // string esql = @"select e.TitleOfCourtesy,COUNT(0) as count from Employees as e group by e.TitleOfCourtesy";
9
10
11 // 3.查询Order 中 OrderDate 时间字段在1996-1997年之间同时Freight 大于40的Order 个数
12 // string esql = @"select COUNT(0) as count from NorthwindEntities.Orders as o where YEAR(o.OrderDate)>=1996 and YEAR(o.OrderDate)<=1997 and o.Freight>40";
13
14
15 // 4.查询CustomerID = “QUICK”的人所购买的 Order 中 Freight 的合计
16 // string esql = @"select SUM(o.Freight) as sum from Orders as o where o.CustomerID = 'QUICK' ";
17
18
19 // 5.查询得出Order表中 Freight 小于10的值显示为“低”，小于30的值显示为“中”，大于50的显示为“高”。
20 // string esql = "select o.OrderID,case when o.Freight<10 then N'低' when o.Freight<30 then N'中' when o.Freight>50 then N'高' else N'正常' end as [运费] from Orders as o";
21
22
23 // 6.查询Order 表中ShipCountry 是“USA” 1996年中每个月的Freight 的合计是多少。
24 // string esql = @"select TheMonth,SUM(o.Freight) as [运费] from Orders as o where o.ShipCountry='USA' and YEAR(o.OrderDate) = 1996 group by MONTH(o.OrderDate) as TheMonth";
25
26
27 // 7.查询Order表中 OrderID=10249 的 Order Details 的Product 列表
28 // string esql = @"select p.ProductID, p.ProductName from Order_Details as d,Products as p where d.ProductID = p.ProductID and d.OrderID = 10249";
29
30
31 // 8.查询Order 表中CustomerID 是“Centc、Frank、 WHITC”这三个人的Order Details 个数
32 // string esql = @"select o.CustomerID,o.OrderID,COUNT(o.OrderID) as CountOrderDetails from Orders as o,Order_Details as d where o.OrderID = d.OrderID and (o.CustomerID='CENTC' or o.CustomerID ='FRANK' or o.CustomerID ='WHITC') group by o.CustomerID,o.OrderID";
33
34
35 // 9.查询 Order 超过 5个 以上的 Customer 列表
36 // string esql = @"select c.CustomerID,COUNT(o.OrderID) as CountByOrder from Customers as c, Orders as o where c.CustomerID = o.CustomerID group by c.CustomerID having COUNT(o.OrderID)>5";
37
38
39 // 10.取出 CustomerID =“ CENTC ”所购买Order 中 Freight 最高的 Order 记录
40   string esql = @" select o.CustomerID,o.Freight from Orders as o where o.CustomerID = 'VINET' order by o.Freight desc limit 1 " ;
41
42
43 // 公共部分代码
44   ObjectQuery < DbDataRecord > record = edm.CreateQuery < DbDataRecord > (esql);
45 GridView1.DataSource = record;
46 GridView1.DataBind();
47 }
48  

 

 

 

转载于:https://www.cnblogs.com/popzhou/archive/2010/03/29/1700082.html