sql - asp.net mvc，原始 sql : Display data in Views generated using aggregate function

Question

我正在使用 ASP.NET MVC、EF 6 和 SQL Server 2008。

我想生成一个 View ，显示特定年份特定月份每天的所有销售额总和。

我发现 LINQ 查询在这类工作中非常复杂，所以我使用了原始 SQL 查询。我在 SQL Server 中编写了查询并进行了测试，它运行良好。

select  
    YEAR(Date) as Year, 
    MONTH(Date) as month, 
    DAY(Date) as date,   
    SUM(GrandTotal) as Total  
from 
    Sales 
where 
    Year(Date) = 2014 
    and MONTH(Date) = 12 
group by 
    DAY(Date), YEAR(Date), MONTH(date)

结果

目前我没有太多数据。但看起来我从查询中得到了我想要的东西。

我为此编写了一个 Controller ，现在我不知道如何在 View 中显示这些数据。

public ActionResult MonthlySalesByDate()
{
        DateTime today = DateTime.Now.Date;
        int _year = today.Year;
        int _month = today.Month;

        //raw sql query 
        string query = "select SUM(GrandTotal) as Total, DAY(Date) as date, MONTH(Date) as month, YEAR(Date) as Year from Sales where Year(Date) = " + _year + " and MONTH(Date) =" + _month + "  Group by DAY(Date), YEAR(Date), MONTH(date)";

        //executing raw sql query 
        var _model = db.Stocks.SqlQuery(query).ToList();           

        return View(_model);
}

请帮我解决这个问题。如果有更好的方法或者我犯了错误，请告诉我。

Answer 1

首先创建 View 模型来表示您要在 View 中显示的内容

public class DayTotalVM
{
  public int Day { get; set; }
  [DisplayFormat(DataFormatString = "{0:C}")]
  public decimal Total { get; set; }
}
public class SalesVM
{
  [DisplayFormat(DataFormatString = "{0:MMMM yyyy}")]
  public DateTime Date { get; set; }
  public List<DayTotalVM> Days { get; set; }
}

您拥有的 sql 查询可以在 linq 中生成并使用投影到您的 View 模型中

int year = 2014;
int month = 12;
var query = db.Sales.Where(x => x.Date.Year == year && x.Date.Month == month)
    .GroupBy(x => x.Date).Select(g => new DayTotalVM
    {
        Day = g.Key.Day,
        Total = g.Sum(x => x.Total)
    })

然而，这只会给你上面图片中的 2 个项目，但是从你想在一个月中的所有日子显示的评论中，你可以添加

int daysInMonth = DateTime.DaysInMonth(year, month);
List<DayTotalVM> days = new List<DayTotalVM>();
for(int i = 1; i < daysInMonth + 1; i++)
{
    DayTotalVM item = new DayTotalVM () { Day = i };
    DayTotalVM ex = query.Where(x => x.Day == i).FirstOrDefault();
    if (ex != null)
    {
        item.Total = ex.Total;
    }
    days.Add(item);
}

最后初始化并返回你的 View 模型

SalesVM model = new SalesVM();
{
  Date = new DateTime(year, month, 1),
  Days = days 
}
return View(model);

然后 View 将是

@model SalesVM
@Html.DisplayFor(m => m.Date);
<table>
  @for(int i = 0; i < Model.Days.Count; i++)
  {
    <tr>
      <td>@Html.DisplayFor(m => m.Days[i].Day)</td>
      <td>@Html.DisplayFor(m => m.Days[i].Total)</td>
    </tr>
  }
</table>

编辑

可以使用 GroupJoin() 替换 for 循环

public ActionResult MonthlySalesByDate(int year, int month)
{
  int daysInMonth = DateTime.DaysInMonth(year, month);
  var days = Enumerable.Range(1, daysInMonth);
  var query = db.Sales.Where(x => x.Date.Year == year && x.Date.Month == month).Select(g => new
  {
    Day = g.Date.Day,
    Total = g.Total
  });
  var model = new SalesVM
  {
    Date = new DateTime(year, month, 1),
    Days = days.GroupJoin(query, d => d, q => q.Day, (d, q) => new DayTotalVM
    {
      Day = d,
      Total = q.Sum(x => x.Total)
    }).ToList()
  };
  return View(model);
}