号外号外: 原[图享网]更名为 码友网(codedefault.com) 啦,感谢大家一路上的陪伴与支持。代码的世界里,码友网与大家一起同行!

[Linq].NET/C#程序开发中如何像SQL语句一样做left join和group by 以及count统计查询?

.NET 作者: Rector 373阅读 0评论 0收藏 收藏本文

郑重申明:本文未经许可,禁止任何形式转载

问题描述

比如现在有SQL查询语句如下:

SELECT p.ParentId, COUNT(c.ChildId)
FROM ParentTable p
  LEFT OUTER JOIN ChildTable c ON p.ParentId = c.ChildParentId
GROUP BY p.ParentId

如何将这条SQL语句转换成对应的Linq查询语句呢?如下的写法是不正确的:

from p in context.ParentTable
join c in context.ChildTable on p.ParentId equals c.ChildParentId into j1
from j2 in j1.DefaultIfEmpty()
group j2 by p.ParentId into grouped
select new { ParentId = grouped.Key, Count = grouped.Count() }

正确的Linq to SQL又是如何写的呢?

方案一

from p in context.ParentTable
join c in context.ChildTable on p.ParentId equals c.ChildParentId into j1
from j2 in j1.DefaultIfEmpty()
group j2 by p.ParentId into grouped
select new { ParentId = grouped.Key, Count = grouped.Count(t=>t.ChildId != null) }

方案二

可以考虑使用子查询,如:

from p in context.ParentTable
let cCount =
(
  from c in context.ChildTable
  where p.ParentId == c.ChildParentId
  select c
).Count()
select new { ParentId = p.Key, Count = cCount } ;

方案三

from p in context.ParentTable
join c in context.ChildTable on p.ParentId equals c.ChildParentId into g
select new { ParentId = p.Id, Count = g.Count() }

如果使用Lambda查询表达式,可以写成这样的:

context.ParentTable
    .GroupJoin(
                   inner: context.ChildTable
        outerKeySelector: parent => parent.ParentId,
        innerKeySelector: child => child.ParentId,
          resultSelector: (parent, children) => new { parent.Id, Count = children.Count() }
    );

方案四

(from p in context.ParentTable
  join c in context.ChildTable
    on p.ParentId equals c.ChildParentId into j1
  from j2 in j1.DefaultIfEmpty()
     select new {
          ParentId = p.ParentId,
         ChildId = j2==null? 0 : 1
      })
   .GroupBy(o=>o.ParentId)
   .Select(o=>new { ParentId = o.key, Count = o.Sum(p=>p.ChildId) })

方案五

from p in context.ParentTable
join c in context.ChildTable on p.ParentId equals c.ChildParentId into joined
select new { ParentId = p.ParentId, Count = joined.Count() }

阅读了该文章的人还浏览了...

本文永久链接码友网 » [Linq].NET/C#程序开发中如何像SQL语句一样做left join和group by 以及count统计查询?

发布于: 2018-02-05 21:49:08
分享扩散: