LINQ, get string list using dynamic field name

Question

So I want to return a List of type String of unique values from a table.

Remarkably, the designers of this table decided that each row in the table has multiple columns with multiple unique fields. So you can't just get a unique list of items from the table you need to specify the field and then get distinct.

So to that end, I want to write a generic method where I can specify the column name and get the unique list of items.

I have tried many approaches two are listed below;

retList = context.LP_Specification.Select(x => x.GetType().GetProperty(fieldName).GetValue(x).ToString()).Distinct().ToList();

retList = context.LP_Specification.Select(fieldName)

also doesn't work.

However I get an error using reflection like this.

So the method call looks like this;

public List<string> GetSpecs(string fieldName)

And I want to get a list of string values from the table and only return the distinct values of the field specified.

Something like this http://stackoverflow.com/questions/39223586/dynamic-linq-query-with-multiple-select-values/39354675#39354675? But your case might be simpler if you always select a `string` column, do you? — Ivan Stoev, Sep 13 '16 at 07:10
Select(s => s.GetType().GetProperty(Property.Name).GetValue(s)) (with no ToString() worked for me earlier today... Hope that helps (just my $0.02 which, when added to two cents produces about $0.015)... specifically, it was Select(s =>(double) s.GetType().GetProperty("Customers").GetValue(s)).ToArray(); — Shannon Holsinger, Sep 13 '16 at 07:12
"also doesn't work. However I get an error using reflection like this." Be more specific! What erros *do* you get? What do you expect? I think we can expect this basic information from a 15k-user, can´t we? — MakePeaceGreatAgain, Sep 13 '16 at 07:19

Zein Makki · Accepted Answer · 2016-09-13T09:28:48.243

You don't need to get the property value of each object using reflection, because this query is going to be executed against the database so that approach is not going to work (unless in the case of querying in-memory collections).

You need to build a dynamic expression tree to acheive what you want. A simple example is this:

// Building expression x=> x.FieldName
ParameterExpression foo = Expression.Parameter(typeof(Foo), "x");
MemberExpression selection = Expression.PropertyOrField(foo, "FieldName");
var lambdaExp = Expression.Lambda<Func<Foo, string>>(selection, foo);

Usage:

retList = context.LP_Specification.Select(lambdaExp).Distinct();

The above assume the entity type is Foo and the Property is called "FieldName".

You can always create an extension method out of this:

public static class MyExtensions
{
    public static IQueryable<V> SelectByName<T, V>(this IQueryable<T> source, 
                                                        string FieldName)
    {
        ParameterExpression paramExp = Expression.Parameter(typeof(T), "x");
        MemberExpression memberExp = Expression.PropertyOrField(paramExp, FieldName);
        var lambdaExp = Expression.Lambda<Func<T, V>>(memberExp, paramExp);

        return source.Select(lambdaExp);
    }
}

Usage:

retList = context.LP_Specification
                 .SelectByName<LP_Specification, string>("SomeFieldName").Distinct();

@shA.t The user states that she's looking for string properties only. — Zein Makki, Sep 13 '16 at 09:27
Thanks that's working great. But I need to two columns ! What do I need to do ? — Milind Thakkar, Oct 14 '18 at 17:43

score 1 · Answer 2 · answered Sep 13 '16 at 11:11

I think you can use a generic method like this:

private IEnumerable<string> GetResult<T>(IEnumerable<T> list, string propName)
{
    var retList = new List<string>();

    var prop = typeof (T).GetProperty(propName);
    if (prop == null)
        throw new Exception("Property not found");

    retList = list.Select(c => prop.GetValue(c).ToString()).ToList();
    return retList;
}

And call it like this:

var result = GetResult(context.LP_Specification, "FieldName");

score 0 · Answer 3 · answered Sep 13 '16 at 09:42

If the number of columns is basically static, you should be able to try this. This removes the need for reflection and complex expression trees;

// this will be used in all querys; add 'where' clauses etc if you want
IQueryable<Specification> rootQuery = from s in specification select s;

IQueryable<string> names = null;
switch(fieldName)
{
    case "field1": names = rootQuery.Select(r => r.field1); break;
    case "field2": names = rootQuery.Select(r => r.field2); break;
    case "field3": names = rootQuery.Select(r => r.field3); break;
    default: throw new ArgumentOutOfRangeException("Unknown field: " + fieldName);
}

var strings = names.Distinct().ToList();

LINQ, get string list using dynamic field name

3 Answers3