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

Question

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)

    }
}

Queries like this tend to not fit will in LINQ to begin with. Just execute the query without using a query provider, or using a micro ORM if you prefer, and use EF when you have a query that is itself static, or dynamic in the ways that EF was designed to support. — Servy
– Servy, Commented Aug 19, 2013 at 16:40
@AlexPaven - That is a lot like the one I mentioned in my question that didn't work out for me. I'll edit the post with better detail of what I'm looking for. — Matt
– Matt, Commented Aug 19, 2013 at 17:26
You know... this may be overkill. The point is to do a metadata-based search and dynamically build option lists based on the fields that come back... If I'm already getting the search results I can probably do all this filtering on the client side and save myself a lot of trouble here. — Matt
– Matt, Commented Aug 19, 2013 at 17:54
Maybe this could help you. If not, show me an example of your table, and the output you want to get. — Amin Saqi
– Amin Saqi, Commented Aug 20, 2013 at 8:45

mlorbetske · Accepted Answer · 2013-08-19 20:31:53Z

3

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();

edited Aug 19, 2013 at 20:31

answered Aug 19, 2013 at 20:26

mlorbetske

5,6592 gold badges32 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Matt Over a year ago

What's the QueryableMonad delegate do? Also if you look at my pseudocode in the OP, you'll see I'm working with that "results" variable at the point when I want to get the distinct columns, and not the full table. Will this example work like that? I'll give it a shot anyway if I don't hear from you first.

mlorbetske Over a year ago

@Matt The QueryableMonad delegate is just shorthand for the full signature - instead of a Func<IEnumerable<TSource>, Expression<Func<TSource, TResult>>, IEnumerable<TResult>>. As long as the variable you're dealing with is still an IQueryable<T> you should be set, it'll build the query to have the database return the specific data requested instead of doing it in memory.

Matt Over a year ago

It looks like it should but I haven't really had the time to mess with it. I ditched the option to even do this in favor of something else because I'm on a time crunch. But after further evaluation I'll probably have to use it anyway, so I'll mark your answer for now and update when I have a chance to actually implement it.

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related