Entity Framework的查询与操作

直接执行sql语句

    MyDbContext myc = new MyDbContext();

            //直接执行sql语句
            List<UserInfo> userlist = myc.UserInfo.SqlQuery("select * from [UserInfoes]").ToList();


            //执行sql语句返回任意对象
            var result = myc.Database.SqlQuery(typeof(Company), "select * from Company");
            foreach (Company item in result)//可以直接遍历 
            {
                
            }

            //返回任意对象方法2
            List<Company> resultt = myc.Database.SqlQuery<Company>("select * from Company").ToList();
            

            //执行查询添加参数
            List<UserInfo> uilist = myc.Database.SqlQuery<UserInfo>("select * from UserInfoes where UserName = @username", new SqlParameter("UserName", "李黑")).ToList();

直接执行存储过程语句

            //执行存储过程并取得返回值
            int prlr = myc.Database.SqlQuery<int>("exec [ProSelectCount] '1'").SingleOrDefault();


            //执行命令语句
            int rcount = myc.Database.ExecuteSqlCommand("update [UserInfoes] set UserName = '小光' where id=1");

Ef  Join

            ef  lamdba写法             

  List<UserScore> uslist = tn.Users.Join(tn.Score, a => a.Id, b => b.UsersId,
                  (m, n) => new UserScore() { UserName = m.UserName, Score = n.Score1, Sub = n.Sub }).ToList();

             ef  linq写法             

           var uslist = from u in tn.Users
                        join s in tn.Score on u.Id equals s.UsersId
                        select new UserScore { UserName = u.UserName, Score = s.Score1, Sub = s.Sub };

      

Ef left Join

 var uslist = (from u in tn.Users
                        join s in tn.Score on u.Id equals s.UsersId into jointemp
                        from js in jointemp.DefaultIfEmpty()
                         select new UserScore { UserName = u.UserName, Score = js.Score1, Sub = js.Sub  }
                        
                        ).ToList();

         ef left join lamdba写法1           

var result = tn.Users.GroupJoin(tn.Score, a => a.Id, b => b.UsersId,
               (m, n) =>new {username = m.UserName,myscore = n}).ToList();

          ef left join lamdba写法      返回部分字段

          var sqls = tn.Users.GroupJoin(tn.Score, a => a.Id, b => b.UsersId,
              (m, n) => new { UserName = m.UserName, scoreo = n.Select(b => new { b.Score1, b.Sub }) });

     

         如果这样写，生成的sql是子查询

       

      

           其实left join返回是个集合也不错,因为left join本来就是适用于，

           一对多得，比如一个考生可能会考很多科目就可以用

           Left join,其实score就是一个集合，因为很有多科目

  

       ef left join lamdba写法2 : 真正生成的是left join
        

            var rlist2 = sgEn.Users.GroupJoin(sgEn.Score, a => a.Id, b => b.UsersId, (m, n) => new
              {
                  Users = m,
                  Score = n
              }).SelectMany(
                   a => a.Score.DefaultIfEmpty(),
                   (x, y) => new { User = x.Users, Score = y }
              ).Select(s => new
              {
                  username = s.User.UserName,
                  sub = s.Score.Sub
              });

Ef三表Join

    ef 三表 join linq写法  (三表left join)   

 var uslist = from u in tn.Users
                         join s in tn.Score on u.Id equals s.UsersId into tempjoin
                         from djoin in tempjoin.DefaultIfEmpty()
                         join p in tn.User_Parent on u.Id equals p.UsersId into tempjoint
                         from p2 in tempjoint.DefaultIfEmpty()
                         select new UserScore
                         {
                             UserName = u.UserName,
                             FatherName = p2.Father,
                             Score = djoin.Score1
                         };

             ef 三表 join 后分组并求函数

var uslist = from u in tn.Users
                         join s in tn.Score on u.Id equals s.UsersId into tempjoin
                         from djoin in tempjoin.DefaultIfEmpty()
                         join p in tn.User_Parent on u.Id equals p.UsersId into tempjoint
                         from p2 in tempjoint.DefaultIfEmpty()
                         select new UserScore
                         {
                             UserName = u.UserName,
                             FatherName = p2.Father,
                             Score = djoin.Score1
                         } into selectr
                         group selectr by selectr.FatherName into grouptem
                         select new DTO_ScoreGroup
                         {
                             FatherName = grouptem.Key,
                             Max = grouptem.Max(a=>a.Score),
                             Min = grouptem.Min(a => a.Score),
                         };

     ef 三表join lamdba写法          

 var tjoinresult = tn.Users.Join(tn.Score, a => a.Id, b => b.UsersId, (m, n) => new {
               username = m.UserName, 
               Score1 = n.Score1,
               uid = m.Id
               }).Join(tn.User_Parent, a => a.uid,b=>b.UsersId, (m, n) =>new {
                     username =m.username,
                     Score1 = m.Score1,
                     Father = n.Father
               }).ToList();

Ef三表 left Join lamdba写法

   var query = sge.Users.
                GroupJoin(sge.UScore, a => a.Id, b => b.UsersId, (x, y) => new //left join 分数表
            {
                username = x.UserName,
                uscore = y,
                uid = x.Id
            }).GroupJoin(sge.User_Parent, a => a.uid, b => b.UsersId, (x, y) => new //left join父母表
            {  //在借助一次匿名类型
                usename = x.username,
                uscore = x.uscore,
                uparent = y
            }).SelectMany(a => a.uscore.DefaultIfEmpty(), (x, y) => new   //指定分数表的部分字段
            {
                username = x.usename,
                sub = y.Sub,
                score = y.Score,
                uparnt = x.uparent
            }).SelectMany(a => a.uparnt.DefaultIfEmpty(), (x, y) => new
            {  //指定父母表的部分字段
                username = x.username,
                sub = x.sub,
                score = x.score,
                parent = y.Father
            });

lamdba   二表 left Join 后分组函数

    var query = sge.Users.GroupJoin(sge.Score, a => a.Id, b => b.UsersId, (x, y) => new
             {
                 username = x.UserName,
                 uscore = y
             }).SelectMany(a => a.uscore.DefaultIfEmpty(), (x, y) => new
             {
                 username = x.username,
                 score = y.Score1
             }).GroupBy(a => a.username) //left join完后看成一个整体在分组不管前面在复杂都是一样
             .Select(a => new
             {
                 usenrame = a.Key,
                 sum = a.Sum(b => b.score)
             });