I have 2 classes which I have created a view model for:
public class Sweet
{
public int SweetID { get; set; }
public int CategoryID { get; set; }
public Category Category { get; set; }
public string SweetName { get; set; }
public bool Singular { get; set; }
public string Description { get; set; }
public decimal Price { get; set; }
public virtual ICollection<Cart> Carts { get; set; }
}
public class Cart
{
[Key]
public int RecordID { get; set; }
public string CartID { get; set; }
public int SweetID { get; set; }
public int PemixID { get; set; }
public int Qty { get; set; }
public System.DateTime DateCreated { get; set; }
public Sweet Sweet { get; set; }
public PreMix PreMix { get; set; }
}
ViewModel:
public class SweetViewModel
{
public IEnumerable<Sweet> Sweet { get; set; }
public IEnumerable<Cart> Cart { get; set; }
}
I am trying to display a list of all the sweets including the Qty of each sweet in current cart (the sweet may not exists in current cart).
I have written a basic query of the data I need but unsure how to do this the MVC way:
select Sweets.*, Qty
from Sweets
left join (select SweetID, Qty from carts where CartID = '7125794e-38f4- 4ec3-b016-cd8393346669' ) t
on Sweets.SweetID = t.SweetID
Controller:
public ActionResult SweetTestArea()
{
var viewModel = new SweetViewModel
{
Sweet = db.Sweets.Include(s => s.Carts)
};
return View(viewModel);
}
View:
@model WebApplication1.ViewModels.SweetViewModel
@foreach (var item in Model.Sweet)
{
@Html.DisplayFor(modelItem => item.SweetName)
//this doesn't work
@Html.DisplayFor(modelItem => item.Cart.Qty)
}
By looking your view you want to display "SweetName" and "Qty".
So for this you have to re structured your "SweetViewModel" like
public class SweetViewModel
{
//From Sweet Model
public int SweetID { get; set; }
public int CategoryID { get; set; }
public Category Category { get; set; }
public string SweetName { get; set; }
public bool Singular { get; set; }
public string Description { get; set; }
public decimal Price { get; set; }
//From Cart Model
public int Qty { get; set; }
}
And depending upon your output you have to design your view like
@model IEnumerable<WebApplication1.ViewModels.SweetViewModel>
@{
Layout = null;
}
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width" />
<title>SweetTestArea</title>
</head>
<body>
<p>
@Html.ActionLink("Create New", "Create")
</p>
<table class="table">
@foreach (var item in Model) {
<tr>
<td>
@Html.DisplayFor(modelItem => item.SweetID)
</td>
<td>
@Html.DisplayFor(modelItem => item.CategoryID)
</td>
<td>
@Html.DisplayFor(modelItem => item.SweetName)
</td>
<td>
@Html.DisplayFor(modelItem => item.Description)
</td>
<td>
@Html.DisplayFor(modelItem => item.Price)
</td>
<td>
@Html.DisplayFor(modelItem => item.Qty)
</td>
</tr>
}
</table>
</body>
</html>
and your controller code will be look like
public ActionResult SweetTestArea()
{
List<SweetViewModel> viewModel = new List<SweetViewModel>();
var q = (from s in db.Sweets
join c in db.Carts on s.SweetID equals c.SweetID
where c.CartID == "7125794e-38f4- 4ec3-b016-cd8393346669"
select new SweetViewModel
{
CategoryID = s.CategoryID,
Description = s.Description,
Price = s.Price,
Qty = c.Qty,
SweetID = s.SweetID,
SweetName = s.SweetName
});
viewModel = q.ToList();
return View(viewModel);
}
IF you want left join then your linq query will be
var q = (from s in db.Sweets
join c in db.Carts on s.SweetID equals c.SweetID into j
from c in j.DefaultIfEmpty()
where c.CartID == "7125794e-38f4- 4ec3-b016-cd8393346669"
select new SweetViewModel
{
CategoryID = s.CategoryID,
Description = s.Description,
Price = s.Price,
Qty = c.Qty,
SweetID = s.SweetID,
SweetName = s.SweetName
});
my suggestion for you that don't use left join because your want to display data in your cart from both of model from Sweet and Cart means there is item in cart with at least 1 qty.
Edit:
As i understood upon your last comment you want every item to be display in your cart either its in your cart model or not
So for that
1) let take your cart items first
List<SweetViewModel> cart = new List<SweetViewModel>();
var q = (from s in db.Sweets
join c in db.Carts on s.SweetID equals c.SweetID into l
from c in l.DefaultIfEmpty()
where c.CartID == "7125794e-38f4- 4ec3-b016-cd8393346669"
select new SweetViewModel
{
CategoryID = s.CategoryID,
Description = s.Description,
Price = s.Price,
Qty = c.Qty,
SweetID = s.SweetID,
SweetName = s.SweetName
});
cart = q.ToList();
2) then get all sweets and project those into SweetViewModel
List<SweetViewModel> allSweets = new List<SweetViewModel>();
var w = (from s in db.Sweets
select new SweetViewModel
{
CategoryID = s.CategoryID,
Description = s.Description,
Price = s.Price,
Qty = 0,
SweetID = s.SweetID,
SweetName = s.SweetName
});
allSweets = w.ToList();
3) Filter out those sweets that are present in cart You'll get sweet id in below filtered list
var filteredSweets = allSweets.Select(x => x.SweetID).Where(x => !cart.Select(y => y.SweetID).Contains(x)).ToList();
4) Add those filtered sweets in to view model
List<SweetViewModel> viewModel = new List<SweetViewModel>();
foreach (var i in filteredSweets)
viewModel.Add(allSweets.Where(x => x.SweetID == i).Take(1).SingleOrDefault());
5) Then add your cart items into view model
viewModel.AddRange(cart);
and return this view model to view.
you can use order by to sort items in cart as your need.