Joins with multiple fields on GroupBy table data in LINQ query/method

Question

Urgent: 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). Any help will be much appreciated.

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.[StartedAt])

Answer 1

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.)