asp.net-mvc - 将sql查询数据从Model返回到controller

Question

我正在尝试设计一个 sql 数据板/仪表板。我已使用 Entity Framework 6 连接到 SQL 数据库。

我尝试通过在模型中执行查询然后将值传递给 Homecontroller 来实现此目的

型号

 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();                  


    } 

我想将总计数传递给我的 Controller / View

CmRlsd3D 

家庭 Controller

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

Answer 1

首先您应该知道，Lr.CmRlsd3D.Count(); 计算字符串属性内某些元素的数量，而不是预期的结果集数量。此外，您还有很多问题:

a) 多个 where 子句是多余的，可能不会返回所需的结果。对使用逻辑 AND 的多个 where 条件使用 && 运算符。

b) select new { c.JobNumber } 使用匿名类型，我认为这是不必要的。 选择 c.JobNumber 就足够了。

c) 针对 LINQ 查询的 ToString() 可能会返回结果集类型的完全限定名称而不是值。您需要使用 FirstOrDefault()、SingleOrDefault() 或 foreach 循环迭代来从中返回字符串值。

因此，您应该使用此设置:

1) 在 vi​​ewmodel 类中使用整数属性来保存计数。

public int CmRlsd3D { get; set; }

2) 将方法返回类型更改为 int 并使用 Count() 修改查询以返回记录数，如下例所示:

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) 通过方法的结果分配属性。

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)在 View 页面内，使用@model LaserRepo和@Model.CmRlsd3D来显示它。