Converting SQL statement into Linq
- by DMan
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
