EF4.0中新增了ExecuteStoreQuery,ExecuteStoreCommand,ExecuteFunction等方法,完美支持T- SQL
之前在EF中用Linq進行left join查詢和報表數據查詢簡直是一場噩夢,以下代碼就是例子:
Linq左連接查詢N張表示例代碼
1 public List<Info> getMapUnitList(int MapId)
2 {
3 var var1 = from mu in epm.MapUnit
4 join us in epm.UnitStatus
5 on mu.Unit equals us.Unit into _UnitStatus
6 join cu in epm.ContractUnit
7 on mu.Unit equals cu.Unit into _ContractUnit
8 where mu.Map.MapId == MapId
9 select new Info()
10 {
11 map = mu.Map
12 ,
13 mapstatus = null
14 ,
15 mapunit = mu
16 ,
17 unit = mu.Unit
18 ,
19 status = _UnitStatus.Select(a => a.Status).FirstOrDefault()
20 ,
21 contract = _ContractUnit.Select(a => a.Contract).FirstOrDefault()
22 ,
23 customer = null
24 ,
25 boothtype = null
26 };
27 var var2 = from v1 in var1
28 join ms in epm.MapStatus
29 on new { map = v1.map, status = v1.status }
30 equals new { map = ms.Map, status = ms.Status } into _MapStatus
31 select new Info()
32 {
33 map = null
34 ,
35 mapstatus = _MapStatus.FirstOrDefault()
36 ,
37 mapunit = v1.mapunit
38 ,
39 unit = v1.unit
40 ,
41 status = v1.status
42 ,
43 contract = v1.contract
44 ,
45 customer = null
46 ,
47 boothtype = null
48 };
49 var var3 = from v2 in var2
50 from ubtl in epm.UnitBoothType
51 where ubtl.Unit.UnitId == v2.unit.UnitId
52 join cc in epm.CustomerContract
53 on v2.contract equals cc.Contract into _CustomerContract
54 select new Info()
55 {
56 map = null
57 ,
58 mapstatus = v2.mapstatus
59 ,
60 mapunit = v2.mapunit
61 ,
62 unit = v2.unit
63 ,
64 status = v2.status
65 ,
66 contract = v2.contract
67 ,
68 customer = _CustomerContract.Select(a => a.Customer).FirstOrDefault()
69 ,
70 boothtype = ubtl.BoothType
71 };
72 List<Info> infos = var3.ToList();
73 return infos;
74 }
後來加入了E-SQL,但還是和T-SQL有很大區別,我看到那一堆 as 和 it 就想給挖掉 :)
EF 4.0新增加了對T-SQL的支持,根據T-SQL語句查詢結果集中的列名自動映射到返回類型的屬性名
甚至連EDM文件不支持映射的sql_varent類型也可以自動轉換了,代碼如下:
ExecuteStoreQuery方法
protected void Page_Load(object sender, EventArgs e)
{
ERPEntities erp = new ERPEntities();
string query = @" SELECT [BillPlanRule].[id] as BillID
,qu.UnitNumber
,[BeginDate]
,[EndDate]
,ft.value as FeeType
,f.value as Frequency
,[Price]
,[ActualPrice]
,[Fee]
FROM [BillPlanRule]
left join dbo.SysParameter as ft
on [BillPlanRule].[FeeType]=ft.guid
left join dbo.SysParameter as f
on [BillPlanRule].[Frequency]=f.guid
left join dbo.QuotationUnit as qu
on [BillPlanRule].[UGuid]=qu.guid
where uguid in (select guid from dbo.QuotationUnit
where QuotationGuid in (select guid from dbo.Quotation where id=3) )
order by BillID";
ObjectResult<SysParameter> result = erp.ExecuteStoreQuery<SysParameter>(query);
List<SysParameter> list = result.ToList();
}
public class SysParameter
{
public int? BillID { get; set; }
public DateTime? BeginDate { get; set; }
public DateTime? EndDate { get; set; }
public string FeeType { get; set; }
public string Frequency { get; set; }
public decimal? Price { get; set; }
}
其中FeeType和Frequency在數據庫中的類型都為sql_varent
ExecuteStoreQuery方法調用時並不是實時連接數據庫查詢,而是在下一句result.ToList()時才真正去 查詢
此方法很適合做復雜查詢和報表查詢,如根據各種不同條件拼接sql字符串的查詢
