Distinct Count and SUM sub queries in Joined LINQ to SQL

Question

How can I replicate the following query in Linq?

SELECT 
    A.GroupID,
    COUNT(DISTINCT B.GroupName) AS [Number of SubGroups],
    SUM(B.AmountRaised) AS [Total raised by All Sub Groups]
FROM
    dbo.Group A
    INNER JOIN dbo.SubGroupActivity B
        ON A.ID = B.GroupID
GROUP BY A.GroupID
HAVING SUM(B.AmountRaised) > 0

Perhaps my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) might help you. — NetMage, Jan 27 '21 at 20:31
@NetMage, perhaps not. COUNT(DISTINCT...). supported only by one LINQ provider and it is definitely not EF. — Svyatoslav Danyliv, Jan 27 '21 at 21:48
You have `A.ID equals B.GroupID` in the `INNER JOIN` but `A.GroupID` in the `SELECT` - is that correct? — NetMage, Jan 28 '21 at 01:24

score 0 · Answer 1 · answered Jan 28 '21 at 01:29

If I understand your query correct, and you are using LINQ to SQL, here is how I would translate:

var ans = from a in dbo.Group
          join b in dbo.SubGroupActivity on a.GroupID equals b.GroupID
          group new { a, b } by a.GroupID into abg
          let TotalRaised = abg.Sum(ab => ab.b.AmountRaised)
          where TotalRaised > 0
          select new {
            GroupID = abg.Key,
            Number = abg.Select(ab => ab.b.GroupName).Distinct().Count(),
            TotalRaised
          };

Distinct Count and SUM sub queries in Joined LINQ to SQL

1 Answers1