LINQ to SQL distinct records

Question

I want to get distinct records by using LINQ not on the base of Id but on other field of the table i.e. date. The table schema is ID (Unique Identifier),date (dateTime), Desc varachar(50)

Records are like : 1st row: Id 61DDF6A2-E5B7-4E88-91FE-5C63EF8E15D8 date 8/1/2010 Desc Abc

1st row: Id 61DDF6A2-E5B7-4E88-91FE-5C63EF8E15D8 date 8/1/2010 Desc Abc

2nd row: Id 61DDF6A2-E5B7-4E88-91FE-5C63EF8E15B1 date 8/1/2010 Desc Abc

3rd row: Id 61DDF6A2-E5B7-4E88-91FE-5C63EF8E15D8 date 8/1/2010 Desc Xyz

4th row: Id 61DDF6A2-E5B7-4E88-91FE-5C63EF8E15D9 date 8/3/2010 Desc AAAA

5th row: Id 51DDF6A2-E5B7-4E88-91FE-5C63EF8E15D9 date 8/3/2010 Desc AAAA

Now what i want is distinct records on the base of date

Required result is like :

id 61DDF6A2-E5B7-4E88-91FE-5C63EF8E15D8 date 8/1/2010 Desc Abc

Id 61DDF6A2-E5B7-4E88-91FE-5C63EF8E15D9 date 8/3/2010 Desc AAAA

Please response me as soon as possible.

Thanks

Answer 1

GroupBy(row => row.DateField) 
   .Select(group => group 
       .OrderBy(row => row.AnotherColumn) 
       .First() 

This will give you the first row for each distinct datefield value.

Row r1 = new Row { ID = "61DDF6A2-E5B7-4E88-91FE-5C63EF8E15D8", Date = Convert.ToDateTime("08/01/10"), Desc = "Abc" };
Row r2 = new Row { ID = "61DDF6A2-E5B7-4E88-91FE-5C63EF8E15B1", Date = Convert.ToDateTime("08/01/10"), Desc = "Abc" };
Row r3 = new Row { ID = "61DDF6A2-E5B7-4E88-91FE-5C63EF8E15D8", Date = Convert.ToDateTime("08/01/10"), Desc = "Xyz" };
Row r4 = new Row { ID = "61DDF6A2-E5B7-4E88-91FE-5C63EF8E15D9", Date = Convert.ToDateTime("08/03/10"), Desc = "AAAA" };
Row r5 = new Row { ID = "51DDF6A2-E5B7-4E88-91FE-5C63EF8E15D9", Date = Convert.ToDateTime("08/03/10"), Desc = "AAAA" };

List<Row> rows = new List<Row> { r1, r2, r3, r4, r5};

var result = rows
    .GroupBy(r => r.Date) 

    // Now we are simply selecting the first item of each subgroup. 
   .Select(group => group
       .OrderBy(row => row.ID) 
       .First());

Answer 2

Heh, this is the same as my answer here from a few days ago.

You can use .Distinct with an IEqualityComparer

var distinctdates = from dc.MyTable.Distinct(new MyComparer());

A good example of an IEqualityComparer is here - http://msdn.microsoft.com/en-us/library/bb338049.aspx

If you check out the ProductComparer example, all you'd probably need to do is replace the "Check whether the products' properties are equal" part with the check you want to make (in your case you'd be comparing dates) and that's pretty much it.