在 c# 中将 SQL 转换为 Linq

Question

我正在尝试将一些SQL代码转换为c# Linq：

SELECT Username, Count(Ticket.TicketId) as 'Tickets Completed'
FROM  Ticket
INNER JOIN TicketStatus ON Ticket.TicketStatusID = TicketStatus.TicketStatusID
INNER JOIN Membership ON Ticket.CompletedBy = Membership.UserId
WHERE Ticket.ClosedDate >= @StartDate
      and Ticket.ClosedDate <= @EndDate
GROUP BY Username
ORDER BY 'Tickets Completed' DESC

显示

Paul    6
Mike    4
Donna   3
Elliot  2

我尝试使用 Linqer，这使这变得更加复杂并且没有返回任何结果：

var query = from Ticket in data.Tickets
                join Membership in data.Memberships on new { CompletedBy = Guid.Parse(Ticket.CompletedBy.ToString()) } equals new { CompletedBy = Membership.UserId }
                where
                  Ticket.ClosedDate >= StartDate &&
                  Ticket.ClosedDate <= EndDate
                group new { Membership, Ticket } by new
                {
                    Membership.Username
                } into g
                orderby
                  "Tickets Completed" descending
                select new
                {
                    Username = g.Key.Username,
                    Completed = g.Count(p => p.Ticket.TicketID > 0)
                };

您的帮助将不胜感激。

谢谢

Answer 1

假设数据库中CompletedBy和UserId列都uniqueidentifier，则不需要执行任何类型转换。

var query = from t in db.ticket
        join ts in db.ticketStatus 
        on t.TicketStatus.ID equals ts.TicketStatusID
        join m in db.Membership 
        on t.CompletedBy equals m.UserId
        where t.ClosedDate >= startDate 
             && t.closedDate <= endDate
        group  t by m.UserName into tGroup
        order by tGroup.Count(t=> t.TicketId) decending
        select new {
          UserName = tGroup.Key,
          TicketCount = tGroup.Count()
        };