Emulating SQL Server 2012 Lag Window function with LINQ

Question

I've got some sample data that look like: (Edit: I don't have SQL Server 2012)

create table #base
(pat_id int
,admission_date date
,discharge_date date
)
go
insert into #base
values
(1, '2007-01-04',   '2007-01-04'),
(1, '2007-01-10',   '2007-01-10'),
(1, '2007-01-11',   '2007-01-11'),
(1, '2007-01-18',   '2007-01-18'),
(1, '2007-01-24',   '2007-01-24'),
(1, '2008-01-25',   '2008-01-26'),
(2, '2007-02-01',   '2007-02-01'),
(2, '2007-02-06',   '2007-02-06'),
(2, '2007-02-07',   '2007-02-07'),
(2, '2007-02-08',   '2007-02-08')

Here's the SQL query I wish to emulate with LINQ

;with cte 
as
(
    select   pat_id
            ,admission_date
            ,discharge_date
            ,ROW_NUMBER() over(partition by pat_id order by admission_date asc) as rn
    from #base
)
select   firstCte.pat_id
        ,firstCte.discharge_date as firstAdmitReference
        ,secondCte.admission_date as dischargeReference
        ,case when DATEDIFF(DAY,firstCte.discharge_date,secondCte.admission_date) <= 30 then 1 else 0 end Readmit
from cte as firstCte
inner join cte as secondCte
      on firstCte.pat_id = secondCte.pat_id
where firstCte.rn = secondCte.rn -1 

Here's what the result of that query looks like:

create table #readmit_data
( pat_id int
 ,admission_date date
 ,discharge_date date
 ,within_x_days int
 )
insert into #readmit_data(pat_id,admission_date,discharge_date,within_x_days)
values

(1, '2007-01-04',   '2007-01-10',   1),
(1, '2007-01-10',   '2007-01-11',   1),
(1, '2007-01-11',   '2007-01-18',   1),
(1, '2007-01-18',   '2007-01-24',   1),
(1, '2007-01-24',   '2008-01-25',   0),
(2, '2007-02-01',   '2007-02-06',   1),
(2, '2007-02-06',   '2007-02-07',   1),
(2, '2007-02-07',   '2007-02-08',   1)

In this result set the basic format for the data is

patient ID dischargeDate nextVisitAdmitDate

A 1 in the within_x_days column indicates that a patient had a readmission with 30 days of their last discharge. Ideally, 30 will be a variable entered in by the user.

What sort of construct in LINQ is there available for doing this?

Answer 1

LinqToSql has no analagous function to lag/lead/row_number.

You simply cannot do that yet, the easiest way is to send the SQL over to the machine and then map the results set to your object when it gets back.