一個 Dapper 搭配 Func<T> 樣板工廠進行即席匿名類別查詢的性能測試 笑死 匿名更快

BenckmarkDapperImmedAnonType.cs

using System.ComponentModel.DataAnnotations.Schema;
using System.Reflection;
using BenchmarkDotNet.Attributes;
using Dapper;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;
using Microsoft.EntityFrameworkCore.Metadata.Conventions;
using Microsoft.Extensions.DependencyInjection;

namespace Benchmarks;

/* record
| Method        | Mean     | Error     | StdDev    | Median   | Ratio | RatioSD | Gen0    | Allocated | Alloc Ratio |
|-------------- |---------:|----------:|----------:|---------:|------:|--------:|--------:|----------:|------------:|
| DefinedType   | 1.818 ms | 0.1423 ms | 0.4196 ms | 1.631 ms |  1.05 |    0.34 | 15.6250 |  73.73 KB |        1.00 |
| AnonymousType | 1.479 ms | 0.0559 ms | 0.1530 ms | 1.440 ms |  0.86 |    0.21 | 15.6250 |  73.71 KB |        1.00 |
*/

[MemoryDiagnoser]
public class BenckmarkDapperImmedAnonType
{
    IServiceProvider _services = null!;

    [GlobalSetup]
    public void Setup()
    {
        var services = new ServiceCollection()
            .AddDbContextFactory<DbContext>(optionsBuilder =>
            {
                var model = new ModelBuilder(MySqlConventionSetBuilder.Build())
                    .ApplyConfigurationsFromAssembly(Assembly.GetExecutingAssembly())
                    .FinalizeModel();
                var connectionString = "Host=127.0.0.1; Uid=test; Database=test";
                optionsBuilder
                    .UseMySql(connectionString, ServerVersion.AutoDetect(connectionString))
                    .UseModel(model);
            })
            .BuildServiceProvider();
        var factory = services.GetRequiredService<IDbContextFactory<DbContext>>();
        using (var context = factory.CreateDbContext())
        {
            context.Database.EnsureDeleted();
            context.Database.EnsureCreated();
        }
        _services = services;
    }

    [GlobalCleanup]
    public void Cleanup()
    {
        var factory = _services.GetRequiredService<IDbContextFactory<DbContext>>();
        using (var context = factory.CreateDbContext())
            context.Database.EnsureDeleted();
        (_services as IDisposable)?.Dispose();
    }

    [Benchmark(Baseline = true)]
    public async Task<object> DefinedType()
    {
        var factory = _services.GetRequiredService<IDbContextFactory<DbContext>>();
        using var context = await factory.CreateDbContextAsync();
        var connection = context.Database.GetDbConnection();
        await connection.OpenAsync();
        return await connection.QueryAsync<QueryResult>(
            sql: """
            SELECT
                u.`name` AS Name,
                uc.`address` AS Address,
                uc.`phone` AS Phone,
                u.`create_at` AS CreateAt
            FROM `test`.`user` AS u
            JOIN `test`.`user_contact` AS uc ON uc.`id` = u.`id`
            WHERE u.`name` = @name;
            """,
            param: new
            {
                name = "test"
            });
    }

    [Benchmark]
    public async Task<object> AnonymousType()
    {
        var factory = _services.GetRequiredService<IDbContextFactory<DbContext>>();
        using var context = await factory.CreateDbContextAsync();
        var connection = context.Database.GetDbConnection();
        await connection.OpenAsync();
        return await connection.QueryAsync(
            sql: """
            SELECT
                u.`name` AS Name,
                uc.`address` AS Address,
                uc.`phone` AS Phone,
                u.`create_at` AS CreateAt
            FROM `test`.`user` AS u
            JOIN `test`.`user_contact` AS uc ON uc.`id` = u.`id`
            WHERE u.`name` = @name;
            """,
            param: new
            {
                name = "test"
            },
            model: () => new
            {
                Name = default(string),
                Address = default(string),
                Phone = default(string),
                CreateAt = default(DateTime)
            });
    }

    public class QueryResult
    {
        public string Name { get; set; } = null!;
        public string Address { get; set; } = null!;
        public string Phone { get; set; } = null!;
        public DateTime CreateAt { get; set; }
    }

    [PrimaryKey(nameof(Id))]
    [Index(nameof(Name))]
    [Table("user")]
    public class User
    {
        [Column("id")]
        public Guid Id { get; set; }

        [Column("name")]
        public string Name { get; set; } = null!;

        [Column("create_at")]
        public DateTime CreateAt { get; set; }

        public static User Default { get; } = new() { Id = Guid.NewGuid(), Name = "test", CreateAt = DateTime.Now };
    }

    [PrimaryKey(nameof(Id))]
    [Table("user_contact")]
    public class UserContact
    {
        [Column("id")]
        public Guid Id { get; set; }

        [Column("uid")]
        public Guid UserId { get; set; }
        [ForeignKey(nameof(UserId))]
        public User User { get; set; } = null!;

        [Column("address")]
        public string Address { get; set; } = null!;

        [Column("phone")]
        public string Phone { get; set; } = null!;

        public static UserContact Create(User user, string address, string phone)
            => new() { Id = Guid.NewGuid(), UserId = user.Id, Address = address, Phone = phone };
    }

    public class UserConfig : IEntityTypeConfiguration<User>
    {
        public void Configure(EntityTypeBuilder<User> builder)
            => builder.HasData(User.Default);
    }

    public class UserContactConfig : IEntityTypeConfiguration<UserContact>
    {
        public void Configure(EntityTypeBuilder<UserContact> builder)
            => builder.HasData(UserContact.Create(User.Default, "No. 25, Lane 3, First Avenue", "0912-345678"));
    }
}

DapperExtensions.cs

using System.Data;
using Dapper;

internal static class DapperExtensions
{
    public static Task<IEnumerable<T>> QueryAsync<T>(
        this IDbConnection cnn,
        string sql,
        object param,
        Func<T>? model,
        IDbTransaction? trans = default,
        int? timeout = default,
        CancellationToken token = default)
        => cnn.QueryAsync<T>(new(
            commandText: sql,
            parameters: param,
            transaction: trans,
            commandTimeout: timeout,
            cancellationToken: token));
}