LINQ to SQL most recent set of records for a given ID?

Question

This one should be easy.

I have a Reviews table with records. The columns are EntityID, UserID, Body, DateModified. A User can submit multple entries for an Entity. I want to select all the Reviews that are the most recent entries for a given entity per UserID.

So the table might look like this:

EntityID     UserID     Body     DateModified
1            101        "hey"    8/22/2010 11:36:47 PM
1            101        "dude"   8/11/2010 04:15:43 PM
1            108        "brah"   8/21/2010 11:31:11 PM
1            108        "sup?"   8/14/2010 10:00:00 PM

I've got something like this:

 var itemReviews = db.Reviews
                             .Where(x => x.EntityID == EntityID)
                             .OrderByDescending(x => x.DateSubmitted)
                             ;

What do I need to add to get only the records for the most recent EntityID?

Thanks.

Answer 1

In order to get the single most recent review:

var mostRecentReview = db.Reviews
                         .Where(x => x.EntityID == EntityID)
                         .OrderByDescending(x => x.DateSubmitted)
                         .First();

In order to get the most recent review for each user:

var reviews = db.Reviews
                .Where(x => x.EntityID == EntityID)
                .GroupBy(x => x.UserID)
                .Select(gr => new {
                    UserID = gr.Key,
                    MostRecentReview = gr.OrderByDescending(x => x.DateSubmitted)
                                         .First()
                });

Answer 2

If you want a set of items returned (sql top/limit) you can use Take

var itemReviews = db.Reviews
                    .Where(x => x.EntityID == EntityID)
                    .OrderByDescending(x => x.DateSubmitted)
                    .Take(4);

If you also want to do some paging you can use Skip

var itemReviews = db.Reviews
                    .Where(x => x.EntityID == EntityID)
                    .OrderByDescending(x => x.DateSubmitted)
                    .Skip(pageNo *pageSize).Take(pageSize);

Answer 3

many way you can use,one of the way is :

var mostRecentReview = db.Reviews
                     .Where(x => x.EntityID == EntityID).Max(x =>x.DateModified);