Query and display in one view data coming from many to many with payload in Entity framework 6

Question

I have this complex model:

    public class Food
        {
        [Key]
        public int FoodID { get; set; }

        [Required]
        [DisplayName("Food Name")]
        public string FoodNameEN { get; set; }

        [Required]
        [DisplayName("Food Name")]
        public string FoodNameFR { get; set; }

        [Required]
        [DisplayName("Food Name")]
        public string FoodNameNL { get; set; }

        [Required]
        [DisplayName("Food Description")]
        public string FoodDescriptionEN { get; set; }

        [Required]
        [DisplayName("Food Description")]
        public string FoodDescriptionFR { get; set; }

        [Required]
        [DisplayName("Food Description")]
        public string FoodDescriptionNL { get; set; }

        public int CategoryID { get; set; }

        [Required]
        public bool Availability { get; set; }

        [Required]
        [DisplayName("Is Menu of the Day")]
        public bool DailyMenu { get; set; }

        public virtual Category Category { get; set; }
        public virtual ICollection<FoodAttributeValue> FoodAttributeValues { get; set; } = new HashSet<FoodAttributeValue>();
        public virtual ICollection<OrderDetail> OrderDetails { get; set; } = new HashSet<OrderDetail>();


 public class Category
    {
        [Key]
        public int CategoryID { get; set; }

        [Required(ErrorMessage = "Please enter a Category Name for English")]
        [DisplayName("Category Name")]

        public string CategoryEN { get; set; }

        [Required(ErrorMessage = "Please enter a Category Name for French")]
        [DisplayName("Category Name")]
        public string CategoryFR { get; set; }

        [Required(ErrorMessage = "Please enter a Category Name for Dutch")]
        [DisplayName("Category Name")]
        public string CategoryNL { get; set; }

        [Required]
        public bool Availability { get; set; }

        public virtual ICollection<Food> Foods { get; set; } = new HashSet<Food>();
    }

 public class Attribute
    {
        [Key]
        public int AttributeID { get; set; }
        public string AttributeNameEN { get; set; }
        public string AttributeNameFR { get; set; }
        public string AttributeNameNL { get; set; }
        public virtual ICollection<AttributeValue> AttributeValues { get; set; } = new HashSet<AttributeValue>();
    }

 public class AttributeValue
    {
        [Key]
        public int AttributeValueID { get; set; }
        public int AttributeID { get; set; }

        public string AttributeValueEN { get; set; }
        public string AttributeValueFR { get; set; }
        public string AttributeValueNL { get; set; }

        public virtual Attribute Attribute { get; set; }
        public virtual ICollection<FoodAttributeValue> FoodAttributeValues { get; set; } = new HashSet<FoodAttributeValue>();
    }

public class FoodAttributeValue
    {
        [Key]
        [Column(Order = 0), ForeignKey("Food")]
        public int FoodID { get; set; }

        [Key]
        [Column(Order = 1), ForeignKey("AttributeValue")]
        public int AttributeValueID { get; set; }

        public decimal Price { get; set; }
        public string CreatorUserName { get; set; }

        public DateTime DateCreated { get; set; }

        public virtual Food Food { get; set; }
        public virtual AttributeValue AttributeValue { get; set; }
    }

Attributes contains an attribute type like size or volume

AttributeValue contains the values for an attribute, like Normal Size, or Large.

FoodAttributeValue is a many to many table with payload "Price". So basically, the same Food comes in different sizes with different Prices.

My DatabaseContext:

  public class ModelContext: DbContext
  {
    public DbSet<Category> Categories { get; set; }
    public DbSet<Food> Foods { get; set; }
    public DbSet<Attribute> Attributes { get; set; }
    public DbSet<AttributeValue> AttributesValues { get; set; }
    public DbSet<FoodAttributeValue> FoodAttributeValues { get; set; }
  }

How can i query the model using linq, with the intention to have on the same view, the list of Foods that contain also the Attributes of the Food, the AttributeValues of it and eventually the Price?

Thanks in advance!

Answer 1

In MVC it is a best practice to create a ViewModel (or DTO) designed for the view you want to display. Generally, try to avoid using your entity POCOs in the view.

So for example:

public class FoodIndexViewModel
{
    // Add the various entity fields you want here.
    public int FoodID { get; set; }
    ...
    // Take all these annotations out of your entity model. They belong here.
    [Required]
    [DisplayName("Food Name")]
    public string FoodNameEN { get; set; }
    ...
    [Required(ErrorMessage = "Please enter a Category Name for English")]
    [DisplayName("Category Name")]
    public string CategoryEN { get; set; }

    public decimal Price { get; set; } 

    ... etc. etc.
}

Now you can compose a LINQ query to populate the view model. AutoMapper can greatly ease this process.

var listOfFoodsVM = context.Foods
    .Where(food => food.Availability)
    .Select(food => new FoodIndexViewModel
    {
        FoodId = food.FoodId,
        FoodNameEN = food.FoodNameEN,
        ... // other fields from food
        CategoryEN = food.Category.CategoryEN,
        // Here you would either pick a single value from the list based on your criteria
        Price = food.FoodAttributeValues.FirstOrDefault(fav => fav.AttributeValueID == 1).Price
        // or you could include a collection of FoodAttributes in your viewmodel:
        FoodAtributes = food.FoodAttributes
        ... etc.     
    }).ToList();

Now you pass this into your view.

return View(listOfFoodsVM );