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.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!