Getting Distinct rows with LINQ

Question

i currently have mutliple records for each "FileStreamID" but i only want to get one distinct one based on the latest "request date"

here is what i have now:

 var resultx = from v in ctx.vEmailSents
                             where v.Lab_ID == 44
                                 && v.UploadDate >= this.BeginDate
                             select new
                             {

                                 FileStreamID = v.FileStream_ID,
                                 UploadDate = v.UploadDate,
                                 RequestDate = v.DateSent,
                                 TypeDesc = v.TypeDesc
                             };

and now i get about 33 records (but i should only get 12)

how can i change it to only give me distinct rows on filestream IDs?

Sergey Berezovskiy · Accepted Answer · 2012-12-21 15:51:03Z

4

Group your vEmailSent objects by FileStream_ID and select from each group item with latest date:

 var resultx = from v in ctx.vEmailSents
               where v.Lab_ID == 44
                     && v.UploadDate >= this.BeginDate
               group v by v.FileStream_ID into g
               select g.OrderByDescending(x => x.DateSent)
                       .FirstOrDefault() into lastV
               select new {
                      FileStreamID = lastV.FileStream_ID,
                      UploadDate = lastV.UploadDate,
                      RequestDate = lastV.DateSent,
                      TypeDesc = lastV.TypeDesc                          
               };

answered Dec 21, 2012 at 15:51

Sergey Berezovskiy

237k44 gold badges441 silver badges468 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Madam Zu Zu Over a year ago

thank you!! i think that did it! all hail to the all mighty LINQ god, @lazyberezovsky!

phil soady Over a year ago

BTW, the post that was deleted, works if the select new {x ...} contains only the fields to be compared. Often .Distinct() is the better the solution

Christoffer Lette · Accepted Answer · 2012-12-21 15:52:10Z

0

var results = resultx
    .GroupBy(x => x.FileStreamID)
    .Select(g => g.OrderByDescending(x => x.RequestDate).First());

answered Dec 21, 2012 at 15:52

Christoffer Lette

15k7 gold badges54 silver badges59 bronze badges

Collectives™ on Stack Overflow

Getting Distinct rows with LINQ

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related