Selecting distinct entity values based on string field name

Question

Is it possible to select distinct property values of an entity where the column name is not known in advance - received by the method as a string from the client.

For filtering purposes I want to make a HTTP POST Ajax request to the server from the browser which will contain the field name as a key, and this will be available to the server as a string.

I know I am going to have to manually map any differences between the ViewModel and the POCO class used by Entity Framework, but is it possible to construct an Entity framework query without the strongly typed property - by using reflection for example.

I'd probably try to implement this where the data was determined by controller, calling a generic method of the base repository with the entity class as the type. Is this possible or do I need to construct a method for each possible field?

Equally, should I be attempting to do this, or instead constructing ADO.NET SQL commands with the field as a parameter (mapping from ViewModel to SQL column names)?

Pinkston

The old good Dynamic LINQ should still work with EF giving you what you want. – Billingsgate

Similar question here: https://mcmap.net/q/276886/-linq-dynamic-select – Knutson

Answer 1

I'm pretty sure I saw a similar answer recently but I can't find it...

If you know the type of your entity and the type of the property you're going to be selecting, this is pretty easy:

public IQueryable<TProperty> SelectProperty<TEntity, TProperty>(DbContext context, string propertyName)
    where TEntity : class
{
    var parameter = Expression.Parameter(typeof(TEntity));
    var body = Expression.Property(parameter, propertyName);
    var lambda = Expression.Lambda<Func<TEntity, TProperty>>(body, parameter);
    var result = context.Set<TEntity>().Select (lambda).Distinct();

    return result;
}

If you can't predict the type of the property then building the expression will be more difficult:

public IQueryable SelectProperty<TEntity>(DbContext context, string propertyName)
    where TEntity : class
{
    var entities = context.Set<TEntity>();
    var query = entities.AsQueryable();
    var parameter = Expression.Parameter(typeof(TEntity), "instance");
    var propertyAccess = Expression.Property(parameter, propertyName);
    var projection = Expression.Lambda(propertyAccess, parameter);

    var selectExpression = Expression.Call(
        typeof(Queryable).GetMethods()
                         .First (x => x.Name == "Select")
                         .MakeGenericMethod(new[]{ typeof(TEntity), propertyAccess.Type }),
        query.Expression,
        projection);

    var distinctExpression = Expression.Call(
        typeof(Queryable).GetMethods()
                         .First (x => x.Name == "Distinct")
                         .MakeGenericMethod(new[]{ propertyAccess.Type }),
        selectExpression);

    var result = query.Provider.CreateQuery(distinctExpression);

    return result;
}

Permutation

Answer 2

You can use Dyanmic Linq (nuget package: System.Linq.Dynamic) to do this, along with an extension method which I will not even try to take credit for, found here

Sample code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Dynamic;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;

namespace dlinq1
{
    class Thing
    {
        public int ID { get; set; }
    }
    class Program
    {
        static void Main(string[] args)
        {
            var things = new List<Thing>();
            for(int x=0;x<10;x++)
            {
                things.Add(new Thing{ID=x%2}); //0,1,0,1,0,1,0,1,0,1
            }
            var result = things.AsQueryable().Select("ID").Distinct();
            foreach (var r in result)
            {
                Console.WriteLine(r);
            }
            Console.ReadLine(); //produces 0, 1
        }
    }

    public static class DynamicQueryableExtras
    {
        public static IQueryable Distinct(this IQueryable q)
        {
            var call = Expression.Call(typeof(Queryable),
                                       "Distinct",
                                       new Type[] { q.ElementType },
                                       q.Expression);
            return q.Provider.CreateQuery(call);
        }
    }
}

Basically the extension works with whatever state the queryable is in since the previous expression just before you called Distinct.

Rhabdomancy