[Solved] How do display top 5 products from specific category [closed]

Question

First, let’s define what we need. We need a table (view) with these fields: ProductId, ProductName, ProductViewCount, and RootCategoryId.

Imagine, the Category table has RootCategoryId field already. Then we can use this query to receive the result:

SELECT P.Id AS 'ProductId', P.Name AS 'ProductName', PVC.ProductViewCount, C.RootCategoryId
FROM Category C
  INNER JOIN ProductCategory PC ON PC.CategoryId = C.Id
  INNER JOIN Product P ON PC.ProductId = P.Id
  INNER JOIN ProductViewCount PVC ON P.Id = PVC.ProductId

Unfortunately the Category table hasn’t necessary fields. So we need a table (instead of Category) with fields CategoryId and RootCategoryId.

For top categories CategoryId and RootCategoryId are the same:

SELECT Id AS 'CategoryId', Id AS 'RootCategoryId'
FROM Category
WHERE ParentCategoryId = 0

For descendant categories CategoryId is the Id, and RootCategoryId is the same as the parent. So we can write CTE:

WITH RecursiveCategory(CategoryId, RootCategoryId)
AS
(
    SELECT Id AS 'CategoryId', Id AS 'RootCategoryId'
    FROM Category
    WHERE ParentCategoryId = 0
    UNION ALL
    SELECT C.Id AS 'CategoryId', RC.RootCategoryId
    FROM Category C
        INNER JOIN RecursiveCategory RC ON C.ParentCategoryId = RC.CategoryId
)
. . .

Now let’s put the pieces together. We need a VIEW:

CREATE VIEW ProductWithRootCategory
AS
WITH RecursiveCategory(CategoryId, RootCategoryId)
AS
(
    SELECT Id AS 'CategoryId', Id AS 'RootCategoryId'
    FROM Category
    WHERE ParentCategoryId = 0
    UNION ALL
    SELECT C.Id AS 'CategoryId', RC.RootCategoryId
    FROM Category C
        INNER JOIN RecursiveCategory RC ON C.ParentCategoryId = RC.CategoryId
)
SELECT P.Id AS 'ProductId', P.Name AS 'ProductName', PVC.ProductViewCount, RC.RootCategoryId
FROM RecursiveCategory RC
  INNER JOIN ProductCategory PC ON PC.CategoryId = RC.CategoryId
  INNER JOIN Product P ON PC.ProductId = P.Id
  INNER JOIN ProductViewCount PVC ON P.Id = PVC.ProductId

Now you can add the view to EF and use:

int rootCategoryId = 1; // Is's Computers
var productsInRootCategory = ProductWithRootCategory.Where(pwrc => pwrc.RootCategoryId == rootCategoryId);
var top5Products = productsInRootCategory.OrderBy(pwrc => pwrc.ProductViewCount).Take(5);

Accepted Answer