将此 SQL 查询转换为 LINQ

Question

我有一个在SQL Server中编写的SQL查询，但我无法弄清楚如何在其中一个电子邮件列上进行GROUP BY。

SELECT tblEmailEvents.dtmEvent,
       tblSentEmails.strEmailSendID,
       tblSentEmails.strSubject,
       COUNT(tblRecipients.lngRecipientNo) AS Recipients, 
       MIN(tblRecipients.strEmailAddress) AS EmailAddress
FROM tblEmailEvents
INNER JOIN tblSentEmails ON tblEmailEvents.strEmailSendID = tblSentEmails.strEmailSendID
INNER JOIN tblRecipients ON tblSentEmails.strEmailSendID = tblRecipients.strEmailSendID
GROUP BY tblSentEmails.strEmailSendID, tblSentEmails.strSubject, tblEmailEvents.dtmEvent
ORDER BY tblEmailEvents.dtmEvent DESC

这就是我到目前为止想出的：

var query = from mails in this._session.Query<tblSentEmails>()
            join events in this._session.Query<tblEmailEvents>() on mails.strEmailSendID equals events.strEmailSendID
            join recipients in this._session.Query<tblRecipients>() on mails.strEmailSendID equals recipients.strEmailSendID
            where mails.UserId == currentUser.Id
            where (events.dtmEvent >= dtmTimeFrame.AddSeconds(1))
            where events.strType != "Sent"
            orderby events.dtmEvent descending
            //select new
            //       {
            //           Date = events.dtmEvent.ToString("yyyy-MM-ddTHH:mm:ss"),
            //           Type = events.strType,
            //           Subject = mails.strSubject,
            //           Recipients = recipients.strEmailAddress
            //       };
            select new
                   {
                       Date = events.dtmEvent.ToString("s"),
                       Type = events.strType,
                       Subject = mails.strSubject,
                       Recipients = recipients.strEmailAddress
                   };

我需要一些帮助来放置GROUP BY，因为我现在很迷茫（这里不是一个很棒的 SQL 人）。

谢谢。

Answer 1

请看。 这对你会有所帮助。

var V = (from State in ST1.State_Master
                group State by State.CountryID into temp
                select new
                {
                   T1 = temp.Key.Value,
                   T2 = temp
                 }).ToList();

如果您的问题得到解决，请告诉我