CODEWITHSUNDEEP
linq-to-sql
munish
munish

Reputation: 39

LINQ to SQL distinct records

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

Upvotes: 0

Views: 1803

Answers (2)

Frank Tzanabetis
Frank Tzanabetis

Reputation: 2836

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.

Upvotes: 1

Dave Barker
Dave Barker

Reputation: 6437

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

Upvotes: 0

Related Questions