关闭

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

801人阅读 评论(0) 收藏 举报
分类:

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



0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:118902次
    • 积分:3131
    • 等级:
    • 排名:第11522名
    • 原创:187篇
    • 转载:124篇
    • 译文:0篇
    • 评论:11条
    最新评论