How do display top 5 products from specific category

Question

I want to fetch top 5 most popular product under specific category say computer.

This is my class file:

  public partial class Category {
       public int Id { get; set; }
       public string Name { get; set; }
       public int ParentCategoryId { get; set; } //reference to Id 
       public ICollection<Category> _subcategories;
  } 

  public partial class ProductCategory {
       public int Id { get; set; }
       public int ProductId { get; set; }
       public int CategoryId { get; set; }
       public virtual Category Category { get; set; }
       public virtual Product Product { get; set; }
  }

 public partial class Product {
       public int Id { get; set; }
       public string Name { get; set; }                     
       public int ProductViewcount { get; set; }//indicated how many times product has been viewed means most popular product.
 }

Here a sample fiddle which contain records: http://www.sqlfiddle.com/#!3/20cba

Final output:

ProductId ProductName

1 hp

2 compaq

3 lenovo

Here problem is Computer is my main category and laptop is child category of Computer so when i say get top 5 Product of computer i want to retrieve child category records also like in fiddle i want to get all records of child category that is Laptop

I know this that I have to perform order by on ProductViewCount and get top 5 products.

If I understand you correctly, you need recursive SQL select or LINQ expression? You can't do it with LINQ, but you can do it with SQL. Use [common table expressions, CTE](http://msdn.microsoft.com/en-us/library/ms175972.aspx). The example D shows how to do it. — Mark Shevchenko, Dec 25 '14 at 09:51
oh i thought if anybody post solution in the form of sql query then i would get idea from that sql query and write linq query from that sql query.why this is not possible with linq??? — Maria Pithia, Dec 25 '14 at 09:56
You can't do it as a one shot LINQ query without having a CTE. What you can do is create the CTE as a SQL View and then query that for what you want. — Corey Adler, Dec 25 '14 at 17:03
@IronMan84:can you please post your CTE sql Query because i dont know what is CTE and i havent use CTE.so please just post your CTE query and check in Fiddle for the correct output — Maria Pithia, Dec 26 '14 at 03:33
@MarkShevchenko:can you please post CTE Sql Query because i havent use CTE — Maria Pithia, Dec 26 '14 at 03:34

Mark Shevchenko · Accepted Answer · 2014-12-26T09:17:06.243

1

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

edited Dec 26 '14 at 09:17

answered Dec 26 '14 at 06:19

Mark Shevchenko

7,937
1
25
29

for checking purpose that it is returning correct list of products or not i want to do this checking in your query. – Maria Pithia Dec 26 '14 at 09:21
right now i want to hardcode this ParentCategorName parameter and directly specify in your query to check whether this View is working or not – Maria Pithia Dec 26 '14 at 09:25
I rewrote my answer. Now there's no `ParentCategoryName`, the new VIEW is called `ProductWithRootCategory`. – Mark Shevchenko Dec 26 '14 at 09:33
sir the condition you are checking on server side i want to check that condition in my query only.i will pass that CategoryId from server as a parameter to this view.so checking will be done on View part only and not on server side – Maria Pithia Dec 26 '14 at 09:40
A view is not a stored procedure, it has no parameters. So you should use `ProductWithRootCategory` in your LINQ query. At the end of my answer there is an example of LINQ expression. – Mark Shevchenko Dec 26 '14 at 09:45
i am using Entity Framework code first.so directly i can use like this as you have specified? – Maria Pithia Dec 26 '14 at 09:51
sorry i was not knowing that we cannot pass parameter to view.sorry sir – Maria Pithia Dec 26 '14 at 10:01
http://stackoverflow.com/questions/5889905/how-do-i-define-a-database-view-using-entity-framework-4-code-first – Mark Shevchenko Dec 26 '14 at 10:09
i cant uppvote your ans which i want to upvote because i dont have 15 reputation – Maria Pithia Dec 26 '14 at 10:12
Damn genius sir.very very nice.this query is a sign of genius sir.thank you so much sir.really thank you – Maria Pithia Dec 26 '14 at 10:26

How do display top 5 products from specific category

1 Answers1

Linked