How do I do this with LINQ?

Question

I'm not entirely sure if I'm trying to do something a little too complex for this but what I essentially want to do is turn this:

declare @callid int
set @callid = 57
declare @update nvarchar(max)
declare update_cursor cursor for
select UpdateIdentity from [Helpdesk_HR].[dbo].[hdEvents] 
where CallID = @callid group by UpdateIdentity order by UpdateIdentity 
open update_cursor 

fetch next from update_cursor
into @update

while @@FETCH_STATUS = 0
begin

  select * 
  from [Helpdesk_HR].[dbo].[hdEvents]
  where UpdateIdentity = @update

  fetch next from update_cursor
  into @update
end
close update_cursor
deallocate update_cursor

into the LINQ equivalent. Can anyone tell me if this is even possible with LINQ? if so how?

Thanks in advance.

Answer 1

Assuming you have converted the database using SQLmetal.exe this should do it. hdEvents should have the data you want.

var identities = 
    from hdevent in context.hdEvents
    where hdevent.CallID == 57
    group hdevent by hdevent.UpdateIdentity into distinctIdentities
    select distinctIdentities.Key;

var hdEvents = 
    from indenity in identities
    from hdevent in context.hdEvents
    where hdevent.UpdateIdentity == indenity
    select hdevent;

Answer 2

You'll need some O/R Mapping to your database using LINQ to SQL and then this can be done in LINQ.