I'm searching a way to get a list of distinct values, for one column of my table. I need to make a reusable method.
This is what I tried so far, but it doesn't work:
IEnumerable<string> GetDistinctValues<T>(string columnName)
{
T.Select(m => m.ColumnName).Distinct().ToList();
}
The desired solution should be an extension method for EF objects.
I've tried this post Dynamically select columns in runtime using entity framework but it works only for a single record not for a list.
The only problem I see with Linq.Dynamic is that there were no updates since 2013 and the project is pretty much dead
I would handle it via extensions, and improve reflection performance via caching (not elaborated here)
Extensions:
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);
}
}
Usage:
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");
Finally I found a solution. I need to include reference to System.Linq.Dynamic (downloaded by nuget), and use the "Select" method that accept String to reference column.
using System.Linq.Dynamic;
public static async Task<IEnumerable<Object>> GetDistinctValuesForProperty<T>(this IQueryable<T> query, String PropertyName)
{
return await query.Select(PropertyName).Distinct().ToListAsync();
}
and call as
String ColumnName = "DateTimeInsert";
DbSet<Log> oDbSet = _uow.DbContext.Set<Log>();
Array DistinctValues;
if (typeof(Log).GetProperty(ColumnName) != null)
{
DistinctValues = (await oDbSet.GetDistinctValuesForProperty(ColumnName)).ToArray();
}
else
{
DistinctValues = new object[0];
}
I need to use array vs ienumerable due to a cast problem in case of datetime types
You can create a generic selector method using Expressions
public static Func<T, T> SelectorFunc<T>(string[] columns) {
// input parameter "o"
var xParameter = Expression.Parameter(typeof(T), "o");
// new statement "new Data()"
var xNew = Expression.New(typeof(T));
// create initializers
var bindings = columns.Select(o => o.Trim())
.Select(o =>
{
// property "Field1"
var mi = typeof(T).GetProperty(o);
// original value "o.Field1"
var xOriginal = Expression.Property(xParameter, mi);
// set value "Field1 = o.Field1"
return Expression.Bind(mi, xOriginal);
}
);
// initialization "new Data { Field1 = o.Field1, Field2 = o.Field2 }"
var xInit = Expression.MemberInit(xNew, bindings);
// expression "o => new Data { Field1 = o.Field1, Field2 = o.Field2 }"
var lambda = Expression.Lambda<Func<T, T>>(xInit, xParameter);
// compile to Func<Data, Data>
return lambda.Compile();
}
using it would be
T.Select( SelectorFunc<T>( new string[]{ "Column" } ) ).Distinct().ToList();
You can also use it any other linq functions like
T.Select( SelectorFunc<T>( new string[]{ "Column" } ) ).Where();
T.Select( SelectorFunc<T>( new string[]{ "Column" } ) ).AsQueryable();
for additional reference you can see the full OP here LINQ : Dynamic select
