How to group by linq based on input parameter?

Question

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

Answer 1

I found Solution :

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

Answer 2

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)
    }).ToList();

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]