5

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)

    }
}
Matt
  • 1,213
  • 14
  • 39
  • See possible duplicate http://stackoverflow.com/a/3469744/430661 – Alex Paven Aug 19 '13 at 16:40
  • 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 Aug 19 '13 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 Aug 19 '13 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 Aug 19 '13 at 17:54
  • Maybe [this](http://stackoverflow.com/a/18239835/1814343) could help you. If not, show me an example of your table, and the output you want to get. – Amin Saqi Aug 20 '13 at 08:45
  • @AminSaghi Thanks, but it is not really helpful. The OP on that question had a completely different issue he wanted to solve. My table has over 20 columns and putting that up here would be a pain. I want to be able to select only one of those columns when provided the name of the column by means of a property in the "searchmeta" object. The overall function should return the distinct instances of the named column. – Matt Aug 20 '13 at 19:56

1 Answers1

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();
mlorbetske
  • 5,529
  • 2
  • 28
  • 40
  • 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. – Matt Aug 20 '13 at 19:58
  • @Matt The `QueryableMonad` delegate is just shorthand for the full signature - instead of a `Func, Expression>, IEnumerable>`. As long as the variable you're dealing with is still an `IQueryable` you should be set, it'll build the query to have the database return the specific data requested instead of doing it in memory. – mlorbetske Aug 20 '13 at 22:49
  • 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. – Matt Aug 27 '13 at 18:07