c# - 在 sql asp.net mvc 5 中使用 linq 时如何正确提取数据？

Question

我有一个 Excel 数据，其中包含来自不同表的记录，问题是如果任何一年都没有记录，我的 Excel 必须这样做。同样，如果有数据，但目前我的 Excel 上的数据不这样做，需要一些关于我下面的逻辑的帮助。Excel 将数据复制到任何一年和一周，但在前端我检查它根本没有这样做，并且在我的后端需要一些帮助。

public IList<ExtractionViewModel> GetExtractionViewModels()
{
    var db = new ProductionManagementEntities();

    var scheduleList = (from p in db.ProductionDays
                    
                        from m in db.Models
                        from mx in db.Models
                        from mt in db.Models
                        from mv in db.Models
                        from wk in db.Models

                        join w in db.Weeks on p.WeekId equals w.WeekId
                        orderby w.Year descending , m.Name descending, p.ProductionDate descending, w.WeekNum descending, mt.Name descending, mx.Name descending, mv.Name descending
               
                        where (mx.InActive == true)
                        where (mt.InActive == false)
                        where(m.InActive == false)
                        where(mv.InActive == false)
                        
                        where (mt.Name == "VW270")
                        where(mx.Name == "VW250")
                        where(m.Name == "VW270PA")
                        where(mv.Name == "VW250/2PA")

                        select new ExtractionViewModel
                        {
                            Year = w.Year,
                            Day = p.ProductionDate,
                            Week = w.WeekNum,
                            VW270 = mt.Name,
                            VW270PA = m.Name,
                            VW250 = mx.Name,
                            VW2502PA = mv.Name
                        }).ToList();

    return scheduleList;
}
    
// Model
public class ExtractionViewModel
{
    public string Year { get; set; }
    public int Week { get; set; }

    [DataType(DataType.Date)]
    public DateTime Day { get; set; }

    public string VW250 { get; set; }
    public string VW270 { get; set; }
    public string VW2502PA { get; set; }
    public string VW270PA { get; set; }
}

public ActionResult DataResult()
{
    return View(this.GetExtractionViewModels());
}

public void ExportToExcel()
{
    var v = new GridView();
    v.DataSource = this.GetExtractionViewModels();
    v.DataBind();
    Response.ClearContent();
    Response.Buffer = true;
    Response.AddHeader("content-disposition", "attachment; filename=ExtractionRecords.xls");
    Response.ContentType = "application/ms-excel";
    Response.Charset = "";
    StringWriter objStringWriter = new StringWriter();
    HtmlTextWriter htmlTextWriter = new HtmlTextWriter(objStringWriter);
    v.RenderControl(htmlTextWriter);
    Response.Output.Write(objStringWriter.ToString());
    Response.Flush();
    Response.End();
    //return View("DataResult");
}

Excel输出：

[![在此处输入图片描述][1]][1]

对 Week 表的 SQL 查询：

SELECT TOP (1000) [WeekId]
      ,[WeekNum]
      ,[Year]
      ,[CreatedDate]
      ,[CreatedBy]
      ,[ModifiedDate]
      ,[ModifiedBy]
      ,[InActive]
FROM [ProductionManagement].[Schedule].[Week]
where WeekNum = 53
order by Year
```[![enter image description here][2]][2]


  [1]: https://i.stack.imgur.com/YsbRq.png
  [2]: https://i.stack.imgur.com/lMVLH.png

标签： c#sql-serverasp.net-mvclinq