Find next value in list of objects

Question

I have a table, let's call it quotation_lines, like this:

Quote No	Product	Date Effective	Price
Q1	ABC	1-Jan-20	10.00
Q1	ABC	1-Mar-20	15.00
Q1	ABC	1-Dec-20	20.00
Q1	ABC	1-Mar-21	10.00
Q2	ABC	1-Feb-22	13.5

And I want to create an object like:

public class QuoteLine{
    public string QuoteNo {get;set;}
    public string Product {get;set;}
    public DateOnly DateEffective {get;set;}
    public decimal Price {get;set;}
    public DateOnly NextDate {get;set;}
    public decimal NextPrice {get;set;}
}

The question is, how do I get the next row of data to populate my object? So for example, when I am considering the first row of data, my object would end up as

{
    "QuoteNo": "Q1",
    "Product": "ABC",
    "DateEffective": "1-Jan-20",
    "Price": 10,
    "NextDate": "1-Mar-20",
    "NextPrice": 15
}

And for the second row it would be

{
    "QuoteNo": "Q1",
    "Product": "ABC",
    "DateEffective": "1-Mar-20",
    "Price": 15,
    "NextDate": "1-Dec-20",
    "NextPrice": 20
}

And so on.

For the avoidance of doubt, this will be grouped by the quote number and the product.

If it's easier to do it by creating a view on the db, that's fine, but I have been struggling with that for about 4 hours now, and I can't work that out either.

Any assistance greatly appreciated.

Answer 1

The question is unclear. This appears to be a LEAD / LAG problem, which EF Core 8 cannot solve easily to my knowledge. There is another ORM project linq2db which can be used to fill in areas where EF is weak. Alternatively you can just write a raw SQL query / view and use EF to perform the mapping.

The linq2db query which appears to solve your request. Note: linq2db can be used without EF Core as a standalone ORM. It has scaffolding and a host of other features listed on their GitHub readme.

connection
    .GetTable<QuoteLine>()
    .Select(q => new
    {
        q.QuoteNo,
        q.Product,
        q.DateEffective,
        q.Price,
        NextDate = Sql.Ext.Lead(q.DateEffective)
            .Over().PartitionBy(q.QuoteNo).OrderBy(q.DateEffective).ToValue(),
        NextPrice = Sql.Ext.Lead(q.Price)
            .Over().PartitionBy(q.QuoteNo).OrderBy(q.DateEffective).ToValue(),
    })
    .ToList()

// SELECT
//     [q].[QuoteNo],
//     [q].[Product],
//     [q].[DateEffective],
//     [q].[Price],
//     LEAD([q].[DateEffective]) OVER(PARTITION BY [q].[QuoteNo] ORDER BY [q].[DateEffective]),
//     LEAD([q].[Price]) OVER(PARTITION BY [q].[QuoteNo] ORDER BY [q].[DateEffective])
// FROM
//     [quotation_lines] [q]

Below is the full program source showing the minimal configuration and setup required to reuse your EF Core model in linq2db.

using System;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;
using System.Text.Json;
using LinqToDB;
using LinqToDB.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore;

// linq2db Nuget Packages added (License: MIT)
// <PackageReference Include="linq2db" Version="5.4.1" />
// <PackageReference Include="linq2db.EntityFrameworkCore" Version="8.1.0" />

public class Program
{
    public static void Main(string[] args)
    {
        // So you can see the SQL generated by linq2db
        LinqToDB.Data.DataConnection.TurnTraceSwitchOn();
        LinqToDB.Data.DataConnection.WriteTraceLine = (message, _, _) => Console.WriteLine(message);
        
        using (var context = new SalesContext())
        using (var connection = context.CreateLinqToDBConnection())
        {
            var output = connection
                .GetTable<QuoteLine>()
                .Select(q => new
                {
                    q.QuoteNo,
                    q.Product,
                    q.DateEffective,
                    q.Price,
                    NextDate = Sql.Ext.Lead(q.DateEffective)
                        .Over().PartitionBy(q.QuoteNo).OrderBy(q.DateEffective).ToValue(),
                    NextPrice = Sql.Ext.Lead(q.Price)
                        .Over().PartitionBy(q.QuoteNo).OrderBy(q.DateEffective).ToValue(),
                })
                .ToList();

            Console.WriteLine(JsonSerializer.Serialize(output, new JsonSerializerOptions { WriteIndented = true }));
        }
    }
}

public class SalesContext : DbContext
{
    public DbSet<QuoteLine> QuoteLines { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder.UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=Sales;Trusted_Connection=True");

    protected override void ConfigureConventions(ModelConfigurationBuilder configurationBuilder)
        => configurationBuilder.Properties<decimal>().HavePrecision(18, 2);
}

[Table("quotation_lines")]
public class QuoteLine
{
    public long Id { get; set; } // EF Needs a PrimaryKey, so I added this
    [MaxLength(100)] public string QuoteNo { get; set; }
    [MaxLength(100)] public string Product { get; set; }
    public DateOnly DateEffective { get; set; }
    public decimal Price { get; set; }
}