Error in a Linq to Sql group Query

edited March 2012
I am using LinqPad for .Net FW 4.0.

The objective is to obtain latest value of EVENT_Date column.

The working T-Sql is
SELECT dbo.PROVIDERS.PROVIDER_ID, dbo.PROV_APPL_EVENTS.EVENTTYPE_ID, dbo.EVENT_TYPES.EVENT_TYPE_NAME, MAX(dbo.PROV_APPL_EVENTS.EVENT_DATE)
AS Event_Date
FROM dbo.PROVIDERS INNER JOIN
dbo.PROV_APPL_EVENTS ON dbo.PROVIDERS.PROVIDER_ID = dbo.PROV_APPL_EVENTS.PROVIDER_ID INNER JOIN
dbo.EVENT_TYPES ON dbo.PROV_APPL_EVENTS.EVENTTYPE_ID = dbo.EVENT_TYPES.EVENTTYPE_ID
GROUP BY dbo.PROVIDERS.PROVIDER_ID, dbo.EVENT_TYPES.EVENT_TYPE_NAME, dbo.PROV_APPL_EVENTS.EVENTTYPE_ID
HAVING (dbo.PROV_APPL_EVENTS.EVENTTYPE_ID = 2235) AND (dbo.PROVIDERS.PROVIDER_ID = 9;

I am trying to convert above t-sql into Linq query by following:

dim query2 = from p in PROVIDERS _
join pae in PROV_APPL_EVENTS on p.PROVIDER_ID equals pae.PROVIDER_ID _
join et in EVENT_TYPES on pae.EVENTTYPE_ID equals et.EVENTTYPE_ID _
where p.PROVIDER_ID = 99926 and et.EVENTTYPE_ID = 2235 _
group by key = new with {p.PROVIDER_ID, pae.EVENTTYPE_ID, et.EVENT_TYPE_NAME}
into LatestEventDate = Max(pae.EVENT_DATE) _
select new with {.ProviderId = p.PROVIDER_ID, _
.EventTypeId = pae.EVENTTYPE_ID, _
.EventTypeName = et.EVENT_TYPE_NAME, _
LatestEventDate}

query2.Dump()

and the error is: Name 'p' is either not declared or not in the current scope. The error marker points to "group by key = new with {p.PROVIDER_ID, pae.EVENTTYPE_ID, et.EVENT_TYPE_NAME}" statement.

What is wrong and how to correct it please?

Thank you,

Piyush
Sign In or Register to comment.