Click here to Skip to main content
15,918,125 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
See more:
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);
Posted
Updated 8-Jul-20 7:55am
Comments
Dave Kreskowiak 8-Jul-20 9:14am    
So did you want to describe the problem or give the error messages, or should we just guess?
Member 13058758 8-Jul-20 9:26am    
model in razor view doesn't recognize to this items

public Nullable<decimal> Food { get; set; }
public Nullable<decimal> Drink{ get; set; }
public Nullable<decimal> Other { get; set; }

that exist in sqlview how to define it in controller?
Dave Kreskowiak 8-Jul-20 10:03am    
OK, "model in razor view doesn't recognize to ..." means nothing. If you're trying to say the razor code isn't using the values that are in the model, we would need to see the razor code to diagnose that.

Also, the controller code you posted doersn't really make sense either. You're iterating over a collection and repeatedly assigning values to a single instance of a "resturantClass", overwriting previous values.

If you're only returning a single object from the database, use .FirstOrDefault() or .SingleOrDefault() instead of .ToList().
Member 13058758 8-Jul-20 13:51pm    
i return pass IEnumerable list in razor view

1 solution

OK, you're not getting anywhere by limiting the amount of typing you have to do in the responses to my questions.

All I can tell you is if those fields in the Razor code are null when you're expecting numeric values, you're going to have to go back to the database code in the controller to figure out why the database isn't returning the data you're expecting.

Other than that, there may be a difference in what the Razor code is expecting for a model and what you are actually passing into the return View(res); line.

If your Razor code is expecting an IEnumerable, that's NOT what you're passing in in the the controller code. You're passing in a single instance of a resturantClass.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900