c# - 使用 C# Linq 表达式自定义排序

Question

我正在努力简化 C# 应用程序和 C# Web API 的排序。我正在使用 Entity Framework Core 进行持久性和测试。

在我的应用程序或 Web API 中，我确定顺序，降序或升序，属性名称。

我将这些知识传递到我的存储库中，在该存储库中创建并执行了 Linq 查询。问题是当我有一个小数列时，它执行的是字符串顺序而不是十进制顺序。

public static class SortingExtensions
{
    public static IQueryable<T> SortBy<T>(
        this IQueryable<T> queryable,
        Sorting sorting)
    {
        IOrderedQueryable<T> orderedQueryable = null;

        sorting.SortableEntities
            .OrderBy(x => x.Order)
            .ToList()
            .ForEach(sortableEntity =>
            {
                Expression<Func<T, object>> expression = QueryHelper.GetDefaultSortExpression<T>(sortableEntity);
                if (expression != null)
                {
                    orderedQueryable = orderedQueryable == null
                        ? queryable.OrderBy(expression, sortableEntity.Descending)
                        : orderedQueryable.OrderBy(expression, sortableEntity.Descending);
                }
            });

        return orderedQueryable;
    }

    private static IOrderedQueryable<T> OrderBy<T, TKey>(
        this IOrderedQueryable<T> query,
        Expression<Func<T, TKey>> keySelector,
        bool descending) => descending ? query.ThenByDescending(keySelector) : query.ThenBy(keySelector);

    private static IOrderedQueryable<T> OrderBy<T, TKey>(
        this IQueryable<T> query,
        Expression<Func<T, TKey>> keySelector,
        bool descending) => descending ? query.OrderByDescending(keySelector) : query.OrderBy(keySelector);
}

public static class QueryHelper
{
    public static Expression<Func<T, object>> GetDefaultSortExpression<T>(SortableEntity sortableEntity)
    {
        Type entityType = typeof(T);
        ParameterExpression arg = Expression.Parameter(entityType, "x");

        string[] fieldNames = sortableEntity.Name.Split('.');
        MemberExpression memberExpression = null;
        foreach (string name in fieldNames)
        {
            Expression expressionToUse = memberExpression ?? (Expression) arg;
            memberExpression = Expression.Property(expressionToUse, name.ToProperCase());
        }

        Expression propertyExpression = Expression.Convert(memberExpression, typeof(object));
        Expression<Func<T, object>>
            complexExpression = Expression.Lambda<Func<T, object>>(propertyExpression, arg);
        return complexExpression;
    }
}

public class SortableEntity
{
    public int Order { get; set; }
    public bool Descending { get; set; }
    public string Name { get; set; }
}

public class Sorting
{
    IEnumerable<SortableEntity> SortableEntities { get; }
}

public class TestDecimalPropertyClass : Entity
{
    public TestDecimalPropertyClass(decimal @decimal) => Decimal = @decimal;

    protected TestDecimalPropertyClass()
    {
    }

    public decimal Decimal { get; set; }
}

public class TestDecimalPropertyClassRepository
{
    private readonly DbContext _dbContext;

    public TestDecimalPropertyClassRepository(DbContext dbContext)
    {
        _dbContext = dbContext;
    }

    public async Task<IEnumerable<TestDecimalPropertyClass>> GetAllAsync(Sorting sorting)
    {
        List<TestDecimalPropertyClass> entities = await _dbContext.Set<TestDecimalPropertyClass>()
            .SortBy(sorting)
            .ToListAsync();

        return entities;
    }

    public async Task SaveAsync(TestDecimalPropertyClass testDecimalPropertyClass)
    {
        _dbContext.Set<TestDecimalPropertyClass>().Add(testDecimalPropertyClass);
        await _dbContext.SaveChangesAsync();
    }
}

这是我为它写的一个测试:

[TestFixture]
public class GenericSortingTests
{
    private SqliteConnection SqliteConnection { get; set; }

    [SetUp]
    public void DbSetup()
    {
        SqliteConnectionStringBuilder sqliteConnectionStringBuilder = new SqliteConnectionStringBuilder
        {
            Mode = SqliteOpenMode.Memory,
            Cache = SqliteCacheMode.Private
        };
        SqliteConnection = new SqliteConnection(sqliteConnectionStringBuilder.ToString());
        SqliteConnection.Open();
    }

    [TearDown]
    public void DbTearDown()
    {
        SqliteConnection.Close();
    }
    [Test]
    public async Task GivenADecimalProperty_WhenISortByColumn_ThenItSorts()
    {
        decimal[] decimals = new[] {7m, 84.3m, 13.4m};

        using (DbContext dbContext = GetDbContext())
        {
            TestDecimalPropertyClassRepository testRepository = new TestDecimalPropertyClassRepository(dbContext);

            foreach (decimal @decimal in decimals)
            {
                TestDecimalPropertyClass entity = new TestDecimalPropertyClass(@decimal);
                await testRepository.SaveAsync(entity);
            }
        }

        IEnumerable<TestDecimalPropertyClass> entities;
        using (DbContext dbContext = GetDbContext())
        {
            TestDecimalPropertyClassRepository testRepository = new TestDecimalPropertyClassRepository(dbContext);

            entities = await testRepository.GetAllAsync(new Sorting
            {
                SortableEntities = new[]
                {
                    new SortableEntity
                    {
                        Descending = false,
                        Name = "decimal",
                        Order = 0
                    }
                }
            });
        }

        List<TestDecimalPropertyClass> list = entities.ToList();
        Assert.That(list.Count(), Is.EqualTo(decimals.Length));
        Assert.That(list.ToArray()[0].Decimal, Is.EqualTo(7m));
        Assert.That(list.ToArray()[1].Decimal, Is.EqualTo(13.4m));
        Assert.That(list.ToArray()[2].Decimal, Is.EqualTo(84.3m));
    }

    private class TestDbContext : DbContext
    {
        public TestDbContext(DbContextOptions options) : base(options)
        {
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<TestDecimalPropertyClass>();
            base.OnModelCreating(modelBuilder);
        }
    }

    private DbContext GetDbContext()
    {
        DbContextOptions<TestDbContext> options = new DbContextOptionsBuilder<TestDbContext>()
            .UseSqlite(SqliteConnection)
            .EnableSensitiveDataLogging()
            .Options;

        TestDbContext dbContext = new TestDbContext(options);
        dbContext.Database.EnsureCreated();
        return dbContext;
    }
}

我希望它按顺序对项目进行排序:7m、13.4m、84.3m，但它却将其排序为 13.4m、7m、84.3m

任何人都可以帮助我理解为什么会这样，以便我可以修复它吗？

谢谢，克里斯

Answer 1

首先，我以前曾尝试自己重新发明轮子，但它从来没有像您想要的那样真正有效。如果您需要那种动态的灵 active ，那么要么某处可能已经有一个库，要么您也可以放弃实际手动制作 SQL 或其他东西(这很糟糕，但有时它是唯一实用的方法).. 除此之外...

我认为您的问题实际上与 SQLite 有关 - 由于拼写错误或版本不相同，我无法让 SQLite 工作(例如，SQLite 的默认 nuget 包有一个SQLiteConnectionStringBuilder 而不是 SqliteConnectionStringBuilder，并且这似乎与您的示例不具有相同的属性)所以我对您的代码进行了一些修改以删除 SQL 内容并摆脱异步的东西(我希望那真的不相关)，所以我有这个存储库:

public class TestDecimalPropertyClassRepository
{
    private readonly IList<TestDecimalPropertyClass> list;

    public TestDecimalPropertyClassRepository(IEnumerable<TestDecimalPropertyClass> repo)
    {
        list = repo.ToList();
    }

    public IEnumerable<TestDecimalPropertyClass> GetAll(Sorting sorting)
    {
        List<TestDecimalPropertyClass> entities = list
            .AsQueryable()
            .SortBy(sorting)
            .ToList();

        return entities;
    }

    public void Save(TestDecimalPropertyClass testDecimalPropertyClass)
    {
        list.Add(testDecimalPropertyClass);            

    }
}

这使得测试看起来像这样

[Test]
public void GivenADecimalProperty_WhenISortByColumn_ThenItSorts()
{
    decimal[] decimals = new[] { 7m, 84.3m, 13.4m };
    var repo = decimals.Select(x => new TestDecimalPropertyClass(x));

    TestDecimalPropertyClassRepository testRepository = new TestDecimalPropertyClassRepository(repo);

    var entities = testRepository.GetAll(new Sorting
    {
        SortableEntities = new[]
            {
            new SortableEntity
            {
                Descending = false,
                Name = "decimal",
                Order = 0
            }
        }
    });

    List<TestDecimalPropertyClass> list = entities.ToList();
    Assert.That(list.Count(), Is.EqualTo(decimals.Length));
    Assert.That(list.ToArray()[0].Decimal, Is.EqualTo(7m));
    Assert.That(list.ToArray()[1].Decimal, Is.EqualTo(13.4m));
    Assert.That(list.ToArray()[2].Decimal, Is.EqualTo(84.3m));
}

并且让所有的扩展内容保持不变，所以它仍然以相同的方式反射等等。

这个测试顺利通过。现在，这并不完全有效，因为它当然不再完全相同，但在我看来这确实意味着它可能不是框架错误解释了 decimal 属性的类型，或者一些与装箱/拆箱相关的某种混淆意味着它无法计算出类型并执行 .ToString() 进行比较。

假设 SQLite EF 提供程序正确地将其转换为 SQL ORDER BY 子句，您是否检查过此 SQL？过去我做过类似的事情(使用 SQLite 编写测试)，发现它在某些晦涩的方面不如 SQL Server 或类似的东西那么完整。也许提供者有一个错误，或者在生成的表达式树中有一个它不能很好理解的怪癖。

所以我会先研究那个​​而不是你写的代码..