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();