LINQ-왼쪽 조인, 그룹화 및 카운트

LINQ-왼쪽 조인, 그룹화 및 카운트

---

이 SQL이 있다고 가정 해 봅시다.

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

이것을 LINQ to SQL로 어떻게 번역 할 수 있습니까? COUNT (c.ChildId)에 멈춰 생성 된 SQL이 항상 COUNT (*)를 출력하는 것 같습니다. 내가 지금까지 얻은 것입니다 :

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

감사합니다!

---

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 
let cCount = p.Children.Count()
select new { ParentId = p.Key, Count = cCount } ;

---

늦은 답변 :

당신은 가입 왼쪽을 필요가 없습니다 당신이하고있는 모든 카운트 인 경우 모두에서 (). 주 join...into사실로 번역되어 GroupJoin있는 같은 그룹 반환 new{parent,IEnumerable<child>}방금 호출 할 필요가 있으므로 Count()그룹에를 :

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

확장 메소드 구문에서 a join into는 is와 같지만 GroupJoin( is는 join없는 intois Join) :

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) })

---

While the idea behind LINQ syntax is to emulate the SQL syntax, you shouldn't always think of directly translating your SQL code into LINQ. In this particular case, we don't need to do group into since join into is a group join itself.

Here's my solution:

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

Unlike the mostly voted solution here, we don't need j1, j2 and null checking in Count(t => t.ChildId != null)

참고URL : https://stackoverflow.com/questions/695506/linq-left-join-group-by-and-count