Ordered Distinct

Question

I have the following

IList<Project> projects = (from update in dbContext.TicketUpdates
               where update.TicketUpdatesEmployeeID == Profile.EmployeeID
               orderby update.TicketUpdatesEndDate descending
               select update.Ticket.Project).Distinct().ToList();

I understand that Distinct() doesn't guarantee order, but I can't reorder it afterwards because the projection loses the field I"m using to order. How can I work around this?

Answer 1

I think your best bet is to post process to preserve ordering:

var projects = (from update in dbContext.TicketUpdates
           where update.TicketUpdatesEmployeeID == Profile.EmployeeID
           orderby update.TicketUpdatesEndDate descending
           select update.Ticket.Project);
var seen = new HashSet<Project>();
foreach (var project in projects)
{
    if (seen.Add(project))
    {
        // A distinct project
    }
}

Or you could abuse GroupBy:

var projects = dbContext.TicketUpdates
    .Where(uu => uu.TicketUpdatesEmployeeID == Profile.EmployeeID)
    .GroupBy(uu => uu.Ticket.Project)
    .OrderByDescending(gg => gg.Max(uu => uu.TicketUpdatesEndDate))
    .Select(gg => gg.Key);

By using GroupBy across uu.Ticket.Project each update will be grouped by their associated project. If you have 10 projects and 30 updates spread amongst them, you'll have 10 groups--one per project--in the output of that stage. Next, we order the groups by their newest end date which preserves the descending order you were looking for. Finally, we select the key from each IGrouping which is the project.

Answer 2

new solution using GroupBy
Update: Actually there is a much easier solution:

IList<Project> projects = (from update in dbContext.TicketUpdates where update.TicketUpdatesEmployeeID == Profile.EmployeeID)
    .GroupBy(tu=>tu.Ticket.Project)
    .Select(group=>group.First())
    .OrderByDescending(tu=>tu.TicketUpdatesEndDate)
    .Select(tu=>tu.Ticket.Project)
    .ToList();

(and I just saw that while I was writing this, others have posted similar answers)

---

Old solution using a custom IEqualityComparer (I am not sure whether this will work with linq2sql)

There is a Distinct overload that takes a custom IEqualityComparer. So do a Distinct over TicketUpdates with a custom IEqualityComparer, before projecting your data.

The IEqualityComparer should count all TicketUpdates as equal if they have the same project. That way TicketUpdates having the same Project will be discarded.

(note that you do not control which TicketUpdates with the same Project will be discarded. So if these TicketUpdates with the same project have different EndDates, you would need a solution involving GroupBy instead.

IList<Project> projects = (from update in dbContext.TicketUpdates where update.TicketUpdatesEmployeeID == Profile.EmployeeID)
    .Distinct(new ProjectComparer())
    .OrderByDescending(tu=>tu.TicketUpdatesEndDate)
    .Select(tu=>tu.Ticket.Project)
    .ToList();


// Your comparer should look somewhat like this 
// (untested code! And I do not know all the details about your class structure)
class ProjectComparer : IEqualityComparer<TicketUpdates>
{
    // Products are equal if their names and product numbers are equal. 
    public bool Equals(TicketUpdates x, TicketUpdates y)
    {

        //Check whether the compared objects reference the same data. 
        if (Object.ReferenceEquals(x, y)) return true;

        //Check whether any of the compared objects is null. 
        if (Object.ReferenceEquals(x, null) || Object.ReferenceEquals(y, null))
            return false;

        //Check whether projects are equal. 
        //(perhaps do a null check for Ticket!)
        return x.Ticket.Project== y.Ticket.Project;
    }

    // If Equals() returns true for a pair of objects  
    // then GetHashCode() must return the same value for these objects. 

    public int GetHashCode(TicketUpdates x)
    {
        //Check whether the object is null 
        if (Object.ReferenceEquals(x, null)) return 0;

        // null check for Ticket and Project?
        return x.Ticket.Project.GetHashCode();
    }

}