开发者

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.

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();


Try to use the following LINQ Statement.

        List<Settings> latestSettings = 
            db.Settings.OrderByDescending(x => x.Modified).GroupBy(x => new {x.Category, x.Group, x.Name, x.Target}, x => x)
            .Select(result => result.FirstOrDefault())
            .ToList();
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜