在c＃中将SQL转换为Linq(Converting SQL to Linq in c#)

在c＃中将SQL转换为Linq(Converting SQL to Linq in c#)

我试图将一些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) };

非常感谢您的帮助。

谢谢

I am trying to convert some SQL Code to 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

which displays

Paul 6 Mike 4 Donna 3 Elliot 2

I tried to use Linqer which made this more complicated and didnt return any results:

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

Your help would be appreciated.

Thanks

最满意答案

假设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() };

Assuming CompletedBy and UserId columns are both uniqueidentifier in the database, you shouldn't need to do any type conversion.

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