Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Entity Framework / Linq - Get distinct values of dynamically specified property

I have a requirement to get the list of distinct values for specific properties of a collection of entities.

So, let's say table A has fields x, y, z, 1, 2, 3 where x is the PK(thus off the table).

I need to get all the unique values for y, z, 1, 2, or 3, without necessarily knowing in my method which field I'm getting. So the pattern for the method would be:

public List<ObjectName> GetUniqueFieldValues(string fieldname)

The "ObjectName" object is an object with two properties, which the method above will fill at least one property per result.

Someone in another question had a similar answer using the ParameterExpression and Expression classes but didn't really provide enough information to help me with my specific task.

I also tried reflection but of course Linq does not like that very much within a Select expression.

I would just use if and call it good but there are really a ton of fields/properties in the actual table/object so it's impractical. This would also save me a little refactoring if the base table ever changes.

SQL version of what I'm trying to do:

SELECT Distinct [usersuppliedfieldname] from TableName where [someotherconditionsexist]

Pseudocode of what I already have:

public List<ReturnObject> GetUniqueFieldValues(int FkId, ConditionObject searchmeta)
{
    using(DbEntities db = new DbEntities())
    {
        // just getting the basic set of results, notice this is "Select *"
        var results = from f in db.Table
                      where f.FkId == FkId && [some static conditions]
                      select f;

        // filtering the initial results by some criteria in the "searchmeta" object
        results = ApplyMoreConditions(results, searchmeta);

        //  GOAL - Select and return only distinct field(s) specified in searchmeta.FieldName)

    }
}
like image 661
Matt Avatar asked Oct 22 '22 02:10

Matt


1 Answers

You might try something like this (similar to the post that was suggested as duplicate)

public static class DynamicQuerier
{
    private delegate IQueryable<TResult> QueryableMonad<TInput, TResult>(IQueryable<TInput> input, Expression<Func<TInput, TResult>> mapper);

    public static IQueryable<TResult> Select<TInput, TResult>(this IQueryable<TInput> input, string propertyName)
    {
        var property = typeof (TInput).GetProperty(propertyName);
        return CreateSelector<TInput, TResult>(input, property, Queryable.Select);
    }

    private static IQueryable<TResult> CreateSelector<TInput, TResult>(IQueryable<TInput> input, MemberInfo property, QueryableMonad<TInput, TResult> method)
    {
        var source = Expression.Parameter(typeof(TInput), "x");
        Expression propertyAccessor = Expression.MakeMemberAccess(source, property);
        var expression = Expression.Lambda<Func<TInput, TResult>>(propertyAccessor, source);
        return method(input, expression);
    }
}

For my test, I've created a dummy set of entities called Tests, below is the query to get the distinct values from the Property2

var values = context.Tests.Select<Test, int>("Property2").Distinct();
like image 79
mlorbetske Avatar answered Oct 24 '22 11:10

mlorbetske