相当复杂的 LINQ to Entities 查询

发布于 2024-11-09 12:34:43 字数 977 浏览 0 评论 0原文

我有两个实体,假设它们称为容器和记录。他们之间有一种主子关系:一个“容器”可以容纳很多记录。

数据库中的 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 技术交流群。

扫码二维码加入Web技术交流群

发布评论

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

评论(2

萝莉病 2024-11-16 12:34:43

从我的头顶上掉下来:

var q = from c in Container
        from r in c.Records
        group r by r.RecordType.RecordType_Id into g
        select new
        {
            Container = c,
            RecordType_Id = g.Key,
            Records = from gr in g
                      let maxDate = g.Max(d => d.Date)
                      where gr.Date == maxDate
                      select gr
        };

Off the top of my head:

var q = from c in Container
        from r in c.Records
        group r by r.RecordType.RecordType_Id into g
        select new
        {
            Container = c,
            RecordType_Id = g.Key,
            Records = from gr in g
                      let maxDate = g.Max(d => d.Date)
                      where gr.Date == maxDate
                      select gr
        };
挽袖吟 2024-11-16 12:34:43

尝试使用 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/

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文