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 options = new DbContextOptionsBuilder<LabDb>().UseSqlite(connection).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 Blog { Name = "B" }); await db.SaveChangesAsync(); db.ChangeTracker.Clear();
var query = db.Blogs.LeftJoin(db.Posts, b => b.Id, p => p.BlogId, (b, p) => new { Blog = b.Name, Title = p == null ? "[none]" : p.Title }).OrderBy(x => x.Blog).ThenBy(x => x.Title);
Check(query.ToQueryString().Contains("LEFT JOIN", StringComparison.OrdinalIgnoreCase), "actual EF10 left join translation");
var rows = await query.ToListAsync(); Check(string.Join(",", rows.Select(x => x.Blog + ":" + x.Title)) == "A:One,B:[none]", "matched and unmatched materialized");
foreach (var row in rows) Console.WriteLine(row.Blog + ":" + row.Title);

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