CODEWITHSUNDEEP
c#linq
Ben
Ben

Reputation: 4319

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

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

Upvotes: 0

Views: 110

Answers (5)

DaEagle
DaEagle

Reputation: 136

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

Upvotes: 2

Cory Nelson
Cory Nelson

Reputation: 30021

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

Upvotes: 1

Aducci
Aducci

Reputation: 26694

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

Upvotes: 1

Fratyx
Fratyx

Reputation: 5797

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

Upvotes: 1

gabba
gabba

Reputation: 2880

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

Upvotes: 2

Related Questions