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

转载

iteyer 2021-07-31 11:32:30

文章标签 equals join linq string 数据库 文章分类 云计算

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; }
}