【C# EntityFramework】ラムダ式ではなくプロパティ名で指定するOrderByの作り方

public IEnumerable<User> Select(DbContext context, int offset, int count, string orderName, string orderDirection)
{
    var entityType = typeof(User);
    var query = context.Users
        .Where(user => !user.DeleteFlag);
    if (orderDirection == "asc")
        query = query.OrderBy(user => entityType.GetProperty(orderName).GetValue(user));
    else
        query = query.OrderByDescending(user => entityType.GetProperty(orderName).GetValue(user));
    return query.Skip(offset).Take(count);
}

SELECT
  id,
  name,
  age,
  delete_flag
FROM Users
WHERE delete_flag = 0
ORDER BY age ASC
OFFSET 20 ROWS 
FETCH NEXT 10 ROWS ONLY

public IEnumerable<User> Select(DbContext context, int offset, int count, string orderName, string orderDirection)
{
    var entityType = typeof(User);
    var query = context.Users
        .Where(user => !user.DeleteFlag).AsEnumerable();
    if (orderDirection == "asc")
        query = query.OrderBy(user => entityType.GetProperty(orderName).GetValue(user));
    else
        query = query.OrderByDescending(user => entityType.GetProperty(orderName).GetValue(user));
    return query.Skip(offset).Take(count);
}

SELECT
  id,
  name,
  age,
  delete_flag
FROM Users
WHERE delete_flag = 0

public IEnumerable<User> Select(DbContext context, int offset, int count, string orderName, string orderDirection)
{
    var entityType = typeof(User);
    var query = context.Users
        .Where(user => !user.DeleteFlag);

    // 「user => user.Age」という式ツリーを生成
    var arg = Expression.Parameter(entityType, "user");
    var property = Expression.Property(arg, orderName);
    var selector = Expression.Lambda(property, new ParameterExpression[] { arg });

    // System.Linq.Queryable.OrderBy または OrderByDescending メソッドを
    // リフレクションで取得
    var direction = orderDirection != "asc" ? "OrderByDescending" : "OrderBy";
    var propertyInfo = entityType.GetProperty(orderName);
    var enumarableType = typeof(System.Linq.Queryable);
    var method = enumarableType.GetMethods()
         .Where(m => m.Name == direction && m.IsGenericMethodDefinition)
         .Where(m =>
         {
            var parameters = m.GetParameters().ToList();
            return parameters.Count == 2;
         }).Single();
    
    var genericMethod = method
         .MakeGenericMethod(entityType, propertyInfo.PropertyType);

    // OrderBy または OrderByDescending メソッドを実行
    var newQuery = (IOrderedQueryable<User>)genericMethod
         .Invoke(genericMethod, new object[] { query, selector });

    return newQuery.Skip(offset).Take(count);
}

version: '3.9'
services:
  db:
    image: mcr.microsoft.com/mssql/server:2019-latest
    container_name: ef_sample_db
    restart: always
    ports:
      - 1433:1433
    environment:
      ACCEPT_EULA: Y
      MSSQL_SA_PASSWORD: saPassword1234
      MSSQL_PID: Express
      MSSQL_LCID: 1041
      MSSQL_COLLATION: Japanese_CI_AS
    volumes:
      - .\db\data:/var/opt/mssql/data
      - .\db\log:/var/opt/mssql/log
      - .\db\secrets:/var/opt/mssql/secrets
  ef:
    image: mcr.microsoft.com/dotnet/sdk:6.0
    container_name: ef_sample_dev
    restart: always
    volumes:
      - .\app:/usr/local/src/work
    tty: true
    depends_on:
      - db

docker-compose up -d

docker ps -a

-- CREATE DATABASE
CREATE DATABASE sample_db collate Japanese_CI_AS;
GO

-- CREATE LOGIN  
CREATE LOGIN usr WITH PASSWORD = 'usrPassword1234';  
GO  

USE sample_db;

-- CREATE USER  
CREATE USER usr FOR LOGIN usr;  
GO

-- Authentication
EXEC sp_addrolemember 'db_datareader', 'usr';
GO
EXEC sp_addrolemember 'db_datawriter', 'usr';
GO

-- CREATE TABLE
CREATE TABLE sample_db.dbo.users
(
    id INT NOT NULL IDENTITY PRIMARY KEY,
    name NVARCHAR(50) NOT NULL,
    age INT,
    delete_flag BIT NOT NULL DEFAULT 0
);
GO

-- Sample data
INSERT INTO sample_db.dbo.users
    (name, age)
VALUES
    ('伊藤博文', 20),
    ('黒田清隆', 32),
    ('山縣有朋', 18),
    ('松方正義', 46),
    ('大隈重信', 9),
    ('桂太郎', 12),
    ('西園寺公望', 51);

dotnet new console --name EfSampleApp

cd /usr/local/src/work/EfSampleApp
dotnet add package Microsoft.EntityFrameworkCore.SqlServer, Microsoft.EntityFrameworkCore.Design

dotnet tool install --global dotnet-ef
echo 'export PATH=$PATH:'/root/.dotnet/tools >> ~/.bashrc
source ~/.bashrc

cd /usr/local/src/work/EfSampleApp
dotnet ef dbcontext scaffold "Data Source=db,1433;Initial Catalog=sample_db;User ID=usr;Password=usrPassword1234;TrustServerCertificate=true;" Microsoft.EntityFrameworkCore.SqlServer -t users -f -o Models

using EfSampleApp.Models;

var dbContext = new SampleDbContext();
var query = dbContext.Users.OrderBy(u => u.Age).Skip(3).Take(3);
foreach (var user in query)
{
    Console.WriteLine($"id[{user.Id}],name[{user.Name}],age[{user.Age}]");
}
Console.WriteLine("===================");

var query = dbContext.Users.OrderBy(u => u.Age).Skip(3).Take(3);

cd /usr/local/src/work/EfSampleApp 
dotnet run

using System;
using System.Collections.Generic;
using Microsoft.EntityFrameworkCore;
// ===== 追加(ここから) =====
using Microsoft.Extensions.Logging;
using Microsoft.EntityFrameworkCore.Diagnostics;
// ===== 追加(ここまで) =====

namespace EfSampleApp.Models;

public partial class SampleDbContext : DbContext
{
    public SampleDbContext()
    {
    }

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

    public virtual DbSet<User> Users { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
#warning To protect potentially sensitive information in your connection string, you should move it out of source code. You can avoid scaffolding the connection string by using the Name= syntax to read it from configuration - see https://go.microsoft.com/fwlink/?linkid=2131148. For more guidance on storing connection strings, see http://go.microsoft.com/fwlink/?LinkId=723263.
        => optionsBuilder.UseSqlServer("Data Source=db,1433;Initial Catalog=sample_db;User ID=usr;Password=usrPassword1234;TrustServerCertificate=true;")
            // ===== 追加(ここから) =====
            .LogTo(
                    message => Console.WriteLine(message),
                    new[] { DbLoggerCategory.Database.Name },
                    LogLevel.Information, 
                    DbContextLoggerOptions.LocalTime);
            // ===== 追加(ここまで) =====

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<User>(entity =>
        {
            entity.HasKey(e => e.Id).HasName("PK__users__3213E83F9AB61C6F");

            entity.ToTable("users");

            entity.Property(e => e.Id).HasColumnName("id");
            entity.Property(e => e.Age).HasColumnName("age");
            entity.Property(e => e.DeleteFlag).HasColumnName("delete_flag");
            entity.Property(e => e.Name)
                .HasMaxLength(50)
                .HasColumnName("name");
        });

        OnModelCreatingPartial(modelBuilder);
    }

    partial void OnModelCreatingPartial(ModelBuilder modelBuilder);
}

using EfSampleApp.Models;
// ===== 追加(ここから) =====
using System.Linq;
using System.Linq.Expressions;

static IEnumerable<User> Select(SampleDbContext context, int offset, int count, string orderName, string orderDirection)
{
    var entityType = typeof(User);
    var query = context.Users
        .Where(user => !user.DeleteFlag);

    // 「user => user.Age」という式ツリーを生成
    var arg = Expression.Parameter(entityType, "user");
    var property = Expression.Property(arg, orderName);
    var selector = Expression.Lambda(property, new ParameterExpression[] { arg });

    // System.Linq.Queryable.OrderBy または OrderByDescending メソッドを
    // リフレクションで取得
    var direction = orderDirection != "asc" ? "OrderByDescending" : "OrderBy";
    var propertyInfo = entityType.GetProperty(orderName);
    var enumarableType = typeof(System.Linq.Queryable);
    var method = enumarableType.GetMethods()
         .Where(m => m.Name == direction && m.IsGenericMethodDefinition)
         .Where(m =>
         {
            var parameters = m.GetParameters().ToList();
            return parameters.Count == 2;
         }).Single();
    
    var genericMethod = method
         .MakeGenericMethod(entityType, propertyInfo.PropertyType);

    // OrderBy または OrderByDescending メソッドを実行
    var newQuery = (IOrderedQueryable<User>)genericMethod
         .Invoke(genericMethod, new object[] { query, selector });

    return newQuery.Skip(offset).Take(count);
}
// ===== 追加(ここまで) =====

var dbContext = new SampleDbContext();
var query = Select(dbContext, 3, 3, nameof(User.Age), "asc"); // 変更
foreach (var user in query)
{
    Console.WriteLine($"id[{user.Id}],name[{user.Name}],age[{user.Age}]");
}
Console.WriteLine("===================");