Entity Framework 4.0新增对T-SQL的支持

Entity Framework 4.0新增对T-SQL的支持

 

Entity Framework 4.0新增对T-SQL的支持 EF4.0中新增了ExecuteStoreQuery,ExecuteStoreCommand,ExecuteFunction等方法,完美支持T-SQL 之前在EF中用Linq进行left join查询和报表数据查询简直是一场噩梦,以下代码就是例子: Linq左连接查询N张表示例代码： public List<Info> getMapUnitList(int MapId) { var var1 = from mu in epm.MapUnit join us in epm.UnitStatus on mu.Unit equals us.Unit into _UnitStatus join cu in epm.ContractUnit on mu.Unit equals cu.Unit into _ContractUnit where mu.Map.MapId == MapId select new Info() { map = mu.Map mapstatus = null mapunit = mu unit = mu.Unitstatus = _UnitStatus.Select(a => a.Status).FirstOrDefault() contract = _ContractUnit.Select(a => a.Contract).FirstOrDefault() customer = null boothtype = null }; var var2 = from v1 in var1 join ms in epm.MapStatus on new { map = v1.map, status = v1.status } equals new { map = ms.Map, status = ms.Status } into _MapStatus select new Info() { map = null mapstatus = _MapStatus.FirstOrDefault() mapunit = v1.mapunit unit = v1.unit status = v1.status contract = v1.contract customer = null boothtype = null }; var var3 = from v2 in var2 from ubtl in epm.UnitBoothType where ubtl.Unit.UnitId == v2.unit.UnitId join cc in epm.CustomerContract on v2.contract equals cc.Contract into _CustomerContract select new Info() { map = null mapstatus = v2.mapstatus mapunit = v2.mapunit unit = v2.unit status = v2.status contract = v2.contract customer = _CustomerContract.Select(a => a.Customer).FirstOrDefault() boothtype = ubtl.BoothType }; List<Info> infos = var3.ToList(); return infos; } 后来加入了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字符串的查询 欢迎访问微软官网下载相关软件http://www.microsoft.com/zh/cn/default.aspx