Linq to SQL Using Group by and Count(Distinct)

LINQ to SQL using GROUP BY and COUNT(DISTINCT)

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

Distinct with group by in Linq

This should give you correct result:-

var result = Userlist.GroupBy(x => new { x.ID, x.UserName })
                     .Select(x => new
                                 {
                                     ID = x.Key.ID,
                                     UserName = x.Key.UserName,
                                     BookType = x.Max(z => z.BookType),
                                     BookId = x.Max(z => z.BookId)
                                 });

Update:

Although you agreed to answer, I somehow missed your requirement and the answer which I posted above is wrong since it fetch the maximum BookType & BookId. Below is the query to fetch the distinct count:-

var result = Userlist.GroupBy(x => new { x.ID, x.UserName })
                     .Select(x => new
                         {
                            ID = x.Key.ID,
                            UserName = x.Key.UserName,
                            BookType = x.Select(z => z.BookType).Distinct().Count(),
                            BookId = x.Select(z => z.BookId).Distinct().Count()
                         });

LINQ to SQL group by shows wrong count of distinct foreign key

I suspect your current code is not doing what you think it is doing.

What you likely think it is doing is counting the number of distinct PID (or SID). But what it is actually doing is counting the number of distinct results of s.p.PID != null.

Now, the result of that s.p.PID != null calculation is a bool (i.e. true /
false) - and due to the nature of your data and query, it will always return true. So, you have a set of data all containing true. You run Distinct on it, and get a single true. Then you Count that single true and you get 1.

To fix this, you likely need to change:

ChildCount = g.Select(s=>s.p.PID != null).Distinct().Count(),
GrandChildCount = g.Select(s => s.c.CID != null).Distinct().Count(),

to something like:

ChildCount = g.Select(s=>s.p.PID).Where(z => z != null).Distinct().Count(),
GrandChildCount = g.Select(s => s.c.CID).Where(z => z != null).Distinct().Count(),

The .Where(z => z != null) bit is optional - you could remove it if you like. I have included it there only to try and replicate what you were likely trying to do with your original code (i.e. exclude null PIDs and SIDs).

Convert SQL Distinct, Count & GroupBy to a Linq query with Navigation

I think this is what you are looking for:

var results = from q in query
    group q by new {q.SoldToContactID,
                    FirstName=q.Contact.FirstName,
                    LastName=q.Contact.LastName} into g
    select new LeadBuyersByStateItem {
        ContactID = g.Key.SoldToContactID,
        FirstName = g.Key.FirstName,
        LastName = g.Key.LastName,
        ContactCount = g.Count()
     };

You need to group by several columns using an anonymous type

Linq to SQL get distinct records grouped by date

you can try the following sample code:

var results = _customerTable.GroupBy(c => new {c.Date, c.CUSTOMER_TYPE_ID}).Select(g => new {g.Key.Date, g.Key.CUSTOMER_TYPE_ID, MyCount = g.Count()});

using this way you can get number of recors per Date and CUSTOMER_TYPE_ID.

---

Related Topics