Entity Framework 4.0新增对TSQL的支持

EF4.0中新增了ExecuteStoreQuery,ExecuteStoreCommand,ExecuteFunction等方法,完美支持T-SQL

之前在EF中用Linq进行left join查询和报表数据查询简直是一场噩梦,以下代码就是例子:

 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.Unit
                          ,
                           status = _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类型也可以自动转换了,代码如下:

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字符串的查询