I'm trying to convert the following to a LINQ to SQL statement in C#. Can anyone give me a hand? Basically my table keeps record of all history of changes such that the created date max date for each seedlot is the most recent record and the correct one to show.

SELECT 
    reports.* 
FROM
    [dbo].[Reports] reports
WHERE
    reports.createdDate
IN (
    SELECT 
        MAX(report_max_dates.createdDate) 
    FROM 
        [dbo].[Reports] report_max_dates
    GROUP BY 
        report_max_dates.Lot
    )

So far this is what I have.

var result = (from report in db.Reports
    where report.createdDate == (from report_max in db.Reports
                                group report_max by report_max.Lot into report_max_grouped
                                select report_max_grouped).Max()
    select report);

I can't figure out how to get the MAX dates for all reports and how to do an IN statement on the report.createdDate.

Thansk, Dman