Display Count of User reviews in a product

Question

I have a small ASP.NET MVC web application written in C# that allows users to add reviews to products.

Is it possible for me to return a count of how many reviews a user has made for a singular product in a simple display?;

'Username' has made X reviews on this product

Where X reviews is the number they have made.

I have 3 models: User, Product and Review

A user can have a collection of Products and Reviews
A product can have a collection of Reviews.

I think I am just confused on how I can get a count of reviews made by a specific user. I can get a count of all reviews made, just not individual user numbers on a single product. I can get a list of products for a user, a list of reviews for a product as they only look into a collection of another however this relies on looking for a collection inside a collection which I cannot do.

What I have tried so far - It doesn't work because I can't look for a collection in a collection:

DAO:

public int UserReviewCount (string UserId, int ProductId, Context context)
{
    Product product = context.Products.Find(ProductId)
    User user = context.Users.Find(UserId)
    return user.product.Reviews.Count(); // Can only do user.Reviews, user.products etc... 
}

Service:

public int UserReviewCount (string UserId, int ProductId)
{
    using (var context = new Context())
    {
        IList<Review> Reviews;
        Reviews = productDAO.GetReviews(id, context);
        return Reviews;
    }
}

GetReviews method in DAO:

public IList<Review> GetReviews(int id, Context context)
{
    Product product = context.Products.Find(id); 
    return product.Reviews.ToList();
}

Model definitions - User model:

[Key]
public string UserId { get; set; }
public string Name { get; set; }
public string Phone { get; set; }
public string Email { get; set; }
public virtual ICollection<Review> Reviews { get; set; }
public virtual ICollection<Product> Products { get; set; }

User database definition:

CREATE TABLE [dbo].[Users] 
(
    [UserId]  NVARCHAR (128) NOT NULL,
    [Name]    NVARCHAR (MAX) NULL,
    [Phone]   NVARCHAR (MAX) NULL,
    [Email]   NVARCHAR (MAX) NULL,

    CONSTRAINT [PK_dbo.Users] 
        PRIMARY KEY CLUSTERED ([UserId] ASC)
);

Review Model:

[Key]
public int ReviewId { get; set; }
public string Details { get; set; }
public int Rating { get; set; }

Review database definition:

CREATE TABLE [dbo].[Reviews] 
(
    [ReviewId] INT  IDENTITY (1, 1) NOT NULL,
    [Details] NVARCHAR (MAX) NULL,
    [Rating] INT NULL,
    [Product_ProductId] INT NULL,
    [User_UserId] NVARCHAR (128) NULL,

    CONSTRAINT [PK_dbo.Reviews] 
        PRIMARY KEY CLUSTERED ([ReviewId] ASC),
    CONSTRAINT [FK_dbo.Reviews_dbo.Products_Product_ProductId] 
        FOREIGN KEY ([Product_ProductId]) REFERENCES [dbo].[Products] ([ProductId]),
    CONSTRAINT [FK_dbo.Reviews_dbo.Users_User_UserId] 
        FOREIGN KEY ([User_UserId]) REFERENCES [dbo].[Users] ([UserId])
);

Product model:

[Key]
public int ProductId { get; set; }
public string Name { get; set; }
public virtual ICollection<Review> Reviews{ get; set; }

Product database definition:

CREATE TABLE [dbo].[Products] 
(
    [ProductId] INT IDENTITY (1, 1) NOT NULL,
    [Name] NVARCHAR (MAX) NULL,
    [User_UserId]  NVARCHAR (128) NULL,

    CONSTRAINT [PK_dbo.Products] 
        PRIMARY KEY CLUSTERED ([ProductsId] ASC),
    CONSTRAINT [FK_dbo.Products_dbo.Users_User_UserId] 
        FOREIGN KEY ([User_UserId]) REFERENCES [dbo].[Users] ([UserId])
);

Just to note, I also have 2 methods which will add the review to either the user collection or the product collection.

Review to Product collection:

public void AddReviewToCollection(Review review, int ProductId, Context context)
{
    context.Products.Find(ProductId).Reviews.Add(review);
    context.SaveChanges();
}

Review to User collection:

public void AddReviewToCollection(Review review, string UserId, Context context)
{
    context.Users.Find(UserId).Reviews.Add(review);
    context.SaveChanges();
}

Answer 1

public long UserReviewCount(string UserId, int ProductId, Context context)
{
   Product product = context.Products.Find(ProductId);
   User user = context.Users.Find(UserId);
   return product.Reviews.Count(x => x.UserId == UserId && x.ProductId == ProductId);
}