LINQ to SQL using GROUP BY and COUNT(DISTINCT)

by

There isn’t direct support for COUNT(DISTINCT {x})), but you can simulate it from an IGrouping<,> (i.e. what group by returns); I’m afraid I only “do” C#, so you’ll have to translate to VB…

 select new
 {
     Foo= grp.Key,
     Bar= grp.Select(x => x.SomeField).Distinct().Count()
 };

Here’s a Northwind example:

    using(var ctx = new DataClasses1DataContext())
    {
        ctx.Log = Console.Out; // log TSQL to console
        var qry = from cust in ctx.Customers
                  where cust.CustomerID != ""
                  group cust by cust.Country
                  into grp
                  select new
                  {
                      Country = grp.Key,
                      Count = grp.Select(x => x.City).Distinct().Count()
                  };

        foreach(var row in qry.OrderBy(x=>x.Country))
        {
            Console.WriteLine("{0}: {1}", row.Country, row.Count);
        }
    }

The TSQL isn’t quite what we’d like, but it does the job:

SELECT [t1].[Country], (
    SELECT COUNT(*)
    FROM (
        SELECT DISTINCT [t2].[City]
        FROM [dbo].[Customers] AS [t2]
        WHERE ((([t1].[Country] IS NULL) AND ([t2].[Country] IS NULL)) OR (([t1]
.[Country] IS NOT NULL) AND ([t2].[Country] IS NOT NULL) AND ([t1].[Country] = [
t2].[Country]))) AND ([t2].[CustomerID] <> @p0)
        ) AS [t3]
    ) AS [Count]
FROM (
    SELECT [t0].[Country]
    FROM [dbo].[Customers] AS [t0]
    WHERE [t0].[CustomerID] <> @p0
    GROUP BY [t0].[Country]
    ) AS [t1]
-- @p0: Input NVarChar (Size = 0; Prec = 0; Scale = 0) []
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.1

The results, however, are correct- verifyable by running it manually:

        const string sql = @"
SELECT c.Country, COUNT(DISTINCT c.City) AS [Count]
FROM Customers c
WHERE c.CustomerID != ''
GROUP BY c.Country
ORDER BY c.Country";
        var qry2 = ctx.ExecuteQuery<QueryResult>(sql);
        foreach(var row in qry2)
        {
            Console.WriteLine("{0}: {1}", row.Country, row.Count);
        }

With definition:

class QueryResult
{
    public string Country { get; set; }
    public int Count { get; set; }
}

More Related Contents:

  1. Returning IEnumerable vs. IQueryable
  2. Difference Between Select and SelectMany
  3. LINQ – Left Join, Group By, and Count
  4. When should I dispose of a data context
  5. How can I conditionally apply a Linq operator?
  6. LINQ to SQL – Left Outer Join with multiple join conditions
  7. LINQ to SQL and a running total on ordered results
  8. Understanding .AsEnumerable() in LINQ to SQL
  9. TransactionScope vs Transaction in LINQ to SQL
  10. How to return anonymous type from c# method that uses LINQ to SQL [duplicate]
  11. How to write Asynchronous LINQ query?
  12. Convert Linq Query Result to Dictionary
  13. Am I misunderstanding LINQ to SQL .AsEnumerable()?
  14. How do you perform a CROSS JOIN with LINQ to SQL?
  15. LINQ Where Ignore Accentuation and Case
  16. Operation could destabilize the runtime?
  17. How to select only the records with the highest date in LINQ
  18. Get table-data from table-name in LINQ DataContext
  19. Linq query with nullable sum
  20. Custom Method in LINQ to SQL query
  21. Max or Default?
  22. How to make LINQ execute a (SQL) LIKE range search
  23. Will using LINQ to SQL help prevent SQL injection
  24. How do I do a left outer join with Dynamic Linq?
  25. Last and LastOrDefault not supported
  26. Linq “Could not translate expression… into SQL and could not treat it as a local expression.”
  27. LINQ to SQL Where Clause Optional Criteria
  28. Linq where column == (null reference) not the same as column == null
  29. Is Injection Possible through Dynamic LINQ?
  30. If Else in LINQ

Leave a Comment