SQL Window 2012 function emulation with LINQ function

Question

I have some sample data that looks like this: (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 want 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 this 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 underlying data format

patient ID dischargeDate nextVisitAdmitDate

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

What type of construct in LINQ is available for this?

Answer 1

LinqToSql has no analogue for the function lag / lead / row_number.

, - SQL , , , .

Answer 2

Window To LINQ - .NET

Answer 3

lib linq2db https://github.com/linq2db/linq2db :

    from p in db.Parent
    join c in db.Child on p.ParentID equals c.ParentID
    select new
    {
        Diff = Sql.Ext
                  .Lag(x.time, Sql.Nulls.None)
                  .Over()
                  .PartitionBy(p.time.Date)
                  .OrderBy(p.time)
                  .ToValue()
    };

Answer 4

LinqTo2Db Lag Lead:

from p in db.Parent
    join c in db.Child on p.ParentID equals c.ParentID 
    select new
    {
        Lag = Sql.Ext
            .Lag(p.Value1, Sql.Nulls.None)
            .Over()
            .PartitionBy(p.Value1, c.ChildID)
            .OrderBy(p.Value1)
            .ThenBy(c.ChildID)
            .ThenBy(c.ParentID)
            .ToValue(),

        Lead = Sql.Ext
            .Lead(p.Value1, Sql.Nulls.None)
            .Over()
            .PartitionBy(p.Value1, c.ChildID)
            .OrderByDesc(p.Value1)
            .ThenBy(c.ChildID)
            .ThenByDesc(c.ParentID)
            .ToValue(),
    }

Sql.Nulls.Ignore Sql.Nulls.Respect SQL Server