如何在 LINQ 实体框架 4 中编写此 SQL

发布于 2024-10-13 00:10:01 字数 453 浏览 4 评论 0原文

select C.CenterID
from   dbo.Center C 
 inner join (select PersonID, max(EffectiveDate) as EffectiveDate
         from   Center
         where  EffectiveDate <= getdate()
         group by PersonID) as C2 
 on C.PersonID= C2.PersonID
  and C.EffectiveDate = C2.EffectiveDate

中心表有一个PersonID和EffectiveDate，多个记录有相同的PersonID，但不同的EffectiveDates，理想情况下我试图为每个PersonID返回1条最新记录

，我想在linq中将其表达为IQueryable，以便我可以使用它构建更大的查询。

原文

select C.CenterID
from   dbo.Center C 
 inner join (select PersonID, max(EffectiveDate) as EffectiveDate
         from   Center
         where  EffectiveDate <= getdate()
         group by PersonID) as C2 
 on C.PersonID= C2.PersonID
  and C.EffectiveDate = C2.EffectiveDate

Center table has an PersonID and EffectiveDate, multiple records have the same PersonID, but different EffectiveDates, I'm trying to return the 1 most current record for each PersonID

ideally, I want to express this in linq as IQueryable so that I can use it to build larger queries.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

苦妄 2024-10-20 00:10:01

var q = from c in oc.Center
join c2 in (
  from ci in oc.Center
  where ci.EffectiveDate <= DateTime.Now
  group ci by ci.PersonID into cig
  select new { PersonID = cig.Key, EffectiveDate = cig.Max(ed => ed.EffectiveDate) }
 ) on new { c.PersonID, c.EffectiveDate } equals { c2.PersonID, c2.EffectiveDate }
select c.CenterID

var q = from c in oc.Center
join c2 in (
  from ci in oc.Center
  where ci.EffectiveDate <= DateTime.Now
  group ci by ci.PersonID into cig
  select new { PersonID = cig.Key, EffectiveDate = cig.Max(ed => ed.EffectiveDate) }
 ) on new { c.PersonID, c.EffectiveDate } equals { c2.PersonID, c2.EffectiveDate }
select c.CenterID

回复收藏 0 原文

~没有更多了~