SplitQuery order expression not equality

[Kation]

Include your code

Having two entities

public class EntityA
{
    public string KeyB { get; set; }
    public string KeyA { get; set; }
    public string Name { get; set; }
    public DateTime Time { get; set; }
    public ICollection<EntityB> B { get; set; }
}
public class EntityB
{
    public string KeyA { get; set; }
    public string KeyB { get; set; }
    public int Id { get; set; }
    [ForeignKey("KeyA,KeyB")]
    public EntityA A { get; set; }
}

Use multiple key

modelBuilder.Entity<EntityA>().HasKey(t => new { t.KeyA, t.KeyB });

Query A include B

dataContext.A.Include(t => t.B).OrderBy(t => t.Name).Skip(2).Take(2).ToList();

Split into two sql

SELECT `a`.`KeyA`, `a`.`KeyB`, `a`.`Name`, `a`.`Time`
      FROM `A` AS `a`
      ORDER BY `a`.`Name`,`a`.`KeyA`, `a`.`KeyB`
      LIMIT 2 OFFSET 2

SELECT `b`.`Id`, `b`.`KeyA`, `b`.`KeyB`, `t`.`KeyA`, `t`.`KeyB`
      FROM (
          SELECT `a`.`KeyA`, `a`.`KeyB`
          FROM `A` AS `a`
          ORDER BY `a`.`Name`
          LIMIT 2 OFFSET 2
      ) AS `t`
      INNER JOIN `B` AS `b` ON (`t`.`KeyA` = `b`.`KeyA`) AND (`t`.`KeyB` = `b`.`KeyB`)
      ORDER BY `t`.`KeyA`, `t`.`KeyB

First sql will generate order by fieds which primary key not used in order by.
But second sql will not generate it.

In some situation that results is different.
ICollection<EntityB> B will not have correct result.

I'm not sure it is ef core or provider issue.

EF Core version: 8.0.11
Database provider: Pomelo.EntityFrameworkCore.MySql 8.0.2
Target framework: .NET 8.0
Operating system: Windows 11
IDE: Visual Studio 2022 17.12

[cincuranet]

I don't understand what the error is. Here's a minimal skeleton. Can you adjust it to make it fail?

using System.ComponentModel.DataAnnotations.Schema;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Diagnostics;

using var db = new MyContext();
db.Database.OpenConnection();
db.Database.EnsureCreated();
db.A.Include(t => t.B).Skip(2).Take(2).ToList();

public class MyContext : DbContext
{
    public DbSet<EntityA> A { get; set; }
    public DbSet<EntityB> B { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<EntityA>().HasKey(t => new { t.KeyA, t.KeyB });
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.LogTo(Console.WriteLine, events: [RelationalEventId.CommandExecuting]);
        optionsBuilder.UseSqlite(o => o.UseQuerySplittingBehavior(QuerySplittingBehavior.SplitQuery));
    }
}

public class EntityA
{
    public string KeyB { get; set; }
    public string KeyA { get; set; }
    public string Name { get; set; }
    public DateTime Time { get; set; }
    public ICollection<EntityB> B { get; set; }
}
public class EntityB
{
    public string KeyA { get; set; }
    public string KeyB { get; set; }
    public int Id { get; set; }
    [ForeignKey("KeyA,KeyB")]
    public EntityA A { get; set; }
}

[Kation]

@cincuranet
I'm missing a order.

db.A.Include(t => t.B).OrderByDescending(t => t.Name).Skip(2).Take(2).ToList();

SELECT `a`.`KeyA`, `a`.`KeyB`, `a`.`Name`, `a`.`Time`
      FROM `A` AS `a`
      ORDER BY `a`.`Name` DESC,`a`.`KeyA`, `a`.`KeyB`
      LIMIT 2 OFFSET 2

get difference results with

SELECT `a`.`KeyA`, `a`.`KeyB`
          FROM `A` AS `a`
          ORDER BY `a`.`Name` DESC
          LIMIT 2 OFFSET 2

In some case.
And I don't know how to reproduce it, probably becauce mysql using sort with filesort.

[Kation]

@cincuranet
I can reproduce it.
EFCoreSplitQueryBug.zip

KeyB	KeyA	Name
23520264	8450678	1
23554666	8450678	1
23555165	8450678	1
23567661	8450678	1
23706881	8450678	1
23741621	8450678	1
23747905	8450678	1
23870918	8450678	1
23886112	8450678	1
24333522	8450678	1
24551899	8450678	1
b1	a1	2
b2	a1	2
b3	a1	1
b4	a1	1
b5	a1	1
b6	a1	1
b7	a1	2
b8	a1	2

 SELECT `a`.`KeyA`, `a`.`KeyB`, `a`.`Name`
      FROM `A` AS `a`
      ORDER BY `a`.`Name` DESC, `a`.`KeyA`, `a`.`KeyB`
      LIMIT 4 OFFSET 4

Return

KeyA	KeyB	Name
8450678	23520264	1
8450678	23554666	1
8450678	23555165	1
8450678	23567661	1

SELECT `a`.`KeyA`, `a`.`KeyB`
          FROM `A` AS `a`
          ORDER BY `a`.`Name` DESC
          LIMIT 4 OFFSET 4

Return

KeyA	KeyB
8450678	24551899
8450678	24333522
a1	b5
a1	b6

[roji]

The expectation here doesn't seem right to me; your LINQ query is the following:

var list = dataContext.A
    .Include(t => t.B)
    .OrderByDescending(t => t.Name)
    .Skip(4)
    .Take(4)
    .ToList();

You're only requesting the results to by ordered by Name, and not by the keys; as a result, results can be ordered in any random order, as long as they're ordered by Name first. Now, your test dataset contains lots of results with the same name 1 (only 4 out of 18 have another name 2); most of these have a B, but there are four which don't, so you can't expect any determinstic results as your code does.

By the way, I removed split querying, and saw the same behavior with single querying (your program reports "should 1: 0)". The SQL generated is:

SELECT [a0].[KeyA], [a0].[KeyB], [a0].[Name], [b].[Id], [b].[KeyA], [b].[KeyB]
FROM (
    SELECT [a].[KeyA], [a].[KeyB], [a].[Name]
    FROM [A] AS [a]
    ORDER BY [a].[Name] DESC
    OFFSET @__p_0 ROWS FETCH NEXT @__p_0 ROWS ONLY
) AS [a0]
LEFT JOIN [B] AS [b] ON [a0].[KeyA] = [b].[KeyA] AND [a0].[KeyB] = [b].[KeyB]
ORDER BY [a0].[Name] DESC, [a0].[KeyA], [a0].[KeyB]

Note that the inner subquery does not have ordering on KeyA or KeyB (becaues you haven't asked for that in your LINQ query), so it outputs non-determistic rows as well.

[Kation]

@roji The point is that split querying generate query A sql add primary keys to order by which unuse in order by.
So A results always same and each one contains a B.

 SELECT `a`.`KeyA`, `a`.`KeyB`, `a`.`Name`
      FROM `A` AS `a`
      ORDER BY `a`.`Name` DESC, `a`.`KeyA`, `a`.`KeyB`
      LIMIT 4 OFFSET 4

KeyA	KeyB	Name
8450678	23520264	1
8450678	23554666	1
8450678	23555165	1
8450678	23567661	1

But when it generate query B sql that select from A doesn't add primary keys to order by which unuse in order by.

SELECT `b`.`Id`, `b`.`KeyA`, `b`.`KeyB`, `t`.`KeyA`, `t`.`KeyB`
      FROM (
          SELECT `a`.`KeyA`, `a`.`KeyB`    <--
          FROM `A` AS `a`                  <--
          ORDER BY `a`.`Name`              <--
          LIMIT 4 OFFSET 4                 <--
      ) AS `t`
      INNER JOIN `B` AS `b` ON (`t`.`KeyA` = `b`.`KeyA`) AND (`t`.`KeyB` = `b`.`KeyB`)
      ORDER BY `t`.`KeyA`, `t`.`KeyB

In MySql return difference A results.

KeyA	KeyB
8450678	24551899
8450678	24333522
a1	b5
a1	b6

So A in list doesn't have there B, it should have if generated sql be.

SELECT `b`.`Id`, `b`.`KeyA`, `b`.`KeyB`, `t`.`KeyA`, `t`.`KeyB`
      FROM (
          SELECT `a`.`KeyA`, `a`.`KeyB`
          FROM `A` AS `a`
          ORDER BY `a`.`Name`,`a`.`KeyA`, `a`.`KeyB   <- add primary key to order by as same as query `A`
          LIMIT 4 OFFSET 4
      ) AS `t`
      INNER JOIN `B` AS `b` ON (`t`.`KeyA` = `b`.`KeyA`) AND (`t`.`KeyB` = `b`.`KeyB`)
      ORDER BY `t`.`KeyA`, `t`.`KeyB  <- I think this order by can remove

[roji]

Apologies, I looked again and you're right.

This issue has already been fixed in the latest bits (so EF Core 10) - unfortunately it make it in time to be released for EF 9.0; the tracking issue is #26808. I confirmed that before that fix (#34097), the ORDER BYs are missing from the subquery and the program outputs "Should be 1:0", but after that fix, the ORDER BYs are present and the program outputs "Should be 1:1".

[roji]

Duplicate of #26808