问使用最大和最小的Entityframe Linq
EN

Stack Overflow用户

提问于 2020-10-22 15:40:05

回答 1查看 37关注 0票数 0

我正尝试在Entityframe中使用最小和最大值作为日期时间字段的布尔值。我可以在SQL中做得很好，但是在Entityframe中，我不确定我错过了什么。该错误与bool之间的日期时间有关。

在SQL中

MeetingDate=case when Convert(varchar(10),min(md.StartDate),101)=Convert(varchar(10),max(md.StartDate),101) then Convert(varchar(10),min(md.StartDate),101) else Convert(varchar(10),min(md.StartDate),101)+' - '+Convert(varchar(10),max(md.StartDate),101) end

   var listofMeeting = (from m in db.Meetings
                            join md in db.MeetingDates on m.MeetingId equals md.MeetingId
                            join mf in db.MeetingFiles on md.MeetingId equals mf.MeetingId
                            join mv in db.MeetingVersions on mf.MeetingFileId equals mv.MeetingFileId
                            join fm in db.FileManagers on mf.FileManagerId equals fm.FileManagerId
                            join g in db.vwGuidelinePanels on m.GroupId equals g.GroupId
                            where g.GroupId == groupID && mf.FileCategoryItemDictionaryId == 755
                            select new PresentationLayer.Models.GuidelineVersion
                            {
                                GroupId = g.GroupId,
                                MeetingId = md.MeetingId,
                                MeetingDate = (md.StartDate). == max(md.StartDate) ? md.StartDate : min(md.StartDate) == max(md.StartDate),
                            }).GroupBy(g => new
                            {
                                g.MeetingId,
                                g.GroupId
                            }).ToList();

linq

entity-framework

回答 1

Stack Overflow用户

回答已采纳

发布于 2020-10-22 15:50:02

在SQL中，min和max之所以有效，是因为您要么在稍后执行group by，要么在查询中没有选择任何非聚合值。

在LINQ中，你需要先做Group By。

如果你没有分组依据(你想把所有的东西都放到一个大的组中)，只需要使用一个常量，比如group by中的1。

group md by 1 into g
let minDate = g.Min(md => md.StartDate)
let maxDate = g.Max(md => md.StartDate)
select new Meeting
{
    MeetingDate = minDate == maxDate ? minDate.ToString() : minDate + " - " + maxDate
}

更新

根据更新后的问题：

from m in db.Meetings
join md in db.MeetingDates on m.MeetingId equals md.MeetingId
join mf in db.MeetingFiles on md.MeetingId equals mf.MeetingId
join mv in db.MeetingVersions on mf.MeetingFileId equals mv.MeetingFileId
join fm in db.FileManagers on mf.FileManagerId equals fm.FileManagerId
join g in db.vwGuidelinePanels on m.GroupId equals g.GroupId
where g.GroupId == groupID && mf.FileCategoryItemDictionaryId == 755
group new { md.StartDate /* add other data you're need here as necessary */}
    by new
    {
        g.MeetingId,
        g.GroupId
    } into grp
let minDate = grp.Min(md => md.StartDate)
let maxDate = grp.Max(md => md.StartDate)
select new PresentationLayer.Models.GuidelineVersion
{
    GroupId = grp.Key.GroupId,
    MeetingId = grp.Key.MeetingId,
    MeetingDate = minDate == maxDate ? minDate.ToString() : minDate + " - " + maxDate
}

我还建议您考虑使用导航属性，以避免所有这些显式连接。一旦您开始从LINQ-first的角度考虑问题，而不是尝试直接从SQL转换，您可能会发现它简化了查询。这可能会变得更像这样：

from g in db.vwGuidelinePanels
from m in g.Meetings
where m.MeetingFiles.Any(mf => mf.FileCategoryItemDictionaryId == 755)
let minDate = m.MeetingDates.Min(md => md.StartDate)
let maxDate = m.MeetingDates.Max(md => md.StartDate)
select new PresentationLayer.Models.GuidelineVersion
{
    GroupId = g.GroupId,
    MeetingId = m.MeetingId,
    MeetingDate = minDate == maxDate ? minDate.ToString() : minDate + " - " + maxDate
}