how to define SQL view in viewmodel class MVC and controller
my sql query i need to use
Select dbo.let.Name0,
sum (dbo.VCategory.Food) AS FoodSales,
sum (dbo.VCategory.Drink) AS DrinkSales,
sum (dbo.VCategory.Other) AS OtherSales,
COUNT(DISTINCT dbo. VCategory.CheckID) AS Checks
from dbo. let, dbo.Check INNER JOIN
dbo. VCategory ON dbo. dbo.Check.CheckID = dbo. VCategory.CheckID WHERE (dbo. Check.Status = 3) AND (dbo.Check.OrderType <> 4) AND CAST(dbo. Check.SysDate AS DATE) = '2016-11-18' AND dbo. Check. let = dbo.let.let GROUP BY dbo. let.Name0, dbo.Check.let
my view model
public class resturantClass
{
public int CheckID { get; set; }
public string let { get; set; }
public Nullable<byte> Status { get; set; }
public Nullable<byte> OrderType { get; set; }
public Nullable<System.DateTime> SysDate { get; set; }
public string Name0 { get; set; }
public Nullable<decimal> Food { get; set; }
public Nullable<decimal> Drink{ get; set; }
public Nullable<decimal> Other { get; set; }
}
What I have tried:
in controller
resturantClass res = new resturantClass();
var detailslist = (
from outlet in db.FposOutlets
from checks in db.FposChecks join
vcic in db.VposCheckItemCategories on checks.CheckID equals vcic.CheckID
where checks.Status == 3 && checks.OrderType != 4 && outlet.Outlet == checks.Outlet
select new
{
outlet.Outlet, outlet.Name0, checks.SysDate, checks.OrderType, checks.Status, vcic.CheckID, vcic.FoodSales, vcic.BeverageSales, vcic.OtherSales
}).ToList();
foreach (var item in detailslist)
{
res.Outlet = item.Outlet;
res.Name0 = item.Name0;
res.SysDate = item.SysDate;
res.OrderType = item.OrderType;
res.Status = item.Status;
res.CheckID = item.CheckID;
res.Food = item.Food ;
res.Drink= item.Drink;
res.Other = item.Other;
}
return View(res);