|
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
出处：蓝色理想
责任编辑：bluehearts
◎进入论坛网络编程版块参加讨论
| 
