Entity Framework的查询与操作

Entity Framework 专栏收录该内容
11 篇文章 0 订阅



直接执行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)
             });



  • 0
    点赞
  • 0
    评论
  • 1
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

©️2021 CSDN 皮肤主题: 编程工作室 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值