From SQL to LINQ

Question

I am trying to figure out how to translate this SQL select to LINQ, but I have not been able to figure it out yet.

I am getting the last test record for each Id(PersonId), for each test, for each month, per year. Basically, getting the last score of the month for each person per year.

CREATE TABLE #MyTable 
(
   Id INT,
   Score INT,
   TestName VARCHAR(50),
   TestDate DATETIME
)

INSERT INTO #MyTable

VALUES

(1, 10, 'Math', '2011-12-16 00:00:00.000')

,(1, 25, 'Math', '2011-12-26 00:00:00.000')

,(1, 100, 'Math', '2011-12-06 00:00:00.000')

,(1, 10, 'Reading', '2011-12-16 00:00:00.000')

,(1, 25, 'Reading', '2011-12-26 00:00:00.000')

,(1, 100, 'Reading', '2011-12-06 00:00:00.000')

,(2, 10, 'Math', '2011-12-16 00:00:00.000')

,(2, 25, 'Math', '2011-12-26 00:00:00.000')

,(2, 100, 'Math', '2011-12-06 00:00:00.000')

,(2, 10, 'Reading', '2011-12-16 00:00:00.000')

,(2, 25, 'Reading', '2011-12-26 00:00:00.000')

,(2, 100, 'Reading', '2011-12-06 00:00:00.000')

,(1, 10, 'Math', '2011-12-16 00:00:00.000')

,(1, 25, 'Math', '2012-12-26 00:00:00.000')

,(1, 100, 'Math', '2012-12-06 00:00:00.000')

,(1, 10, 'Reading', '2012-12-16 00:00:00.000')

,(1, 25, 'Reading', '2012-12-26 00:00:00.000')

,(1, 100, 'Reading', '2012-12-06 00:00:00.000')

,(2, 10, 'Math', '2012-12-16 00:00:00.000')

,(2, 25, 'Math', '2012-12-26 00:00:00.000')

,(2, 100, 'Math', '2012-12-06 00:00:00.000')

,(2, 10, 'Reading', '2012-12-16 00:00:00.000')

,(2, 25, 'Reading', '2012-12-26 00:00:00.000')

,(2, 100, 'Reading', '2012-12-06 00:00:00.000')



SELECT DISTINCT
M.Id,M.Score,M.TestName, M.TestDate

FROM 
#MyTable M

WHERE
M.TestDate IN (
SELECT MAX(m.TestDate)
FROM #MyTable m
GROUP BY MONTH(TestDate), YEAR(TestDate)
)

DROP TABLE 
#MyTable

BEFORE SubQuery:

FINAL RESULT after SubQuery used, Results 8 records returned:

What I have:

  from a in MyTable
  where   a.TestDate == from b in MyTable
   group b.TestDate.Value.Month,
        a.TestDate.Value.Year 

Answer 1

var q1= 
    from entry in MyTable
    group entry by new{entry.TestDate.Month, entry.TestDate.Year} into g
    select g.Where(entry => entry.TestDate == g.Max(e => e.TestDate));

Note that this will literally create groups for each month/year combo, which may actually fit your business case better. If you need it flattened though, you can say:

var q2 = from g in q1
         from entry in g
         select entry;

or:

var q2 = q1.SelectMany(g => g)