CODEWITHSUNDEEP
c#asp.netlistlinq-to-sqldistinct
Niek Jonkman
Niek Jonkman

Reputation: 497

Distinct a list with objects by id

I have a program where there is a topic (like a forum), people can react to that topic.

USER:

  1. id
  2. first name
  3. last name

TOPIC:

  1. id
  2. subject

REACTION:

  1. id
  2. topic_id
  3. content

Code:

List<USER> ListOfAllUsers = new List<USER>();
var AllReactions = from r in db.REACTIONs
                   where r.topic_id == _topic_id
                   select r;

foreach (var itemX in AllReactions)
{
    ListOfAllUsers.Add(itemX.USER);
}

//Distinct the list of duplicates
var ListOfUsers = ListOfAllUsers.Distinct().ToList();

Now, the "distinct" list still has some duplicates, how do I distinct the list based on the user id's? Or maybe there is another better way to do this. Thanks in advance.

Upvotes: 12

Views: 25178

Answers (4)

Uwy
Uwy

Reputation: 21

MoreLinq (available on NuGet) has a DistincBy method that allow you to use a delegate as equality comparer.

So you only have to do something like this :

var ListOfUsers = ListOfAllUsers.DistinctBy(user => user.id).ToList();

Edit : MoreLinq Link

Upvotes: 2

Sergey Berezovskiy
Sergey Berezovskiy

Reputation: 236228

I suggest you to let database return distinct users for you:

    List<USER> ListOfAllUsers = 
         db.REACTIONs.Where(r => r.topic_id == _topic_id)
                     .Select(r => r.USER)
                     .Distinct()
                     .ToList();

That will be translated into single SQL query. Something like (assume your USER table has two columns - Id and Name):

SELECT 
    [Distinct1].[Id] AS [Id], 
    [Distinct1].[Name] AS [Name]
    FROM ( SELECT DISTINCT 
        [Extent2].[Id] AS [Id], 
        [Extent2].[Name] AS [Name]
        FROM  [dbo].[USER] AS [Extent1]
        INNER JOIN [dbo].[REACTION] AS [Extent2] 
            ON [Extent1].[Id] = [Extent2].[UserId]
        WHERE @id = [Extent1].[topic_id]
    )  AS [Distinct1]

Upvotes: 3

Avner Shahar-Kashtan
Avner Shahar-Kashtan

Reputation: 14700

Distinct has an overload that receives an instance of IEqualityComparer<T>, which is an object that contains the logic that allows LINQ to know which two objects are equal, and thus one should be eliminated.

You need to implement this (very simple) interface, something like this:

public class UserEqualityComparer : IEqualityComparer<User>
{
      public bool Equals(User x, User y)
      {
           return x.Id == y.Id;
      }

      public int GetHashCode (User obj)
      {
           return obj.Id.GetHashCode();
      }
}

And then pass an instance of UserEqualityComparer to Distinct():

var ListOfUsers = ListOfAllUsers.Distinct(new UserEqualityComparer()).ToList();

Upvotes: 4

Kamil Budziewski
Kamil Budziewski

Reputation: 23087

You can use GroupBy to achieve that

var ListOfUsers = ListOfAllUsers.GroupBy(x => x.Id)
                                  .Select(g => g.First())
                                  .ToList();

Upvotes: 30

Related Questions