using System;
using System.Linq;
using System.Collections.Generic;
using System.Threading;
using System.Threading.Tasks;
using Microsoft.Data.Sqlite;
using Microsoft.EntityFrameworkCore;

await using var connection = new SqliteConnection("Data Source=:memory:;Foreign Keys=True");
await connection.OpenAsync();
var counter = new ReadCounter();
var options = new DbContextOptionsBuilder<LabDb>().UseSqlite(connection).AddInterceptors(counter).Options;
await using var db = new LabDb(options); await db.Database.EnsureCreatedAsync();
db.Blogs.AddRange(new Blog { Name = "A", Posts = { new Post { Title = "One" }, new Post { Title = "Two" } } }, new Blog { Name = "B", Posts = { new Post { Title = "Three" } } }); await db.SaveChangesAsync(); db.ChangeTracker.Clear();
counter.Reset(); var parents = await db.Blogs.AsNoTracking().OrderBy(b => b.Name).ToListAsync(); var repeated = new List<string>();
foreach (var blog in parents) repeated.Add(blog.Name + ":" + await db.Posts.CountAsync(p => p.BlogId == blog.Id));
Check(counter.Reads == 3 && string.Join(",", repeated) == "A:2,B:1", "N+1 observations"); Console.WriteLine("loop-commands:" + counter.Reads);
counter.Reset(); var rows = await db.Blogs.OrderBy(b => b.Name).Select(b => new { b.Name, Count = b.Posts.Count }).ToListAsync();
Check(counter.Reads == 1 && string.Join(",", rows.Select(x => x.Name + ":" + x.Count)) == "A:2,B:1", "projection command and values"); Console.WriteLine("projection-commands:" + counter.Reads);

static void Check(bool condition, string name) { if (!condition) throw new InvalidOperationException("CHECK: " + name); }