Entity Framework常用查询,EF join,EF多表联查

Ef 两表Join

linq写法

//两表join linq写法
var query = from u in oae.Users
        join p in oae.Parent on u.Id equals p.ParentId
        select new
        {
           username = u.UserName,
           father = p.Father
        };

lamdba写法:

/*
   第一个参数:   join的表
   第二,三参数: 连接条件
   第四个参数:   返回值
*/
   var query = oae.Users.Join(oae.Parent, a => a.Id, b => b.ParentId, (a, b) => new
   {
     username = a.UserName,
     fahter = b.Father
   });

Ef 两表 left Join

linq写法:

//两表left join linq写法
var query = from u in oae.Users
        join p in oae.Parent on u.Id equals p.ParentId into jtemp
        from leftjoin in jtemp.DefaultIfEmpty()
        select new
        {
         username = u.UserName,
         father = leftjoin.Father
        };

lamdba写法:

//两表left join lamdba写法
var query = oae.Users.GroupJoin(oae.Parent, a => a.Id, b => b.ParentId, (a, b) => new
            {
                username = a.UserName,
                parent = b
            }).SelectMany(a => a.parent, (m, n) => new
            {
                username = m.username,
                father = n.Father
            });

lamdba的写法主要用到了groupjoin与SelectMany,这里简单解释一下:

groupjoin:    用于查询一对多的关系很方便,所以得数据格式就是1对多的关系

SelectMany:    可以解析集合中含有集合的情况(也就是1对多的表现)为单一对象

Ef三表Join

linq写法:

//三表join linq写法
var queru = from u in oae.Users
        join p in oae.Parent on u.Id equals p.ParentId
        join s in oae.Score on u.Id equals s.UsersId
        select new
        {
         username = u.UserName,
         fahter = p.Father,
         sub = s.Sub,
         score = s.Score1
        };

lamdba写法:

//三表join lamdba写法
var query = oae.Users.Join(oae.Parent, a => a.Id, b => b.ParentId, (a, b) => new
       {
          uid = a.Id,
          username = a.UserName,
          father = b.Father
        }).Join(oae.Score, a => a.uid, b => b.UsersId, (m, n) => new
         {
          username = m.username,
          father = m.father,
          sub = n.Sub,
          score = n.Score1
        });

其实和两表join类似,往后面点就行了

Ef三表left Join

Linq写法:

//三表left join linq写法
var query = from u in oae.Users
        join p in oae.Parent on u.Id equals p.ParentId into ptemp
        join s in oae.Score on u.Id equals s.UsersId into stemp
        from leftp in ptemp.DefaultIfEmpty()
        from lefts in stemp.DefaultIfEmpty()
        select new
        {
           username = u.UserName,
           father = leftp.Father,
           sub = lefts.Sub,
           score = lefts.Score1
         };

lamdba写法:

//三表left join lamdba写法
var query = oae.Users.GroupJoin(oae.Parent, a => a.Id, b => b.ParentId, (a, b) => new
        {
            uid = a.Id,
            username = a.UserName,
            parent = b
        }).GroupJoin(oae.Score, a => a.uid, b => b.UsersId, (m, n) => new
        {
            username = m.username,
            uid = m.uid,
            score = n,
            parent = m.parent
        }).SelectMany(a => a.parent.DefaultIfEmpty(), (m, n) => new
        {
            username = m.username,
            fahter = n.Father,
            score = m.score
        }).SelectMany(a => a.score.DefaultIfEmpty(), (m, n) => new
        {
            usernaem = m.username,
            father = m.fahter,
            sub = n.Sub,
            score = n.Score1
        });

lamdba写法2:上面是现join完在selectmany,也可以先selectmany了在join第三张表

//三表left join lamdba写法2
var query = oae.Users.GroupJoin(oae.Parent, a => a.Id, b => b.ParentId, (a, b) => new
        {
            uid = a.Id,
            username = a.UserName,
            parent = b
        }).SelectMany(a => a.parent.DefaultIfEmpty(), (m, n) => new
        {
            uid = m.uid,
            username = m.username,
            father = n.Father
        }).GroupJoin(oae.Score, a => a.uid, b => b.UsersId, (m, n) => new
        {
            username = m.username,
            father = m.father,
            score = n
        }).SelectMany(a => a.score, (m, n) => new
        {
            username = m.username,
            father = m.father,
            sub = n.Sub,
            score = n.Score1
        });

单表分组函数

linq:

//linq
var query = from score in oae.Score
            group score by score.Sub into grouptemp
            select new
            {
                sub = grouptemp.Key,
                sum = grouptemp.Sum(a => a.Score1),
                max = grouptemp.Max(a => a.Score1),
                min = grouptemp.Min(a => a.Score1),
                avg = grouptemp.Average(a => a.Score1)
            };

lamdba:

//lamdba
var query = oae.Score.GroupBy(a => a.Sub).Select(grouptemp => new
{
    sub = grouptemp.Key,
    sum = grouptemp.Sum(a => a.Score1),
    max = grouptemp.Max(a => a.Score1),
    min = grouptemp.Min(a => a.Score1),
    avg = grouptemp.Average(a => a.Score1)
}).Where(a => a.max > 60);

var result = query.ToList();

分组函数后接一点条件

linq:

//linq
var query = from score in oae.Score
            group score by score.Sub into grouptemp
            where grouptemp.Sum(a=>a.Score1)>60
            select new
            {
                sub = grouptemp.Key,
                sum = grouptemp.Sum(a => a.Score1),
                max = grouptemp.Max(a => a.Score1),
                min = grouptemp.Min(a => a.Score1),
                avg = grouptemp.Average(a => a.Score1)
            };
linq写法2:
//linq
var query = from score in oae.Score
            group score by score.Sub into grouptemp
            select new
            {
                sub = grouptemp.Key,
                sum = grouptemp.Sum(a => a.Score1),
                max = grouptemp.Max(a => a.Score1),
                min = grouptemp.Min(a => a.Score1),
                avg = grouptemp.Average(a => a.Score1)
            } into temp
            where temp.max > 60
            select new
            {
                sub = temp.sub,
                sum = temp.sum
            };
var result = query.ToList();
  • 5
    点赞
  • 39
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

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

余额充值