Distinct with group by in Linq

Question

ID  UserName    BookType    BookID

1   Krish       1           1
1   Krish       1           2
1   Krish       2           1
1   Krish       2           2
2   Ram         1           1    
3   Raj         1           1
3   Raj         1           2

I have above table and I want to get the distinct BookType count and BookID count for each user, I am able to write this in SQL Server but when I come to LINQ I am unable to write the query.

I need the following output

ID  UserName    BookType    BookID

1   Krish       2           2
2   Ram         1           1 
3   Raj         1           2

please explain your output. or show what you have tried so far. — Sateesh Pagolu, Sep 30 '15 at 10:55
Possible duplicate of [LINQ to SQL using GROUP BY and COUNT(DISTINCT)](https://stackoverflow.com/questions/448203/linq-to-sql-using-group-by-and-countdistinct) — Drag and Drop, Jul 17 '18 at 08:13

Rahul Singh · Accepted Answer · 2015-09-30T11:25:24.477

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

I Think there was a proble with max, every time it is giving max number — Sagar, Sep 30 '15 at 11:03
@Sagar - Glad to help :) But from next time don't forget to show your efforts. — Rahul Singh, Sep 30 '15 at 11:04

Distinct with group by in Linq

1 Answers1