LINQ to SQL, get most recent records for given ID

Question

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 for 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!

Answer 1

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

Answer 2

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