Adds support for split queries

[QianMoXi]

the subquery is not converted when using a split query.

example code:

public class Table1
{
    public int          Id      { get; set; }
    public List<Table2> Table2s { get; set; }
    public List<Table3> Table3s { get; set; }
}
public class Table2
{
    public int    Id       { get; set; }
    public int    Table1ID { get; set; }
    public Table1 Table1   { get; set; }
}

public class Table3
{
    public int    Id       { get; set; }
    public int    Table1ID { get; set; }
    public Table1 Table1   { get; set; }
}
public class MyDbContext : DbContext
{
    public MyDbContext(DbContextOptions options) : base(options)
    {
    }

    public DbSet<Table1> Table1s { get; set; }
    public DbSet<Table2> Table2s { get; set; }
    public DbSet<Table3> Table3s { get; set; }
}

internal class Program
{
    static void Main(string[] args)
    {
        var services = new ServiceCollection();
        services.AddLogging(b => b.AddConsole());
        services.AddDbContext<MyDbContext>(b =>
        {
            b.UseSqlServer("Data Source=localhost;Initial Catalog=TestDb1;Integrated Security=True;Encrypt=False;", s =>
            {
                s.UseRowNumberForPaging();
            });
        });

        using var provider = services.BuildServiceProvider();
        using (var scope = provider.CreateScope())
        {
            var context = scope.ServiceProvider.GetRequiredService<MyDbContext>();
            context.Database.EnsureDeleted();
            context.Database.EnsureCreated();

            for (var i = 0; i < 100; i++)
            {
                context.Table1s.Add(new()
                {
                    Table2s = new() {new(), new(), new(), new()},
                    Table3s = new() {new(), new(), new(), new()}
                });
            }

            context.SaveChanges();

            context.Table1s
                   .Include(x => x.Table2s)
                   .Include(x => x.Table3s)
                   .Skip(5)
                   .Take(5)
                   .AsSplitQuery() // using a split query
                   .ToList();
        }

        Console.ReadLine();
    }
}

[Rwing]

Yes, it has been confirmed as a bug and I will take care of it as soon as not busy

[deaquino]

Hi everyone,

Facing the same issue. Can you point us, so we can help coding this, please?

Cheers

[QianMoXi]

@deaquino I haven't used SQL Server in a long time and can't test it.

EF Core puts the split query in the RelationalSplitCollectionShaperExpression, so you can handle it in the VisitExtension method as well, which might solve the problem

Related Links: https://learn.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.query.relationalsplitcollectionshaperexpression?view=efcore-8.0

[LukaMatkic]

@deaquino I have solved the problem by adding code at beginning of "VisitSelect" method:

if (selectExpression.Tables.Any())
{
    selectExpression = selectExpression.Update(projections: selectExpression.Projection.ToList(),
        predicate: selectExpression.Predicate,
        tables: selectExpression.Tables.Select(x => (TableExpressionBase)VisitExtension(x)).ToList().AsReadOnly(),
        groupBy: selectExpression.GroupBy.ToList(),
        having: selectExpression.Having,
        orderings: selectExpression.Orderings,
        limit: selectExpression.Limit,
        offset: selectExpression.Offset);
}

Problem is "VisitExtension" method is not called for property "Tables" when processing expression type "SelectExpression". This code will ensure it is called. I did this for .NET 8 visitor class. This fix may not be the most optimal one but it works.

Full code:

private Expression VisitSelect(SelectExpression selectExpression)
{
        if (selectExpression.Tables.Any())
        {
            selectExpression = selectExpression.Update(projections: selectExpression.Projection.ToList(),
                predicate: selectExpression.Predicate,
                tables: selectExpression.Tables.Select(x => (TableExpressionBase)VisitExtension(x)).ToList().AsReadOnly(),
                groupBy: selectExpression.GroupBy.ToList(),
                having: selectExpression.Having,
                orderings: selectExpression.Orderings,
                limit: selectExpression.Limit,
                offset: selectExpression.Offset);
        }
  
        var oldOffset = selectExpression.Offset;
        if (oldOffset == null)
            return selectExpression;
        var oldLimit = selectExpression.Limit;
        var oldOrderings = selectExpression.Orderings;
        var newOrderings = oldOrderings.Count > 0 && (oldLimit != null || selectExpression == root)
            ? oldOrderings.ToList()
            : new List<OrderingExpression>();
      
        ...

[tonyqus]

Yes, it has been confirmed as a bug and I will take care of it as soon as not busy

Can you put some time on this ticket? I think you were NOT busy in the past 3 years. At least you have time to bully others like me.