how to get data from multiple related tables and p

Question

i have three table in my data base category and Subcategory and product:

category has many Subcategory Subcategory has many product

so i'm trying to join this three table to get the data for each product and display it in a view like that:

 public ActionResult Index()
    {
        var memberId = WebSecurity.CurrentUserId;

        var productsCompany = db.Products
        .Join(db.SubCategorys, p => p.SubCategoryID, subcat => subcat.SubCategoryID,
                     (p, subcat) => new { p = p, subcat = subcat })
        .Join(db.Categorys, temp0 => temp0.subcat.CategoryID, cat => cat.CategoryID,
                     (temp0, cat) => new { temp0 = temp0, cat = cat })
        .Join(db.Sizes, temp1 => temp1.temp0.p.SizeID, s => s.SizeID,
                     (temp1, s) => new { temp1 = temp1, s = s })
        .Join(db.Colors, temp2 => temp2.temp1.temp0.p.ColorID, c => c.ColorID,
                     (temp2, c) => new { temp2 = temp2, c = c })
        .Join(db.Stores, temp3 => temp3.temp2.temp1.temp0.p.StoreID, st => st.StoreID,
                     (temp3, st) => new { temp3 = temp3, st = st })
        .Join(db.Companyies, temp4 => temp4.st.CompanyID, camp => camp.CompanyID,
                     (temp4, camp) => new { temp4 = temp4, camp = camp })
        .Where(temp5 => (temp5.camp.UserID == memberId))
        .Select(temp5 => new
        {
            CategoryName = temp5.temp4.temp3.temp2.temp1.cat.CategoryName,
            SubCategoryName = temp5.temp4.temp3.temp2.temp1.temp0.subcat.SubCategoryName,
            ProductImageURL = temp5.temp4.temp3.temp2.temp1.temp0.p.ProductImageURL,
            ProductName = temp5.temp4.temp3.temp2.temp1.temp0.p.ProductName,
            Price = temp5.temp4.temp3.temp2.temp1.temp0.p.Price,
            SizeName = temp5.temp4.temp3.temp2.s.SizeName,
            ColorName = temp5.temp4.temp3.c.ColorName,
            Quantity = temp5.temp4.temp3.temp2.temp1.temp0.p.Quantity,
            Sales = temp5.temp4.temp3.temp2.temp1.temp0.p.Sales,
            Discount = temp5.temp4.temp3.temp2.temp1.temp0.p.Discount,
            StoreName = temp5.temp4.st.StoreName,
            CompanyName = temp5.camp.CompanyName
        }).ToList();

        return View(productsCompany);
    }

but in this way it take time to get the data so i' trying another way like that:

public ActionResult Index()
    {
        var memberId = WebSecurity.CurrentUserId;

        var productsCompany = db.Products.Include(p => p.Color).Include(p => p.Size).Include(p => p.Store).Include(p => p.SubCategory); 

        return View(productsCompany.ToList());
    }

but i cant figure out how can i get the data from the threed table category in this way and this only for display the data in this index view any idea on how can i create a new product from this three tables and thanks for any help

Update my classes is:

Product class

public class Product
{
    [ScaffoldColumn(false)]
    public int ProductID { get; set; }

    [DisplayName("Image URL")]
    //[DataType(DataType.Url)]
    public string ProductImageURL { get; set; }

    [Required(ErrorMessage = "Product Name is required")]
    [DisplayName("Product Name")]
    [StringLength(40)]
    public string ProductName { get; set; }

    [Required(ErrorMessage = "Price is required")]
    [DisplayName("Product Price")]
    [DataType(DataType.Currency)]
    [Range(1, 5000.00, ErrorMessage = "Price must be between 1 SP and 5000.00 SP")]
    public decimal Price { get; set; }

    [Required(ErrorMessage = "Quantity is required")]
    [DisplayName("Product Quantity")]
    public int Quantity { get; set; }

    [DisplayName("Sales Amount")]
    [Range(0, 100, ErrorMessage = "sale Prsent must be between 1 and 100")]
    public int Sales { get; set; }

    //Exclude
    public decimal Discount { get; set; }

    [Required(ErrorMessage = "Color is required")]
    [DisplayName("Color")]
    public int ColorID { get; set; }
    public virtual Color Color { get; set; }

    [Required(ErrorMessage = "Size is required")]
    [DisplayName("Size Type")]
    public int SizeID { get; set; }
    public virtual Size Size { get; set; }

    [Required(ErrorMessage = "Store is required")]
    [DisplayName("Store")]
    public int StoreID { get; set; }
    public virtual Store Store { get; set; }

    [Required(ErrorMessage = "Category Type is required")]
    [DisplayName("Sub Category Type")]
    public int SubCategoryID { get; set; }
    public virtual SubCategory SubCategory { get; set; }


}

Subcategory class:

 public class SubCategory
{

    [ScaffoldColumn(false)]
    public int SubCategoryID { get; set; }

    [Required(ErrorMessage = "Category Type is required")]
    [DisplayName("Category Type")]
    [StringLength(40)]
    public string SubCategoryName { get; set; }

    [Required(ErrorMessage = "Category is required")]
    [DisplayName("Category")]
    public int CategoryID { get; set; }
    public virtual Category Category { get; set; }
    public virtual ICollection<Product> Products { get; set; }
}

Category class:

 public class Category
{

    [ScaffoldColumn(false)]
    public int CategoryID { get; set; }

    [Required(ErrorMessage = "Category Name is required")]
    [DisplayName("Category Name")]
    [StringLength(50)]
    public string CategoryName { get; set; }
    public virtual ICollection<SubCategory> SubCategorys { get; set; }
}

Answer 1

Assuming that your category, sub category and product table are relate with forign key

If you are using Entity Framework to access the data from database and ProxyCreationEnabled is set to true (by default it is true) in your dbContext class constructor, The product table will automatically retrieve the related category and subcategory data for the specific product.

For example:

Product objProduct =  _datacontext.Product.where(p=> p.productId.equals(pid));

and your Product table is defined as below:

public class Product
{
    public int productId {get; set;}
    ...
    public virtual IEnumarable<Category> pCategories {get; set;} 
    public virtual IEnumarable<SubCategory> pSubCategories {get; set;}
}

so now your objProduct will automatically store the related Category and SubCategory in pCategories and pSubCategories respectively. you can access it directly, No need to Join or Inclue the related tables explicitly.

Now pass the object to the view as a Model

public ActionResult Index()
{
    Product objProduct =  _datacontext.Product.SingleOrDefault(p=> p.productId.equals(pid));

    return View(objProduct);
}

and use the Model in the view as per requirement.