We have a requirement for a list of itineraries with multple itinerary items show only single itinerary in the list with details of one of the items selected based on some custom sorting logic. So I needed to group by itinerary ID and sort by custom Comparison and select the first in each group.
Below is a LinqPad file that I've used for testing:
void Main()
{
// http://smehrozalam.wordpress.com/2009/12/29/linq-how-to-get-the-latest-last-record-with-a-group-by-clause/
// http://stackoverflow.com/questions/6963707/linq-query-group-by-and-selecting-first-items/7456167#7456167
var rows=new List<MyRow>()
{
new MyRow(1,ProductType.F,new DateTime(2012,01,01),"A")
,new MyRow( 1,ProductType.C, new DateTime(2012,01,01),"B")
,new MyRow(1,ProductType.H, new DateTime(2012,01,01),"C")
,new MyRow(2,ProductType.C, new DateTime(2012,01,01),"D")
,new MyRow(2,ProductType.H, new DateTime(2012,01,01),"E")
,new MyRow(3,ProductType.F, new DateTime(2012,02,01),"F")
,new MyRow(3,ProductType.F, new DateTime(2012,03,01),"G")
,new MyRow(3,ProductType.F, new DateTime(2012,01,05),"H")
,new MyRow(3,ProductType.H, new DateTime(2011,01,01),"I")
,new MyRow(4,ProductType.C, new DateTime(2012,01,01),"J")
,new MyRow(5,ProductType.H, new DateTime(2012,01,01),"K")
,new MyRow(6,ProductType.C, new DateTime(2012,01,01),"L")
,new MyRow(6,ProductType.H, new DateTime(2011,01,01),"M")
} ;
var firstsInGroups= from p in rows
//where conditions or joins with other tables to be included here
group p by p.ID into grp
select grp.First();
firstsInGroups.Dump();
var firstsByCompareInGroups= from p in rows
group p by p.ID into grp
select grp.OrderBy(a => a, new CompareRows()).First();
firstsByCompareInGroups.Dump();
}
// Define other methods and classes here
public class MyRow
{ public int ID;
public ProductType Type;
public DateTime StartTime;
public string OtherData;
public MyRow( int id, ProductType type, DateTime startTime, string otherData)
{
ID=id;
Type=type;
StartTime=startTime;
OtherData= otherData;
}
}
public enum ProductType
{F,C,H}
// http://msdn.microsoft.com/en-us/library/bb549422.aspx
public class CompareRows : IComparer<MyRow>
{
// Because the class implements IComparer, it must define a
// Compare method. The method returns a signed integer that indicates
// whether s1 > s2 (return is greater than 0), s1 < s2 (return is negative),
// or s1 equals s2 (return value is 0). This Compare method compares strings.
public int Compare(MyRow r1, MyRow r2)
{
if(r1.Type==r2.Type)
{
return DateTime.Compare(r1.StartTime, r2.StartTime);
}
//F is the best type
else if (r1.Type==ProductType.F)
{
return -1;
}
else if (r2.Type==ProductType.F)
{
return 1;
}
else //for C and H min Date is better
{ int compareDates=DateTime.Compare(r1.StartTime.Date, r2.StartTime.Date);
if(compareDates!=0)
{
return compareDates;
}
else//For the same date C is better
{
if (r1.Type==ProductType.C)
{
return -1;
}
else if (r2.Type==ProductType.C)
{
return 1;
}
Debug.Assert(false,"should not be here");
return 0;
}
}
}
}