CODEWITHSUNDEEP
asp.net-mvcentity-framework
Kuldeep Saini
Kuldeep Saini

Reputation: 39

returning sql query data from Model to controller

I am trying to design an sql data board/dashboard. I have connected to SQL database using Entity framework 6.

I trying to pull this off by doing a query in Model and then passing the value to Homecontroller

Model

 public class LaserRepo
{
    public string CmRlsd3D { get; set; }

    public void CMRlsd3Days()
    {
        WorkflowEntities db = new WorkflowEntities();
        int CurrentYear = DateTime.Now.Year;
        DateTime firstday = new DateTime(CurrentYear, 1, 25);

        DateTime prevyear = new DateTime(2017, 10, 1);

        DateTime Yday = DateTime.Today.AddDays(-2);
        string Dayofweek = Yday.DayOfWeek.ToString();
        int day1 = (int)(DateTime.Now.DayOfWeek);
        if (day1 == 1)
        {
            Yday = DateTime.Today.AddDays(-4);
        }

        CmRlsd3D   = (from c in db.OrderDetailCheckHistories
                        join d in db.OrderDetails on c.JobNumber equals d.JobNumber
                        where (c.DateTime > firstday && c.DateTime < Yday)
                        where c.FieldName == "Released" || c.FieldName == "Pre Release"
                        where (c.StockCode.Contains("-CA") && !(c.StockCode.Contains("-CAB"))) ||
                        c.StockCode.Contains("-CM") || c.StockCode.Contains("-LP")
                        select new { c.JobNumber }).ToString();                  


    }

I would like to pass the total count to my controller/view

CmRlsd3D

Home Controller

public class HomeController : Controller
{    
    public ActionResult Laser()
    {
        LaserRepo Lr = new LaserRepo();
        var Cm3D = Lr.CmRlsd3D.Count();
        return View(Lr);
    }

Upvotes: 3

Views: 420

Answers (1)

Tetsuya Yamamoto
Tetsuya Yamamoto

Reputation: 24957

First thing you should know that Lr.CmRlsd3D.Count(); counts number of certain elements inside the string property, not the number of result sets as intended. Also you have numerous issues there:

a) Multiple where clauses is redundant and may not returning desired result. Use && operator for multiple where conditions which uses logical AND.

b) select new { c.JobNumber } uses anonymous type, which I think is unnecessary. select c.JobNumber just enough.

c) ToString() against LINQ query may returns fully-qualified name of result set type instead of the value. You need to use FirstOrDefault(), SingleOrDefault() or foreach loop iteration to return string value from it.

Therefore, you should use this setup:

1) Use an integer property inside viewmodel class to hold the count.

public int CmRlsd3D { get; set; }

2) Change method return type to int and modify the query using Count() to return number of records as provided in example below:

public int CMRlsd3Days()
{
    WorkflowEntities db = new WorkflowEntities();
    int CurrentYear = DateTime.Now.Year;
    DateTime firstday = new DateTime(CurrentYear, 1, 25);

    DateTime prevyear = new DateTime(2017, 10, 1);

    DateTime Yday = DateTime.Today.AddDays(-2);
    string Dayofweek = Yday.DayOfWeek.ToString();
    int day1 = (int)(DateTime.Now.DayOfWeek);
    if (day1 == 1)
    {
        Yday = DateTime.Today.AddDays(-4);
    }

    var count = (from c in db.OrderDetailCheckHistories
                join d in db.OrderDetails on c.JobNumber equals d.JobNumber
                where (c.DateTime > firstday && c.DateTime < Yday)
                && (c.FieldName == "Released" || c.FieldName == "Pre Release")
                && ((c.StockCode.Contains("-CA") && !(c.StockCode.Contains("-CAB"))) ||
                c.StockCode.Contains("-CM") || c.StockCode.Contains("-LP"))
                select c.JobNumber).Count();               

    return count;
}

3) Assign the property by the method's result.

public ActionResult Laser()
{
    LaserRepo Lr = new LaserRepo();
    Lr.CmRlsd3D = CMRlsd3Days();
    return View(Lr);
}

// alternative using property get definition
public int CmRlsd3D 
{ 
    get
    {
        return CMRlsd3Days();
    }
}

4) Inside view page, use @model LaserRepo and @Model.CmRlsd3D to display it.

Upvotes: 1

Related Questions