Map of products - category relationship EF c#

Question

I have three models as below.

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Description { get; set; }
    public Decimal Price { get; set; }
    public bool Live { get; set; }
}

public class Category
{
    public int Id { get; set; }
    public string Name { get; set; }

    [ForeignKey(nameof(ParentCategory))]
    public int? ParentId { get; set; }

    public virtual Category ParentCat { get; set; }
    public ICollection<Category> Children { get; set; }
}


public class CategoryProduct
{
    public int Id { get; set; }
    public int CatId { get; set; }
    public int ProductId { get; set; }
}

I would need one or more linq to entities query to have a hierarchical map of the categories with the quantity of the related live products.

I give an example of the result I would like to obtain:

Computer (5)

Computer -> Windows (3)

Computer -> Windows -> lenovo (2)

Computer -> Windows -> HP (1)

Computer -> Apple (2)

Computer -> Apple -> Macbook air (2)

Queries should filter only categories that have products live true

Thank you

Answer 1

@Salvatore Rizzo

IMHO, you need to use Linqpad with an existing database with data in the tables and play with Linqpad to get your desired results using Linqpad and Linq queries against the database tables. This is assuming you know how to use Linq. By using Linqpad, it will help in the long run on how to use Linq.

If you are posting requirements as to what you want to do, then follow your requirements and do it.

https://www.linqpad.net/

https://learn.microsoft.com/en-us/samples/dotnet/try-samples/101-linq-samples/

https://www.tektutorialshub.com/linq-to-entities/linq-to-entities-tutorial/