Why doesn't this Linq query bring back distinct (unique) results?

Question

I have the following query:

 List<Meeting> meetings = 
    (from m in crdb.tl_cr_Meetings
    join p in crdb.tl_cr_Participants on m.MeetingID equals p.MeetingID
    where p.TimeOut == null
    select new Meeting
      {
       MeetingID = m.MeetingID,
       MeetingName = m.MeetingName,
       HostUserName = m.HostUsername,
       BlueJeansMeetingID = m.tl_cr_BlueJeansAccount.MeetingID,
       StartTime = m.StartTime,
       Participants = (from pa in crdb.tl_cr_Participants
                       where pa.MeetingID == m.MeetingID
                       select pa.tl_cr_BlueJeansAccount.DisplayName).ToList()
              }).Distinct().ToList();

And I want it to bring back a list of unique meetings. For some reason it brings back an entry for every participant, even though the data is identical:

enter image description here

Am I missing a grouping somewhere?

EDIT

The meeting class is currently very basic:

 public class Meeting
{
    public int MeetingID { get; set; }
    public string MeetingName { get; set; }
    public string HostUserName { get; set; }
    public DateTime StartTime { get; set; }
    public List<string> Participants { get; set; }
    public string BlueJeansMeetingID { get; set; }
}

Can you show the code for the `Meeting` class? Does it implement `IEquatable`? You might want to see this [question](http://stackoverflow.com/questions/1365748/distinct-not-working-with-linq-to-objects) — juharr, Oct 17 '14 at 17:37

score 2 · Answer 1 · answered Oct 17 '14 at 17:54

You can use the way that juharr advise you, or you can implement comparer as separate class that implements IEqualityComparer interface and pass this comparer to distinct.

public class MeetingComparer : IEqualityComparer<Meeting>
{
    public bool Equals (Meeting x, Meeting y)
    {
        return x.smth.Equals (y.smth);
    }

    public int GetHashCode (Meeting obj)
    {
        return obj.smth.GetHashCode ();
    }
}

DaEagle · Accepted Answer · 2014-10-17T18:12:00.760

I believe the reason you get an entry for every participant is that you perform two joins. You need to do a groupjoin.

 var meetings = crdb.tl_cr_Meetings.GroupJoin(crdb.tl_cr_Participants,
                                                k => k.MeetingID,
                                                k => k.MeetingID,
                                                (o,i) => new Meeting
                                                {
                                                   MeetingID = o.MeetingID,
                                                   MeetingName = o.MeetingName,
                                                   HostUserName = o.HostUsername,
                                                   BlueJeansMeetingID = o.tl_cr_BlueJeansAccount.MeetingID,
                                                   StartTime = o.StartTime,
                                                   Participants = i.Select(s => s.DisplayName)
                                                }).ToList();

This is perfect. I've not come across GroupJoin before. Have looked at this http://www.dotnetperls.com/groupjoin and now understand how the above works! — Ben, Oct 17 '14 at 18:21

score 1 · Answer 3 · answered Oct 17 '14 at 17:55

1

I think you just have to delete the 3rd line of your code (join ...).

answered Oct 17 '14 at 17:55

Fratyx

5,717
1
12
22

Only trouble here is that I need the join in to check the meetings only contain participants where p.TimeOut is null. – Ben Oct 17 '14 at 18:11

score 1 · Answer 4 · answered Oct 17 '14 at 17:55

Is this Linq to Entities? Regardless, I would remove the Distinct and add a group by.

List<Meeting> meetings = 
    (from m in crdb.tl_cr_Meetings
    join p in crdb.tl_cr_Participants on m.MeetingID equals p.MeetingID
    where p.TimeOut == null
    group m by new { m.MeetingID, m.MeetingName, m.HostUsername, MeetingID2 = m.tl_cr_BlueJeansAccount.MeetingID, m.StartTime } into m
    select new Meeting
    {
      MeetingID = m.Key.MeetingID,
      MeetingName = m.Key.MeetingName,
      HostUserName = m.Key.HostUsername,
      BlueJeansMeetingID = m.Key.MeetingID2,
      StartTime = m.Key.StartTime,
      Participants = (from pa in crdb.tl_cr_Participants
                       where pa.MeetingID == m.Key.MeetingID
                       select pa.tl_cr_BlueJeansAccount.DisplayName).ToList()
    }).ToList();

a group join could clean this query up quite well. – Cory Nelson Oct 17 '14 at 17:57 — Cory Nelson, Oct 17 '14 at 17:57
@CoryNelson - what do you mean? – Aducci Oct 17 '14 at 17:58 — Aducci, Oct 17 '14 at 17:58

score 1 · Answer 5 · answered Oct 17 '14 at 18:04

gabba explained why your code is failing. Here's a way you can re-work your query to not even need a Distinct() and make it a little cleaner looking, using a group join:

from m in crdb.tl_cr_Meetings
join p in crdb.tl_cr_Participants on new { m.MeetingID, null }
                  equals new { p.MeetingID, p.TimeOut } into meetingParticipants
select new Meeting
{
    MeetingID = m.MeetingID,
    MeetingName = m.MeetingName,
    HostUserName = m.HostUsername,
    BlueJeansMeetingID = m.tl_cr_BlueJeansAccount.MeetingID,
    StartTime = m.StartTime,
    Participants = meetingParticipants
                      .Select(x => x.tl_cr_BlueJeansAccount.DisplayName)
                      .ToList()
}

Why doesn't this Linq query bring back distinct (unique) results?

5 Answers5