Joins with multiple fields on GroupBy table data in LINQ query/method
I have to work out how to write the following SQL query usingLINQ query or method syntax. (Edit: This is to return a list of latest AgentActivities for all Agents).
SELECT
a.[AgentActivityId],
a.[AgentId],
a.[ActivityId],
a.[StartedAt],
a.[EndedAt],
a.[Version]
FROM
[dbo].[AgentActivity] a
INNER JOIN
(
SELECT
[AgentId],
MAX([StartedAt])[StartedAt]
FROM
[dbo].[AgentActivity]
WHERE
([StartedAt] > '2010/01/24 23:59:59')
AND ([StartedAt] < '2010/10/25')
GROUP BY
AgentId
)grouped
ON (a.[AgentId] = grouped.[AgentId]
AND a.[StartedAt] = grouped.[Start开发者_Python百科edAt])
Just to recap, here's how I interpret the question:
What you want is a list with the most recently started activity for an agent, with the added requirement that the activity must be started within a given date interval.
This is one way to do it:
// the given date interval
DateTime startDate = new DateTime(2010, 1, 24);
DateTime endDate = new DateTime(2010, 10, 25);
IEnumerable<AgentActivity> agentActivities =
... original list of AgentActivities ...
IEnumerable<AgentActivity> latestAgentActivitiesByAgent = agentActivities
.Where(a => a.StartedAt >= startDate && a.StartedAt < endDate)
.GroupBy(a => a.AgentId)
.Select(g => g
.OrderByDescending(a => a.StartedAt)
.First());
(If the question involves LINQ to SQL, there may be some gotchas. I haven't tried that.)
精彩评论