Skip to content

CROSS APPLY vs. ROW_NUMBER() #30450

@verdysh

Description

@verdysh

Hi!

Microsoft.EntityFrameworkCore.SqlServer, Version="6.0.5"

var dbOptionsBuilder = new DbContextOptionsBuilder<FilesContext>()
    .UseSqlServer(@"Server=.;Database=Files;User Id=sa;Password=pa$$w0rd;")
    .LogTo(Console.WriteLine, LogLevel.Information)
    .EnableSensitiveDataLogging();

await using var db = new FilesContext(dbOptionsBuilder.Options);
await db.Database.EnsureDeletedAsync();
await db.Database.EnsureCreatedAsync();
await Migrate(db);

var query =
    db.Folders
    .SelectMany(
        a => db.Files.Where(aa => aa.FolderId == a.Id).Take(1),
        (a, aa) => new { a, aa }
    );

var result = query.ToList();

Console.WriteLine("Done");
Console.ReadLine();

async Task Migrate(FilesContext db)
{
    var folder1 = new Folder
    {
        Id = new Guid("00642c1a-3afb-4acc-b0e6-956537af75b8"),
        Name = "C:",        
    };
    var folder2 = new Folder
    {
        Id = new Guid("aa2d7923-955d-4a2c-b7ad-10a0693e6200"),
        Name = "Test",
        ParentId = folder1.Id
    };
    var folder3 = new Folder
    {
        Id = new Guid("5e4a987a-4302-44e4-bbf8-5ab1b689d07a"),
        Name = "Documents",
        ParentId = folder1.Id
    };

    db.Folders.Add(folder1);
    db.Folders.Add(folder2);
    db.Folders.Add(folder3);

    //////////

    var file1 = new File
    {
        Id = new Guid("1c16ad5a-2f08-450a-843a-fe3ba00f126a"),
        Name = "temp.txt",
        FolderId = folder2.Id
    };
    var file2 = new File
    {
        Id = new Guid("4faaa7dd-2863-4e45-b38d-67a8cb845bab"),
        Name = "mydoc.doc",
        FolderId = folder2.Id
    };
    var file3 = new File
    {
        Id = new Guid("0bb822d3-f418-4d57-a4f6-bb7a11be6ffd"),
        Name = "book.pdf",
        FolderId = folder3.Id
    };
    
    db.Files.Add(file1);
    db.Files.Add(file2);
    db.Files.Add(file3);

    await db.SaveChangesAsync();
}

public class FilesContext: DbContext
{
    public DbSet<Folder> Folders { get; set; }
    public DbSet<File> Files { get; set; }

    public FilesContext(DbContextOptions<FilesContext> options): base(options)
    { }
}

public class Folder
{
    public Guid Id { get; set; }
    public Guid? ParentId { get; set; }
    public string Name { get; set; }

    public ICollection<File> Files { get; set; }
}

public class File
{
    public Guid Id { get; set; }
    public string Name { get; set; }
    public Guid FolderId { get; set; }
    public Folder Folder { get; set; }
}

The query from the code above results in the following SQL text:

SELECT [f].[Id], [f].[Name], [f].[ParentId], [t0].[Id], [t0].[FolderId], [t0].[Name]
      FROM [Folders] AS [f]
      INNER JOIN (
          SELECT [t].[Id], [t].[FolderId], [t].[Name]
          FROM (
              SELECT [f0].[Id], [f0].[FolderId], [f0].[Name], ROW_NUMBER() OVER(PARTITION BY [f0].[FolderId] ORDER BY [f0].[Id]) AS [row]
              FROM [Files] AS [f0]
          ) AS [t]
          WHERE [t].[row] <= 1
      ) AS [t0] ON [f].[Id] = [t0].[FolderId]

I want the SQL text to contain CROSS APPLY statement:

select *
from folders f
cross apply (
	select top 1 *
	from Files ff
	where ff.FolderId = f.Id
) ff

The data I get as a result is the same, but the query plans are different.
How can I do a query exactly with CROSS APPLY statement?

Metadata

Metadata

Assignees

No one assigned

    Projects

    No projects

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions