c# - 使用动态列名从 Entity Framework 中选择不同的列

Question

我正在寻找一种方法来为我的表的一列获取不同值的列表。我需要制作一个可重用的方法。

到目前为止，这是我尝试过的方法，但它不起作用:

IEnumerable<string> GetDistinctValues<T>(string columnName)
{
    T.Select(m => m.ColumnName).Distinct().ToList();
}

所需的解决方案应该是 EF 对象的扩展方法。

我试过这篇文章 Dynamically select columns in runtime using entity framework但它仅适用于单个记录而不适用于列表。

Answer 1

我看到 Linq.Dynamic 的唯一问题是自 2013 年以来没有更新，而且该项目几乎已经死了

我会通过扩展来处理它，并通过缓存来提高反射性能(这里不详细说明)

扩展:

public static class QueryableExtensions
{
    public static IReadOnlyCollection<TResult> GetDistinctValuesForProperty<T, TResult>(this IQueryable<T> query, Expression<Func<T, TResult>> propertyAccess)
    {
        return SelectDistinct(query, propertyAccess).ToList();
    }

    public static IReadOnlyCollection<object> GetDistinctValuesForProperty<TSource>(this IQueryable<TSource> query, string propertyName)
    {
        var unboundFuncType = typeof(Func<,>);
        var unboundExprType = typeof(Expression<>);

        var sourceType = typeof(TSource); // TSource

        var resultType = typeof(TSource)
            .GetProperty(propertyName)
            .PropertyType; // TResult

        // Func<TSource, TResult>
        var funcType = unboundFuncType.MakeGenericType(new [] { sourceType, resultType });

        // Expression<Func<TSource, TResult>>
        var expressionType = unboundExprType.MakeGenericType(new [] { funcType });

        // Instance of Expression<Func<TSource, TResult>>, for example x => x.Name
        var propertyAccess = typeof(StringExtensions)
            .GetMethod(nameof(StringExtensions.AsPropertyExpression), new[] { typeof(string) })
            .MakeGenericMethod(new [] { sourceType, resultType })
            .Invoke(null, new object[] { propertyName });

        // SelectDistinct query transform
        var selectDistinctMethod = typeof(QueryableExtensions)
            .GetMethod(nameof(QueryableExtensions.SelectDistinct), BindingFlags.NonPublic | BindingFlags.Static)
            .MakeGenericMethod(new [] { sourceType, resultType });

        // IQueryable<TSource> ==> IQueryable<TResult>
        var result = selectDistinctMethod.Invoke(null, new object[] { query, propertyAccess });

        // Cast to object via IEnumerable and convert to list
        return ((IEnumerable)result).Cast<object>().ToList();
    }

    private static IQueryable<TResult> SelectDistinct<TSource, TResult>(this IQueryable<TSource> query, Expression<Func<TSource, TResult>> propertyAccess)
    {
        return query.Select(propertyAccess).Distinct();
    }
}

public static class StringExtensions
{
    public static Expression<Func<T, TResult>> AsPropertyExpression<T, TResult>(this string propertyName)
    {
        var parameter = Expression.Parameter(typeof(T), "x");
        var property = typeof(T).GetProperty(propertyName);
        var body = Expression.MakeMemberAccess(parameter, property);
        return Expression.Lambda<Func<T, TResult>>(body, parameter);
    }
}

用法:

public class Person
{
    public string Name { get; }
    public int Age { get; }

    public Person(string name, int age)
    {
        Name = name;
        Age = age;
    }
}

var people = new Person[]
{
    new Person("John", 25), new Person("Peter", 25), new Person("Sean", 25),
    new Person("John", 32), new Person("Peter", 32),
};

var query = people.AsQueryable();

var namePropertyExpression = "Name".AsPropertyExpression<Person, string>();
var agePropertyExpression = "Age".AsPropertyExpression<Person, int>();

// When you know the result type
var names1 = query.GetDistinctValuesForProperty(x => x.Name);
var ages1 = query.GetDistinctValuesForProperty(x => x.Age);

// When you know the result type, but you may want to reuse the property expression
var names2 = query.GetDistinctValuesForProperty(namePropertyExpression);
var ages2 = query.GetDistinctValuesForProperty(agePropertyExpression);

// When you just know the property name
var names3 = query.GetDistinctValuesForProperty("Name");
var ages3 = query.GetDistinctValuesForProperty("Age");