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();
精彩评论