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]