how to write group by clause with row_number-1 order by time desc in linq

Question

i want to join 2 tables overview and transformerlocations table. i want to find the latest record of each deviceimei and then join with locations table.

[HttpGet]
[Route("overview_locations")]
public HttpResponseMessage GetoverviewLocations()
{
    var overviewDetails = _dbContext.Overviews.ToList();
    if (overviewDetails.Count != 0)
    {
        return Request.CreateResponse(HttpStatusCode.OK, overviewDetails, Configuration.Formatters.JsonFormatter);
    }
    else
    {
       
    }
}

sample data

2020-11-23 01:03:07.000 8673220311024   0   0   23.842163   91.280693
2020-11-23 01:01:06.000 8673220311024   0   0   23.842163   91.280693
2020-11-23 01:00:00.000 8645020301067   0   0   23.841940   91.280306

Exp op

 2020-11-23 01:03:07.000 8673220311024   0   0   23.842163   91.280693
 2020-11-23 01:00:00.000 8645020301067   0   0   23.841940   91.280306

sql

select * 
from 
    (select 
        o.devicetimestamp, 
        o.deviceimei, 
        o.oti_a, 
        oti_t,  
        ts.latitude, 
        ts.longitude, 
        row_number() over(partition by o.deviceimei order by o.devicetimestamp desc) rn 
    from overview  
    inner join transformerlocations ts 
        on o.deviceimei = ts.imei  
    where ts.latitude is not null) t 
where rn = 1

Perhaps my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) might help you. — NetMage, Nov 23 '20 at 21:27
Short answer, EF do not support Window Functions. Run SQL via Dapper or use extensions, like https://github.com/linq2db/linq2db.EntityFrameworkCore — Svyatoslav Danyliv, Nov 24 '20 at 08:37
It is important to say what LINQ you are using: LINQ to SQL / EF 6.x / EF Core 2.0 / EF Core 2.1 / EF Core 3.x? — NetMage, Nov 25 '20 at 20:01

score 0 · Accepted Answer · answered Nov 25 '20 at 20:26

To translate filtering by the first ROW_NUMBER() you need to group by the PARTITION and then order each group as desired and select the first item in each group.

var ans = from o in overview
          join ts in transformerlocations on o.deviceimei equals ts.imei
          where ts.latitude != null
          group new { o, ts } by o.deviceimei into otsg
          select (
             from ots in otsg
             orderby ots.o.devicetimestamp descending
             select new {
                 ots.o.devicetimestamp,
                 ots.o.deviceimei,
                 ots.o.oti_a,
                 ots.ts.latitude,
                 ots.ts.longitude
             }
          ).FirstOrDefault();

how to write group by clause with row_number-1 order by time desc in linq

1 Answers1