使用LINQ获取连接表中的最大计数
本文关键字:LINQ 获取 连接 使用 | 更新日期: 2023-09-27 18:04:27
我在Stackoverflow上问了很多问题,恐怕我仍然找不到答案。我正在使用实体框架。使用LINQ,我试图找到最近七天最受欢迎的课程。我有四个表涉及到查询,时间表,会话,位置和连接表clientsessions
Schedule的Model是
public class Schedule{
public Guid ScheduleID { get; set; }
public Guid CompanyID { get; set; }
public Guid LocationID { get; set; }
public Guid SessionID { get; set; }
public Guid SessionTypeID { get; set; }
public Guid ParentScheduleID { get; set; }
public int ClassDay { get; set; }
public int ClassMonth { get; set; }
public int ClassYear { get; set; }
public string Day { get; set; }
public DateTime StartTime { get; set; }
public DateTime EndTime { get; set; }
public DateTime ClassDate { get; set; }
public string Title { get; set; }
public string Description { get; set; }
public bool Deleted { get; set; }
public int SessionSize { get; set; }
public virtual Session Session { get; set; }
public virtual Company Company { get; set; }
public virtual Location Location { get; set; }
public virtual SessionType SessionType { get; set; }
}
会话模型
public class Session {
public System.Guid SessionID { get; set; }
public System.Guid ProgramID { get; set; }
public System.Guid CompanyID { get; set; }
public Nullable<Guid> SessionTypeID { get; set; }
public Int32 SessionSize { get; set; }
public bool Display { get; set; }
public virtual ClientSession ClientSession { get; set; }
}
The Junction ClientSession Table
public class ClientSession
{
public Guid ClientID { get; set; }
public Guid CompanyID { get; set; }
public Guid SessionID { get; set; }
public Nullable<System.Guid> TransactionID { get; set; }
// Navigation Properties
public virtual Client Client { get; set; }
public virtual Session Session { get; set; }
}
我已经写了下面的SQL代码,按预期工作,但我担心我只是没有足够的知识或经验,将其转换为Linq。
SELECT TOP 1 s.ClassDate, l.LocationName, COUNT(c.SessionID) as num_att
FROM Schedules s
JOIN Sessions ss ON s.SessionID = ss.SessionID
JOIN Sessions ss ON s.SessionID = ss.SessionID
JOIN ClientSessions c ON ss.SessionID = c.SessionID
JOIN Locations l ON s.LocationID = l.LocationID
WHERE s.CompanyID = '109'
AND s.LocationID = '4'
AND (s.ClassDate >= DATEADD(DAY, -7, GETDATE()))
GROUP BY s.ClassDate, l.LocationName
ORDER BY num_att DESC, s.ClassDate DESC
这是我所能得到的
var details = _repositoryBase.AllIncluding<Schedule>(x => x.Session, x => x.Location)
.Where(x => x.CompanyID == mostPopularSessionRequest.CompanyID
&& x.LocationID == mostPopularSessionRequest.LocationID
.GroupBy(x => x.ClassDate)
在告诉我如何得到这个完成其余部分的任何帮助将是非常感激的。
提前感谢你的帮助。
var query = from s in Schedules
join ss in Schedules on s.SessionID equals ss.SessionID
join c in ClientSessions on ss.SessionID equals c.SessionID
join l in Locations on s.LocationID equals l.LocationID
where s.CompanyID = 109 && s.LocationID = 4 && (s.ClassDate >= DateTime.Now.AddDays(-7))
group c by new
{
c.ClassDate,
l.LocationName
} into gcs
select new
{
ClassDate = gcs.Key.ClassDate,
LocationName = gcs.Key.LocationName,
SessionIDCount = gcs.c.Count()
};