ASP.NET MVC Trouble getting data and related data to the view. (View Model Pattern?)

Question

My site has Users. My site has Items. Users can leave a Review of an Item. Users can leave a Rating of an Item. When users rate an Item, they rate it in different categories: a rating for build quality, a rating for usefulness, a rating for aesthetic appeal.

So, in my database, my Reviews table would have one entry and my Ratings table would have three entries per Item per User.

Now on my Item page I want to display the ten most recent Reviews and Ratings.

How can I grab the Reviews and corresponding Ratings and send them all to the view for display?

(I'm using LINQ to SQL).

Thanks.

EDIT: Sorry, thought I had included enough info. Here is some more info about my database. Let's say my Site has items of different sorts. Let's say it contains info on Cars and Books. Obviously when rating a car, you have different criteria than when rating a book.

I am putting all my ratings data into one Ratings table.

I have a RatingsCategories table. It contains two colums:

RatingCategoryID  (Identity column)
RatingCategory    (name of category)

Category "Cover Art" would apply to books. Category "Performance" would apply to cars.

I have a Ratings table. It contains the following columns:

RatingID  (Identity column)
EntityID  (Unique identifier among Cars AND Books)
UserID    (Identifier of the user who left the rating)
RatingCategoryID  (Reference to the category; FK from the RatingsCategories table)
Rating    (int;  the rating given)
DateModified (DateTime)

So, this table can contain all the ratings of the books and the cars. One book might have three different ratings per user (Cover Art, Content, Binding Quality, etc) and one car might have three different ratings per user (Appearance, Performance, Ease of Maintenance, etc)

Likewise my Review table can contain reviews for Books and Cars. It looks like this:

ReviewID  (Identity column)
EntityID  (Unique identifier among Cars AND Books)
UserID    (Identifier of the user who left the rating)
Body      (Text of review)
DateModified  (DateTime)

User's can Rate and Review each item once. So, if when a site visitor navigates to a Book's page, I want to display the last 10 Reviews and corresponding Ratings. That means I need to grab the 10 most recent Review records for that Book. I also need to grab the ten most recent full Ratings for that book (same EntityID and same UserID as the Reviews) which could be three records for each Rating.

Does that make sense?

What's the best way to do this?

Am I crazy in this approach and database schema?

Thanks for your patience.

Answer 1

Without more info I'm guessing something like this would work:

Item.Reviews.OrderByDescending(d => d.date).Take(10)

Answer 2

Not much to go on here but I'll make a few assumptions and try to answer your question.

Assumptions:

1. If you are using LINQ to SQL you are probably using the repository pattern.
2. You are using a relational database structure that would involve tables to tie Items to Reviews and Items to Ratings.
3. I am sort of guessing at what your database structure would be.

I'll work backwards to help you with how to set this up. Your main top level view that will be showing the reviews and ratings would need to be strongly typed to a view model that contains another view model for the reviews and ratings. Your view could contain a reference to a partial since the view will be easily reusable.

<div class="latest-reviews-ratings">
    <% Html.RenderPartial("ReviewsAndRatings", Model.Reviews); %>
</div>

Then your partial can look however you want it to look...

<div class="reviews">
    <% foreach (var reviewModel in Model) {%>
        <div class="review">
            Review of: <i><%: reviewModel.Item.Name %></i> by <b><%: reviewModel.Reviewer.Username %></b>.
            <div><%: reviewModel.Review.Value %></div>

            <ul class="ratings">
                <% foreach (var ratingModel in reviewModel.Ratings) {%>
                    <li>
                        <b><%: ratingModel.Rater.Username %></b> rated this a <%: ratingModel.Rating.Value %> on <%: ratingModel.Rating.CategoryName %>.
                    </li>
                <% } %>
            </ul>
        </div>
    <% } %>
</div>

Your view model might look like this:

public class IndexViewModel
{
    public IEnumerable<ReviewViewModel> Reviews { get; set; }
}

The ReviewViewModel could look like this:

public class ReviewViewModel
{
    public User Reviewer { get; set; }
    public Item Item { get; set; }
    public Review Review { get; set; }
    public IEnumerable<RatingViewModel> Ratings;
}

RatingViewModel:

public class RatingViewModel
{
    public User Rater { get; set; }
    public Rating Rating { get; set; }
}

Now all you have to do is wire it up in your controller. Pull out your ItemReview records and create view models from them.

var itemReviews = _itemReviewRepository.FindAll().OrderByDescending(p => p.Review.DateAdded).Take(10);
var reviewViewModels = itemReviews.Select(p =>
    new ReviewViewModel
    {
        Item = p.Item,
        Reviewer = p.User,
        Review = p.Review,
        Ratings = ReviewRatingService.GetRatingViewModelForReview(p.ReviewId)
    });

You can trap some of that logic into your repository or a service class if you want. ReviewRatingService class would use a linq query like this: (note: there is most definitely a better query but this is the one I quickly came up with)

return (from rating in dataContext.Ratings
        join itemRating in dataContext.ItemRatings
            on rating.Id equals itemRating.RatingId
        join itemReview in dataContext.ItemReviews
            on itemRating.ItemId equals itemReview.ItemId
        join review in dataContext.Reviews
            on itemReview.ReviewId equals review.Id
        where review.Id == reviewId
        select new RatingViewModel
                {
                    Rater = itemRating.User,
                    Rating = rating
                });

Now you can populate the index view model with that data

var model = new IndexViewModel
{
    Reviews = reviewViewModels
};

and pass the main model back to your view.

return View(model);

I just wrote the web app. It was actually pretty fun.