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

Solution

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

Comments

[Sateesh Pagolu]

please explain your output. or show what you have tried so far.

[Sagar]

I Think there was a proble with max, every time it is giving max number

[Rahul Singh]

@Sagar - Glad to help :) But from next time don't forget to show your efforts.

[Rahul Singh]

Sagar - Please check my update. @petelids - Thanks :)