c# - sql to linq and 2 query into 1

Question

再会。我有 2 个请求。

SELECT cf.Conf_StartTime, cf.Conf_DisplayName, eve.Conf_ID, eve.Party_ID, eve.Point_NAME, eve.Ongoing_ID, COUNT(*) AS 'num'
FROM CDR_EVENT as eve INNER JOIN CDR_Conference as cf ON eve.Conf_ID = cf.Conf_ID 
WHERE TIMESTAMP >= '9/10/2020' AND TIMESTAMP <= '9/11/2020' AND Ongoing_ID IS NOT NULL
GROUP BY
cf.Conf_StartTime, cf.Conf_DisplayName, eve.Conf_ID, eve.Party_ID, eve.Point_NAME, eve.Ongoing_ID
HAVING COUNT (*) >= 2
ORDER BY eve.Conf_ID

GO

SELECT cf.Conf_StartTime, cf.Conf_DisplayName, eve.Conf_ID, eve.Party_ID, eve.Point_NAME, eve.Disconnect_Cause_DESCRIPTION, COUNT(*) AS 'num'
FROM CDR_EVENT as eve INNER JOIN CDR_Conference as cf ON eve.Conf_ID = cf.Conf_ID 
WHERE TIMESTAMP >= '9/10/2020' AND TIMESTAMP <= '9/11/2020' AND Disconnect_Cause_DESCRIPTION IS NOT NULL AND Disconnect_Cause_ID != '2'
GROUP BY
cf.Conf_StartTime, cf.Conf_DisplayName, eve.Conf_ID, eve.Party_ID, eve.Point_NAME, Disconnect_Cause_DESCRIPTION
HAVING COUNT (*) >= 1
ORDER BY cf.Conf_DisplayName

问题 1. 如何将它们转换为 LINQ？

问题 2. 是否可以将这两个查询合二为一？

标签： c#sqllinq

Answer 1

我想我能够转换为 LINQ，结果并不难。问题＃2仍然有效:)

var obj = from cdr in dataContext.CDR_EVENT
where (cdr.TIMESTAMP.Date >= dTimeStart.Date && cdr.TIMESTAMP.Date <= dTimeEnd.Date && 
cdr.Ongoing_ID != null)
group (cdr) by new {cdr.Conf_ID, cdr.Party_ID,cdr.Ongoing_ID, cdr.Point_NAME } into g
where g.Count() >=2
orderby g.Key.Party_ID
select new
{
   s1 = g.Key.Conf_ID,
   s2 = g.Key.Party_ID,
   s3 = g.Key.Ongoing_ID,
   s4 = g.Key.Point_NAME,
   s5 = g.Count()
};