How write this query in EF Core or Linq

Question

Hello,
I want to write this query in linq or lambda EF core

SELECT LAG(id) OVER ( ORDER BY ID ) AS PreviousWord ,ID ,LEAD(id) OVER ( ORDER BY ID ) AS NextWord FROM sales

Please help

Answer 1

Hi @Prathamesh Shende ,

Based on your SQL command, I create a table with the following data, and modify the SQL query command to set the default value when using the LAG and LEAD function:

Then, to using EF core get the same result, we could use the following code:

var result = _context.Sales.OrderBy(d => d.ID).Select(c => new  
{  
    ID = c.ID,  
    PreviousId = _context.Sales.OrderBy(i => i.ID).Where(i => i.ID < c.ID).Select(i=>i.ID).LastOrDefault(),  
    NextId = _context.Sales.OrderBy(i => i.ID).Where(i => i.ID > c.ID).Select(i => i.ID).FirstOrDefault(),  
}).ToList();  

The Sale model:

public class Sale  
{  
    [Key]  
    public int ID { get; set; }  
    public int SalesAmountQuota { get; set; }  
    public DateTime QuotaDate { get; set; }  
}  

Besides, we could also use the FromSqlRaw extension method to execute the SQL query command.

Based on the SQL query result to create a Model:

public class SaleViewModel  
{  
    public int ID { get; set; }  
    public int PreviousWord { get; set; }  
    public int NextWord { get; set; }  
}  

Add to the ApplicationDbContext:

public class ApplicationDbContext: DbContext  
{   
    public DbSet<Sale> Sales { get; set; }  
    public DbSet<SaleViewModel> SaleViewModels { get; set; }  

Then, enable migration to generate the related table, after that, in the controller, use the following code to get the result:

var sqlresult = _context.SaleViewModels  
    .FromSqlRaw("SELECT  ID ,LAG(ID,1,0) OVER ( ORDER BY ID ) AS PreviousWord , LEAD(ID,1,0) OVER ( ORDER BY ID ) AS NextWord FROM sales")   
    .Select(c=> new { ID = c.ID, PreviousID = c.PreviousWord, NextId = c.NextWord })  
    .ToList();  

The result as below:

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Best Regards,
Dillion