EntityFramework 4/5/6 中执行自定义SQL语句

参考：http://www.cnblogs.com/chengxiaohui/articles/2092001.html

在EF4（.NET  4）中，我们有了全新的API：ObjectContext.ExecuteStoreCommand(...)和 ObjectContext.ExecuteStoreQuery<T>(...)。从函数名不难知道前者是为了执行某一并无返回集的SQL 命令，例如UPDATE，DELETE操作；后者是执行某一个查询，并可以将返回集转换为某一对象。

今天小试一把，上代码 

      using (var db = new CentaStaffEntities())      {        //查询 自定义实体        var presons = db.Database.SqlQuery<Person>("SELECT TOP 10 * FROM [CentaStaff].[CentaStaff].[Staff] WITH(NOLOCK)");        Console.WriteLine(presons.FirstOrDefault().CnName);        Console.WriteLine(presons.Count());        //查询 EF生成的实体        var staffs = db.Database.SqlQuery<Staff>("SELECT TOP 10 * FROM [CentaStaff].[CentaStaff].[Staff] WITH(NOLOCK)");        Console.WriteLine(staffs.FirstOrDefault().CnName);        Console.WriteLine(staffs.Count());        //多表查询 自定义实体         var tables = db.Database.SqlQuery<Tables>("SELECT TOP 10 SA.[AwardID],S.[StaffID],S.[CnName] FROM [CentaStaff].[CentaStaff].[Staff] S WITH(NOLOCK) INNER JOIN [CentaStaff].[CentaStaff].[StaffAward] SA WITH(NOLOCK) ON S.[StaffID]=SA.[StaffID]");        Console.WriteLine(tables.FirstOrDefault().CnName);        Console.WriteLine(tables.Count());        //修改和添加        //var execute = db.Database.ExecuteSqlCommand("UPDATE Person SET Name = 'Michael' WHERE PersonID = 1");        //if (execute > 0)        //{        //  Console.Write("execute success");        //}        //查询 参数传递        string sql = "SELECT TOP 10 * FROM [CentaStaff].[CentaStaff].[Staff] WITH(NOLOCK) WHERE 1=1 ";        sql += " AND CnName=@CnName";        List<SqlParameter> sqlparameters = new List<SqlParameter>();        sqlparameters.Add(new SqlParameter("@CnName", "张三"));        var parameters = db.Database.SqlQuery<Staff>(sql, sqlparameters.ToArray());        Console.WriteLine(parameters.FirstOrDefault().CnName);        Console.WriteLine(parameters.Count());      }

    //自定义实体类    public class Person    {      public string CnName      { get; set; }      public string Gender      { get; set; }    }    //多表查询    public class Tables    {      public Guid AwardID      { get; set; }      public Guid StaffID      { get; set; }      public string CnName      { get; set; }    }

在EF的项目，对于很耗性能的查询语句，采用SQL语句查询，会是一个比较好的选择。

原标题：EntityFramework 4/5/6 中执行自定义SQL语句

关键词：sql