むしゃくしゃしたので若干深夜テンション込みで書き殴り。
とっちらかってるし見落しとか何とかいろいろあるはず。

#結論
・データ取得時 (特に編集をしない場合) はAsNoTracking()を呼んでおく
・ThenInclude()は可能であれば使わない
・使う必要がある場合は上手く実装できないとThenInclude()を使うのが(多分)一番速い
~~・EFCoreなんて知らんとばかりにゴリゴリSQL発行するのがいいのかもしれない(試してない)~~

#追記
・ORDER BYがボトルネックになってたのでInclude()時のORDER BYについて調べてたらこんなのもあった → https://github.com/dotnet/efcore/issues/19828
~~・いっそ6.0リリース待つのが正解かもしれない~~

#前提
・既存の管理ツールが合わなかったので自前でBMSの管理ツール作ろうとした。
・フォルダ数が1万over,BMSファイル数が6万over
・開発は.NET5 WPF EFCore5.0 SQLServer2019

・BMSの特性上ファイルはフォルダ単位で管理
・ファイルが保管されているフォルダが入っているディレクトリをルートとする
・ルートフォルダは管理のために階層化する

#データの形式
・ルートフォルダ-BMSフォルダ-BMSファイルで階層にしてた。

#起きたこと
ファイルの読み込み→DBへの書き込みが恙無く完了し、いざDBからのデータ呼び出しを実行するとクッソ遅い。(30秒以上かかった)

最初のコード

using (var con = new Context())
{
    var root = con.Root
        .Include(d => d.Children)
        .Include(d => d.Folders)
        .ThenInclude(d => f.Files).ToArray();
}

とりあえずログ確認するとMicrosoft.EntityFrameworkCore.ChangeTrackingから始まるログが大量に流れてる → とりあえずAsNoTracking()を呼び出すように変更。最短10秒ちょっとまで縮まる。

AsNoTracking

using (var con = new Context())
{
    var root = con.Root
        .Include(d => d.Children)
        .Include(d => d.Folders)
        .ThenInclude(d => f.Files)
        .AsNoTracking().ToArray();
}

デバッグ実行するとToArray()でめちゃくちゃ時間がかかっていた。いくらInclude()・ThenInclude()があるとはいえ、内容的には単純なJOIN,LEFT JOINで取得できるような内容に時間かかりすぎな気がすると思ってSQL確認してみた。

SELECT [r].[ID], [r].[ParentRootID], [r].[Path], [r0].[ID], [r0].[ParentRootID], [r0].[Path], [t].[ID], [t].[Artist], [t].[Path], [t].[RootID], [t].[Title], [t].[ID0], [t].[Artist0], [t].[FolderID], [t].[MD5], [t].[Path0], [t].[Title0]
FROM [Root] AS [r]
LEFT JOIN [Root] AS [r0] ON [r].[ID] = [r0].[ParentRootID]
LEFT JOIN (
    SELECT [f].[ID], [f].[Artist], [f].[Path], [f].[RootID], [f].[Title], [f0].[ID] AS [ID0], [f0].[Artist] AS [Artist0], [f0].[FolderID], [f0].[MD5], [f0].[Path] AS [Path0], [f0].[Title] AS [Title0]
    FROM [Folder] AS [f]
    LEFT JOIN [File] AS [f0] ON [f].[ID] = [f0].[FolderID]
) AS [t] ON [r].[ID] = [t].[RootID]
ORDER BY [r].[ID], [r0].[ID], [t].[ID], [t].[ID0]

……なんでわざわざサブクエリ？
~~書きながら見返すと足引っ張ってるの完全にORDER BYだけどもういいや~~

試しにThenInclude()を外して実行してみると綺麗にLEFT JOINだけになって結果もすぐに返ってくるようになった。
Fileがロードされない状態になるので求めていたデータにはならないのだけれども。
あとはInclude()で階層化されたデータをロードしてもThenInclude()と等価になるっぽい。よくよく考えれば当然ではあるのだけれども。

こんな感じ

using (var con = new Context())
{
    var root = con.Root
        .Include(r => r.Children)
        .Include(r => r.Folders)
        .Include($"{nameof(Root.Folders)}.{nameof(Folder.Files)}")
        .AsNoTracking().ToArray();
}

というわけで先にFileテーブル全部読んでそれをFolderに投げ込むとか、Folder毎にクエリ発行するとかやってみたけど全滅。そりゃそうだ。万件オーダーのループに万件オーダーのループがネストされるんだから。

もうちょいなんとかならんかといろいろ調べる → こんなの見つける https://github.com/dotnet/efcore/issues/17622
→ 諦める (イマココ)

・ちなみに
FolderにFileをIncludeして実行したら即返ってきたのでデータ件数による問題ではないのは確実なはず。

書きながら別案も浮かんだので気力があれば試してみようと思う。~~どちらにしろ万件オーダーのループなので正直望み薄な気がする~~

・一応改善はできた

using (var con = new Context())
{
    var folders = con.BmsFolders
        .Include(f => f.Files)
        .AsNoTracking().ToArray();

    var allRoots = con.RootDirectories
        //.Include(r => r.Children)
        //.Include(d => d.Folders)
        //    .ThenInclude(f => f.Files)
        .AsNoTracking().ToArray();

    // これでも大分マシな速度は出た
    //foreach (var root in allRoots)
    //{
    //    root.Folders = folders.Where(f => f.RootID == root.ID).ToList();
    //}

    // ↑に比べてループ部分は10分の1ぐらいで終わった
    foreach (var folder in folders.GroupBy(f => f.RootID))
    {
        var root = allRoots.FirstOrDefault(r => r.ID == folder.Key);
        root.Folders = folder.ToList();
    }
}

~~車輪の再発明感がなくもない~~