The database used in this example is northwind.
All tests are run correctly;
The background code is as follows:
1 using (var edm = new northwindentities ())
2 {
3 // 1. Check the number of orders owned by customer customerid = "alfki"
4 // string esql = @ "select count (0) as count from orders as O where O. customerid = 'alfki '";
5
6
7 // 2. Check the number of men and women in employees.
8 // string esql = @ "select e. titleofcourtesy, count (0) as count from employees as E group by E. titleofcourtesy ";
9
10
11 // 3. query the number of orders with the orderdate time field greater than 40 at the same time between and
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. query the total number of freight items in the order purchased by customerid = "quick"
16 // string esql = @ "select sum (O. Freight) as sum from orders as O where O. customerid = 'quick '";
17
18
19 // 5. The value of freight less than 10 in the order table is displayed as "low", the value smaller than 30 is displayed as "medium", and the value greater than 50 is displayed as "high ".
20 // string esql = "select O. orderid, case when o. freight <10 then n'low' when o. 'When o in freight <30 then n. freight> 50 then n 'high' else n' normal 'end as [freight] From orders as O ";
21
22
23 // 6. query the total number of freight of each month in the order table shipcountry "USA" in 1996.
24 // string esql = @ "select themonth, sum (O. freight) as [freight] From orders as O where o. shipcountry = 'usa' and year (O. orderdate) = 1996 group by month (O. orderdate) as themonth ";
25
26
27 // 7. query the product list of Order details with orderid = 10249 in the order table
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. query the number of Order details of "CENTC, Frank, and whitc" in the order table.
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. query the list of more than 5 customers with an order
36 // string esql = @ "select C. customerid, count (O. orderid) as countbyorder from MERs as C, orders as O where c. customerid = O. customerid group by C. customerid having count (O. orderid)> 5 ";
37
38
39 // 10. Retrieve the order record with the highest freight value in the order purchased by customerid = "CENTC"
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 // some common code
44 objectquery <dbdatarecord> record = EDM. createquery <dbdatarecord> (esql );
45 gridview1.datasource = record;
46 gridview1.databind ();
47}
48
