相当复杂的 LINQ to Entities 查询
我有两个实体,假设它们称为容器和记录。他们之间有一种主子关系:一个“容器”可以容纳很多记录。
数据库中的 Records 表具有以下列:
- Id
- Date
- Container_Id
- RecordType_Id
Record 实体没有任何反向引用 Container 的导航属性。
我正在为我的存储库编写一个 LINQ 查询,该查询将仅检索每个 RecordType_Id 具有最新日期的容器的记录。所有较旧的记录都应被忽略。
因此,如果一个容器有 5 条记录,每个 RecordType_Id 一条,日期为 2011 年 5 月 24 日。但每个 RecordType_Id 还有另外 5 条记录,但日期为 20/May/2011。然后,仅检索日期为 5 月 24 日的前 5 个并将其添加到容器中的集合中。
我想出了一个 SQL 查询来满足我的需要(但也许有一些更有效的方法?):
select t.*
from Records t
inner join (
select Container_Id, RecordType_Id, max(Date) AS MaxDate
from Records
group by Container_Id, RecordType_Id ) g
on t.Date = g.MaxDate
and t.Container_Id = g.Container_Id
and t.RecordType_Id = g.RecordType_Id
order by t.Container_Id
, t.RecordType_Id
, t.Date
但是我正在努力将其转换为正确的 LINQ 查询。 EF 本身已经生成了一个相当大的查询,只是为了加载实体,这让我不确定这个 SQL 查询中有多少实际上与 LINQ 查询相关。
I have two entities, assume they are called Container and Record. They have a master-child relationship: a 'container' can hold many records.
The Records table in the database has the following columns:
- Id
- Date
- Container_Id
- RecordType_Id
The Record entity does not have any navigation properties that back reference the Container.
I am writing a LINQ query for my repository that will retrieve ONLY the records for a container that have the most recent date for each RecordType_Id. All older records should be ignored.
So if a container has say 5 records, one for each RecordType_Id, with the date 24/May/2011. But also has another 5 records for each RecordType_Id but with the date 20/May/2011. Then only the first 5 with the 24/May date will be retrieved and added to the collection in the container.
I came up with an SQL query that does what I need (but maybe there is some more efficient way?):
select t.*
from Records t
inner join (
select Container_Id, RecordType_Id, max(Date) AS MaxDate
from Records
group by Container_Id, RecordType_Id ) g
on t.Date = g.MaxDate
and t.Container_Id = g.Container_Id
and t.RecordType_Id = g.RecordType_Id
order by t.Container_Id
, t.RecordType_Id
, t.Date
However I am struggling to translate this into a proper LINQ query. EF is already generating a fairly large query all by itself just to load the entities, which makes me unsure of how much of this SQL query is actually relevant to the LINQ query.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
从我的头顶上掉下来:
Off the top of my head:
尝试使用 LinqPad,它可以帮助您轻松测试 linq 查询。即使针对现有的 EF 模型(位于您的项目中)也是如此。访问http://www.linqpad.net/
Try using LinqPad, it helps you test linq queries easily. Even against an existing EF model (which is in your project). Visit http://www.linqpad.net/