DbReadBench.cs

using System.Buffers;
using System.ComponentModel.DataAnnotations;
using BenchmarkDotNet.Order;
using Dapper;
using LinqToDB;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Infrastructure;
using Npgsql;

namespace ConsoleApp1;

[SimpleJob(RuntimeMoniker.Net90)]
[Orderer(SummaryOrderPolicy.FastestToSlowest)]
[MeanColumn, MemoryDiagnoser]
public class DbReadBench : IDisposable
{
    public const string ConnectionString = "Server=localhost;Port=5432;Database=postgres;User Id=postgres;Password=123456;";
    private const int BlogCount = 100;
    private const int PostPerBlogCount = 200;
    
    private static readonly CancellationToken Cancellation = CancellationToken.None;
    private  static readonly Func<BloggingContext, IAsyncEnumerable<Blog>> GetBlogs = 
        EF.CompileAsyncQuery((BloggingContext context) => context.Blogs
            .Include(static b => b.Posts)
            .AsNoTracking());

    private static Dictionary<Guid, Blog>? _blogCache;
    private PooledDbContextFactory<BloggingContext> _entityFramework = null!;
    private NpgsqlDataSource _dapper = null!;

    [Benchmark]
    public async Task<Blog[]> Ef_AsAsyncEnumerable()
    {
        await using var context = await _entityFramework
            .CreateDbContextAsync(Cancellation)
            .ConfigureAwait(false);
        
        var sql = context.Blogs
            .Include(static b => b.Posts)
            .AsNoTracking();
    
        var arrayPool = ArrayPool<Blog>.Shared;
        var buffer = arrayPool.Rent(128);
        var count = 0;
        await foreach (var blog in EntityFrameworkQueryableExtensions
                           .AsAsyncEnumerable(sql)
                           .WithCancellation(Cancellation)
                           .ConfigureAwait(false))
        {
            buffer[count++] = blog;
            
            if (count < buffer.Length) continue;
            var newBuffer = arrayPool.Rent(count * 2);
            Array.Copy(buffer, newBuffer, count);
            arrayPool.Return(buffer, true);
            buffer = newBuffer;
        }
        
        var blogs = count == 0 ? [] : buffer[..count];
        arrayPool.Return(buffer, true);
        return blogs;
    }
    
    [Benchmark]
    public async Task<Blog[]> Ef_Compiled()
    {
        await using var context = await _entityFramework
            .CreateDbContextAsync(Cancellation)
            .ConfigureAwait(false);

        var arrayPool = ArrayPool<Blog>.Shared;
        var buffer = arrayPool.Rent(128);
        var count = 0;
        await foreach (var blog in GetBlogs(context)
                           .WithCancellation(Cancellation)
                           .ConfigureAwait(false))
        {
            buffer[count++] = blog;
            
            if (count < buffer.Length) continue;
            var newBuffer = arrayPool.Rent(count * 2);
            Array.Copy(buffer, newBuffer, count);
            arrayPool.Return(buffer, true);
            buffer = newBuffer;
        }
        
        var blogs = count == 0 ? [] : buffer[..count];
        arrayPool.Return(buffer, true);
        return blogs;
    }
    
    [Benchmark]
    public async Task<Blog[]> Ef_FromSql_TwoQuery()
    {
        await using var context = await _entityFramework
            .CreateDbContextAsync(Cancellation)
            .ConfigureAwait(false);
        
        var blogs = await EntityFrameworkQueryableExtensions
            .ToArrayAsync(context.Blogs.FromSqlRaw("SELECT * FROM \"Blogs\""), Cancellation)
            .ConfigureAwait(false);
        
        var cache = Interlocked.Exchange(ref _blogCache, null) ?? new Dictionary<Guid, Blog>(256);
        foreach (var blog in blogs)
        {
            cache.Add(blog.Id, blog);
        }
        
        var blogIds = blogs.Select(b => b.Id);
        var postSql = context.Posts.FromSql($"SELECT * FROM \"Posts\" WHERE \"BlogId\" = ANY({blogIds})");
        await foreach (var post in EntityFrameworkQueryableExtensions
                           .AsAsyncEnumerable(postSql)
                           .WithCancellation(Cancellation)
                           .ConfigureAwait(false))
        {
            if (!cache.TryGetValue(post.BlogId, out var existsBlog)) continue;
    
            post.Blog = existsBlog;
            existsBlog.Posts.Add(post);
        }
    
        cache.Clear();
        Interlocked.Exchange(ref _blogCache, cache);
    
        return blogs;
    }
    
    [Benchmark]
    public async Task<Blog[]> Ef_ToArray()
    {
        await using var context = await _entityFramework
            .CreateDbContextAsync(Cancellation)
            .ConfigureAwait(false);
        
        var sql = context.Blogs
            .Include(static b => b.Posts)
            .AsNoTracking();
        
        var blogs = await EntityFrameworkQueryableExtensions
            .ToArrayAsync(sql, Cancellation)
            .ConfigureAwait(false);
        
        return blogs;
    }
    
    [Benchmark]
    public async Task<List<Blog>> Ef_ToList()
    {
        await using var context = await _entityFramework
            .CreateDbContextAsync(Cancellation)
            .ConfigureAwait(false);
        
        var sql = context.Blogs
            .Include(static b => b.Posts)
            .AsNoTracking();
        
        var blogs = await EntityFrameworkQueryableExtensions
            .ToListAsync(sql, Cancellation)
            .ConfigureAwait(false);
        
        return blogs;
    }
    
    [Benchmark(Baseline = true)]
    public async Task<Blog[]> Dapper_TwoQuery()
    {
        await using var connection = await _dapper
            .OpenConnectionAsync(Cancellation)
            .ConfigureAwait(false);
        
        var blogRows = await connection
            .QueryAsync<Blog>("SELECT * FROM \"Blogs\" ORDER BY \"Id\"", Cancellation)
            .ConfigureAwait(false);
    
        var cache = Interlocked.Exchange(ref _blogCache, null) ?? new Dictionary<Guid, Blog>(256);
        foreach (var blogRow in blogRows)
        {
            cache.Add(blogRow.Id, blogRow);
        }
        
        var posts = await connection
            .QueryAsync<Post>("SELECT * FROM \"Posts\" WHERE \"BlogId\" = ANY(@ids)", new { ids = cache.Keys.ToArray() })
            .ConfigureAwait(false);
    
        foreach (var post in posts)
        {
            if (!cache.TryGetValue(post.BlogId, out var existsBlog)) continue;
            existsBlog.Posts.Add(post);
            post.Blog = existsBlog;
        }
        
        var blogs = cache.Values.ToArray();
        
        cache.Clear();
        Interlocked.Exchange(ref _blogCache, cache);
        return blogs;
    }
    
    [Benchmark]
    public async Task<Blog[]> Dapper_Join()
    {
        await using var connection = await _dapper.OpenConnectionAsync(Cancellation);
        
        var cache = Interlocked.Exchange(ref _blogCache, null) ?? new Dictionary<Guid, Blog>(256);
        await connection.QueryAsync<Blog, Post, Blog>("SELECT b.\"Id\", b.\"IsDeleted\", b.\"Rating\", b.\"Url\", p.\"Id\", p.\"Content\", p.\"IsDeleted\", p.\"Title\" FROM \"Blogs\" b LEFT JOIN \"Posts\" p ON b.\"Id\" = p.\"BlogId\" ORDER BY b.\"Id\"", (blog, post) =>
        {
            if (!cache.TryGetValue(blog.Id, out var blogInstance))
            {
                blogInstance = blog;
                cache.Add(blog.Id, blogInstance);
            }
    
            post.Blog = blogInstance;
            post.BlogId = blogInstance.Id;
            
            blogInstance.Posts.Add(post);
            
            return blogInstance;
        }, splitOn: "Id").ConfigureAwait(false);;
    
        var blogs = cache.Values.ToArray();
        cache.Clear();
        Interlocked.Exchange(ref _blogCache, cache);
        
        return blogs;
    }
    
    [Benchmark]
    public async Task<Blog[]> Linq2Db()
    {
        await using var db = new Blogging();
    
        var sql = db.Blog.LoadWithAsTable(static b => b.Posts);
    
        var blogs = await AsyncExtensions
            .ToArrayAsync(sql, Cancellation)
            .ConfigureAwait(false);
        
        return blogs;
    } 
    
    [GlobalCleanup]
    public void Dispose()
    {
        _dapper?.Dispose();
    }
    
    [GlobalSetup]
    public void Setup()
    {
        var random = new Random(123);
        
        _dapper = NpgsqlDataSource.Create(ConnectionString);

        _entityFramework = new PooledDbContextFactory<BloggingContext>(
            new DbContextOptionsBuilder<BloggingContext>()
                .UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking)
                .UseNpgsql(ConnectionString)
                .Options);
        
        using var efContext = _entityFramework.CreateDbContext();

        var blogs = efContext.Blogs;
        var posts = efContext.Posts;

        if (blogs.Count() == BlogCount)
        {
            Console.WriteLine("Data already exists");
            return;
        }
        
        posts
            .Where(p => p.IsDeleted == false)
            .ExecuteDelete();

        blogs
            .Where(b => b.IsDeleted == false)
            .ExecuteDelete();

        efContext.SaveChanges();

        for (var blog = 0; blog < BlogCount; blog++)
        {
            var blogId = Guid.NewGuid();
            blogs.Add(new Blog
            {
                Id = blogId,
                IsDeleted = false,
                Rating = random.Next(),
                Url = CreateRandomString(random, 128)
            });

            for (var post = 0; post < PostPerBlogCount; post++)
            {
                posts.Add(new Post
                {
                    Id = Guid.NewGuid(),
                    BlogId = blogId,
                    Content = CreateRandomString(random, 1024),
                    IsDeleted = false,
                    Title = CreateRandomString(random, 128),
                });
            }
        }

        efContext.SaveChanges();
    }

    private static string CreateRandomString(Random random, int length)
    {
        const string chars = "ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789";
        Span<char> buffer = stackalloc char[length];

        random.GetItems(chars, buffer);
        return buffer[..length].ToString();
    }
}

public sealed class BloggingContext : DbContext
{
    public DbSet<Blog> Blogs { get; set; }
    public DbSet<Post> Posts { get; set; }

    public BloggingContext() : this(new DbContextOptionsBuilder<BloggingContext>()
        .UseNpgsql(DbReadBench.ConnectionString).Options)
    {
    }

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

public sealed class Blogging : LinqToDB.Data.DataConnection
{
    public Blogging() : base(new DataOptions().UsePostgreSQL(DbReadBench.ConnectionString)) { }

    public ITable<Blog> Blog  => this.GetTable<Blog>();
    public ITable<Post> Post => this.GetTable<Post>();
}

[LinqToDB.Mapping.Table("Blogs")]
public sealed class Blog
{
    [LinqToDB.Mapping.PrimaryKey, LinqToDB.Mapping.Identity, LinqToDB.Mapping.Column]
    public required Guid Id { get; set; }
    
    [LinqToDB.Mapping.Column]
    public required int Rating { get; set; }
    
    [LinqToDB.Mapping.Column, MaxLength(256)]
    public required string Url { get; set; }

    [LinqToDB.Mapping.Column]
    public required bool IsDeleted { get; set; }

    [LinqToDB.Mapping.Association(ThisKey = nameof(Id), OtherKey = nameof(Post.BlogId))]
    public List<Post> Posts { get; set; } = [];
}

[LinqToDB.Mapping.Table("Posts")]
public sealed class Post
{
    [LinqToDB.Mapping.PrimaryKey, LinqToDB.Mapping.Identity, LinqToDB.Mapping.Column]
    public required Guid Id { get; set; }
    
    [LinqToDB.Mapping.Column, MaxLength(2048)]
    public required string Content { get; set; }
    
    [LinqToDB.Mapping.Column, MaxLength(256)]
    public required string Title { get; set; }

    [LinqToDB.Mapping.Column]
    public required bool IsDeleted { get; set; }

    [LinqToDB.Mapping.Column]
    public required Guid BlogId { get; set; }
    
    [LinqToDB.Mapping.Association(ThisKey = nameof(BlogId), OtherKey=nameof(Blog.Id))]
    public Blog Blog { get; set; }
}