LINQ to SQL, get most recent records for given ID

intHi,

Pretty new to LINQ.

I have a Ratings table.

A User adds a set of Ratings for an Entity There can be more than one Rating set f开发者_运维问答or an Entity per User.

For example, let's say the Entity is a car. The car is rated on Appearance and Performance. And a User can rate the given car's appearance and performance more than once. So my table looks something like this (the Rating field is not an Identity column; it is an int on a scale of 1 - 10):

ReviewID     UserID     EntityID     CatID     Rating    Body               DateSubmitted
1            3          6            1         7         "drives great"     8/01/2010 02:36:28 PM
2            3          6            2         8         "looks great"      8/01/2010 02:36:28 PM
3            3          6            1         2         "broke down"       8/18/2010 11:39:58 PM
4            3          6            2         1         "paint flaked off" 8/18/2010 11:39:58 PM

Now, I have a helper method where I supply the UserID and the EntityID and I want to return the most recent set of Ratings (into a ViewModel that includes the Rating Category).

public static IQueryable<RatingViewModel> GetRatingViewModel(int EntityID, int UserID)
    {
        DB _db = new DB();

        var a =

            from rating in _db.Ratings
            join ratingCat in _db.RatingCategories
                on rating.RatingCategoryID equals ratingCat.RatingCategoryID
            where rating.UserID == UserID
                && rating.EntityID == EntityID
            select new RatingViewModel
             {
                 Rater = rating.User,
                 RaterRating = rating,
                 RatingCategory = ratingCat

             };

        return a;
    }

What kind of "where" or "group by" or "order by" do I need to add to ONLY grab the most recent set of Ratings for the given UserID and EntityID?

Thanks!

Consider ordering by the DateSubmitted on the return of the method, and then taking the number of entries that you'd like.

var a = from rating in _db.Ratings
        join ratingCat in _db.RatingCategories
            on rating.RatingCategoryID equals ratingCat.RatingCategoryID
        where rating.UserID == UserID
            && rating.EntityID == EntityID
        orderby rating.DateSubmitted descending

        select new RatingViewModel
         {
             Rater = rating.User,
             RaterRating = rating,
             RatingCategory = ratingCat

         }
         .Take(10);

.OrderByDescending(a => a.DateSubmitted).FirstOrDefault()