LINQ子表中选中复合键 [英] LINQ subselect in table with composite key

问题描述

我在数据库中有一个通过实体框架查询访问的设置表。
设置有一个复合键：类别，组，名称，目标，修改。
设置显然还包含非关键字段，如值。

I have a settings table in a database that is accessed through Entity Framework queries. The settings have a composite key: Category, Group, Name, Target, Modified. The settings obviously also contain non-key fields like value.

如何编写一个查询，让我在同一个（类别，组，名称，目标）？

How would I write a query that gives me the latest setting within the same (Category, Group, Name, Target)?

更具体地说，我想要所有最新的设置实体，包括非关键字段，与特定类别，组，目标组合匹配。

More specific, I want all the latest setting entities, including the non-key fields, that matches a specific category, group, target combination.

例如：给我所有目录的所有最新设置，类别是数据库，组是超时。

For example: Give me all the latest settings for all targets where Category is "Database" and Group is "Timeout".

这可以两步完成，初始查询返回与条件匹配的所有[类别，组，名称，目标，修改]密钥，然后循环查询设置每个键，但会产生大量数据库查询，从而产生开销。

This could be done in two-steps with an initial query returning all [Category, Group, Name, Target, Modified] keys matching the criteria and then a loop querying a setting for each key, but that would produce a lot of database queries, creating overhead.

如果我想最小化SQL数据库的数量，我该如何使用LINQ由框架执行的查询？

How would I do this with LINQ if I want to minimize the number of SQL database queries executed by the framework?

推荐答案

var query = from s in db.Settings
            group s by new
            {
              s.Category,
              s.Group,
              s.Name,
              s.Target,
            } into sg
            select new
            {
              Setting = sg.OrderByDescending(r => r.Modified).FirstOrDefault()
            };

编辑 - 如果要返回设置列表

Edit - If you want to return a List of Settings

 List<Setting> list = (from s in db.Settings
                       group s by new
                       {
                         s.Category,
                         s.Group,
                         s.Name,
                         s.Target,
                       } into sg
                       select sg.OrderByDescending(r => r.Modified).FirstOrDefault()
                      ).ToList();

这篇关于LINQ子表中选中复合键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！