How to create viewmodel ,controller & view to join many to many table in asp.net mvc Entity framework

Question

I want to join & display "ProductId,Quantity,UnitPrice,Discount & Total" columns from tblOrder ,tblCustomer ,tblProduct tables where OrderNo= givenId .

OrderNo is given through a textbox.This is how it should be

DataBase

Order Table

public partial class tblOrder
{
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
    public tblOrder()
    {
        this.tblProducts = new HashSet();
    }

    [Key]
    public int OrderNo { get; set; }
    public Nullable Quantity { get; set; }
    public Nullable Discount { get; set; }
    public Nullable Total { get; set; }
    public Nullable SubTotal { get; set; }
    public Nullable DiscountTotal { get; set; }
    public Nullable Tax { get; set; }
    public Nullable NetTotal { get; set; }
    public int CustomerCode { get; set; }

    public virtual tblCustomer tblCustomer { get; set; }
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
    public virtual ICollection tblProducts { get; set; }
}

Product Table

public partial class tblProduct
{
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
    public tblProduct()
    {
        this.tblOrders = new HashSet();
    }
    [Key]
    public int ProductId { get; set; }
    public string UnitPrice { get; set; }

    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
    public virtual ICollection tblOrders { get; set; }
}

Customer Table

public partial class tblCustomer
{
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
    public tblCustomer()
    {
        this.tblOrders = new HashSet();
    }

    [Key]
    public int CustomerCode { get; set; }
    public string CustomerName { get; set; }

    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
    public virtual ICollection tblOrders { get; set; }
}

This is the ViewModel I created

public class OrderCustomerProductViewModel
{
    public OrderCustomerProductViewModel() { }
    public OrderCustomerProductViewModel(tblOrder orderow, tblCustomer customerow, tblProduct productrow)
    {
        OrderNo = orderow.OrderNo;
        CustomerName = customerow.CustomerName;
        ProductId = productrow.ProductId;
        Quantity = orderow.Quantity;
        UnitPrice = productrow.UnitPrice;
        Discount = orderow.Discount;
        Total = orderow.Total;
    }

    public int OrderNo { get; set; }
    public string CustomerName { get; set; }
    public int ProductId { get; set; }
    public Nullable Quantity { get; set; }
    public string UnitPrice { get; set; }
    public Nullable Discount { get; set; }
    public Nullable Total { get; set; }
}

I tried to send all the data to view using a list as follows , but there were some errors encountered . It says

ICollection doesn't contain a definition for ProductId and UnitPrice

Please help me to fix this error. Thanks in advance

public ActionResult Index(int id)
{
    var results = (db.tblOrders.Where(l => l.OrderNo == id).Include(c => c.tblCustomer).Include(p => p.tblProducts)
          .Select(v => new OrderCustomerProductViewModel
              {                        
                  CustomerName = v.tblCustomer.CustomerName,
                  ProductId = v.tblProducts.ProductId,
                  Quantity = v.Quantity,
                  UnitPrice = v.tblProducts.UnitPrice,
                  Discount  = v.Discount,
                  Total = v.Total,
        })).ToList();

    return View(results);
}

! I tried as [Jaggan_j] said but it doesn't work. [Jaggan_j] Please help me 3

user_2093481 · Answer

ProductId and UnitPrice are attributes of a single Product and not of a collection of Products, which is causing the error. So you have to get all Products in an Order by returning a list of Products. You can add a list of Product to the view model:

List tblProductList { get; set; }

and get all the corresponding Products in an order like:

public ActionResult Index(int id)
{
   var results = (db.tblOrders.Where(l => l.OrderNo == id).Include(c => c.tblCustomer)
                  .Include(p => p.tblProducts)
                  .Select(v => new OrderCustomerProductViewModel
                  {                        
                      tblProductList = v.tblProducts.ToList(),
                      --rest of your select--
                  })).ToList();

   return View(results);
}

Then in the View, you just have to iterate through tblProductList to display each product's Id and Unit Price.

How to create viewmodel ,controller & view to join many to many table in asp.net mvc Entity framework

1 Answers