I want to use group by in C#, and my result must be like this code in SQL:

SELECT MAX(EffectiveDate) AS EffectiveDate,details.PersonnelBaseID
FROM dbo.tkp_PersonnelDetails AS details 
where details.EffectiveDate <= @Time
GROUP BY PersonnelBaseID

@Time is an input parameter; assume tkp_PersonnelDetails is a list of objects in C#.

  • Slightly off topic, but dont you want to know which Group (PersonnelBaseID) each MAX(EffectiveDate) belongs to? The query in your example will return a set of dates, but there is no way to tell what PersonnelBaseID each date is related to. – Lennart Jul 12 at 3:28
  • i want to know Maximum Effectivedate Per Personnel . Where EffectiveDate Time,i want to know Maximum Effectivedate Per Personnel . Where EffectiveDate les than Time , Time Is a DateTime Parameter that i Get From Client. – mhd.cs Jul 12 at 3:31

I have a suggestion for you that will be a bit more efficient than the answer you provided.

var filterMaxEffectivedate = personnelDetail
    .Where(c=> c.EffectiveDate<= timeRecord.Time.Date)
    .GroupBy(c => c.PersonnelBaseID)
    .Select(de => new {
        PersonnelBaseID = de.Key,
        EffectiveDate = de.Max(x => x.EffectiveDate)

What I try to think about when designing Linq queries is trying to only use aggregation functions that translate into SQL. For example, SUM, MAX, MIN, COUNT, AVERAGE. In this case, Linq can't easily translate a OrderByDescending().FirstOrDefault() into a simple SQL aggregation, but it can translate a Max() into the MAX keyword!

Here's what the difference would look like for the generated SQL (I got this output using LinqPad and a Linq to SQL context, but I would assume that the same would apply in your case)

Old solution's SQL (notice that a separate subquery is necessary to retrieve the Date data):

SELECT [t1].[PersonnelBaseID], (
        SELECT [t3].[EffectiveDate]
        FROM (
            SELECT TOP (1) [t2].[EffectiveDate]
            FROM [tkp_PersonnelDetails] AS [t2]
            WHERE ([t1].[PersonnelBaseID] = [t2].[PersonnelBaseID]) AND ([t2].[EffectiveDate] < @p0)
            ORDER BY [t2].[EffectiveDate] DESC
            ) AS [t3]
        ) AS [value]
    FROM (
        SELECT [t0].[PersonnelBaseID]
        FROM [tkp_PersonnelDetails] AS [t0]
        WHERE [t0].[EffectiveDate] < @p0
        GROUP BY [t0].[PersonnelBaseID]
        ) AS [t1]

New solution's SQL:

SELECT MAX([t0].[EffectiveDate]) AS [value], [t0].[PersonnelBaseID]
    FROM [tkp_PersonnelDetails] AS [t0]
    WHERE [t0].[EffectiveDate] < @p0
    GROUP BY [t0].[PersonnelBaseID]

Not the answer you're looking for? Browse other questions tagged or ask your own question.