复杂的SQL到LINQ以填充网格(每月计数项目)(MVC，EF代码首先)

Question

我有一个我想填充以下内容的网格：

它正在跟踪去年每个月开放的门票。网格通过优先级过滤，所以我有这样的东西：

| Mon | Critical | High | Tot |
| Jan |    5     |  2   |  7  |
| Feb |    2     |  3   |  5  |

这是我的SQL语句：

SELECT
    MONTH(CreateDate),
    DATENAME(MONTH,m.CreateDate) AS 'Month',
    SUM(CASE WHEN (m.PriorityId = 1) THEN 1 ELSE 0 END) AS 'Critical',
    SUM(CASE WHEN (m.PriorityId = 2) THEN 1 ELSE 0 END) AS 'High',
    SUM(CASE WHEN (m.PriorityId = 3) THEN 1 ELSE 0 END) AS 'Normal',
    SUM(CASE WHEN (m.PriorityId = 4) THEN 1 ELSE 0 END) AS 'Low',
    SUM(CASE WHEN (m.PriorityId = 5) THEN 1 ELSE 0 END) AS 'Not Ready',
    Count(m.Id) AS Total
FROM
    [projects].[dbo].[tblMaintenanceTicket] m
INNER JOIN
    [projects].[dbo].[tblPriority] p
ON
    p.Id = m.PriorityId
WHERE
    StatusId = 1
    AND YEAR(CreateDate) = year(getdate())
GROUP BY 
    MONTH(CreateDate),
    DATENAME(MONTH,m.CreateDate)
ORDER BY
    MONTH(CreateDate)

我希望在vb linq中转换它，但我不确定这是最好的方法。我还在想打电话给存储的程序，但我想真正远离数据库。我正在使用MVC 3和存储库模式，因此调用我的代码是JSON的此类输出：

Dim tickets = ticketRepo.GetAll().Include(Function(p) p.Priority).ToArray()

有帮助吗？

Answer 1

这是您可以做的，当前假设状态化在tblmaintenanceticket表中。

Visual Basic：

Module Module1
    Sub Main()
    Dim rand As New Random
    Dim list As New List(Of tblMaintenanceTicket)
    For count = 1 To 100
      Dim newTicket As New tblMaintenanceTicket
      newTicket.CreateDate = Now.AddDays(rand.Next(0, 365) * -1)
      newTicket.PriorityId = rand.Next(1, 6)
      newTicket.StatusId = 1
      list.Add(newTicket)
    Next
    Dim results = From ticket In list _
                  Where ticket.StatusId = 1 And ticket.CreateDate.Year = Now.Year _
                  Group ticket By ticket.CreateDate.Month Into Group _
                  Select New TicketResults With { _
                    .Month = Month,
                    .Critical = Group.Count(Function(t) t.PriorityId = 1),
                    .High = Group.Count(Function(t) t.PriorityId = 2),
                    .Normal = Group.Count(Function(t) t.PriorityId = 3),
                    .Low = Group.Count(Function(t) t.PriorityId = 4),
                    .NotReady = Group.Count(Function(t) t.PriorityId = 5)
                  }

    End Sub
  Public Class tblMaintenanceTicket
    Public CreateDate As Date
    Public PriorityId As Integer
    Public StatusId As Integer
  End Class
  Public Class TicketResults
    Public Month As Integer
    Public Critical As Integer
    Public High As Integer
    Public Normal As Integer
    Public Low As Integer
    Public NotReady As Integer
  End Class
End Module

c＃：

  class Program
  {
    static void Main(string[] args)
    {
      //Generate some random data
      var list = new List<tblMaintenanceTicket>();    
      var rand = new Random();    
      for (var count = 0; count < 100; ++count)
      {
        var newTicket = new tblMaintenanceTicket();
        newTicket.CreateDate = DateTime.Now.AddDays(rand.Next(0, 365)* -1);
        newTicket.PriorityId = rand.Next(1, 6);
        newTicket.StatusId = 1;
        list.Add(newTicket);
      }

      var results = from ticket in list
                    where ticket.StatusId == 1 && ticket.CreateDate.Year == DateTime.UtcNow.Year
                    group ticket by ticket.CreateDate.Month into months
                    select new TicketResults
                    {
                      Month = months.Key,
                      Critical = months.Count(m => m.PriorityId == 1),
                      High = months.Count(m => m.PriorityId == 2),
                      Normal = months.Count(m => m.PriorityId == 3),
                      Low = months.Count(m => m.PriorityId == 4),
                      NotReady = months.Count(m => m.PriorityId == 5)
                    };
      System.Diagnostics.Debugger.Break();
    }
  }
  public class TicketResults
  {
    public int Month { get; set; }
    public int Critical { get; set; }
    public int High { get; set; }
    public int Normal { get; set; }
    public int Low { get; set; }
    public int NotReady { get; set; }
  }
  public class tblMaintenanceTicket
  {
    public DateTime CreateDate { get; set; }
    public int PriorityId { get; set; }
    public int StatusId { get; set; }
  }