EF Core 6: generic method to select distinct fields from the list

Question

I need to write a generic method, which would return distinct values for two or more fields, given by the user in the List<string>.

With some help, I have the below, which gives "Linq cannot be translated" error.

public static Expression<Func<TSource, IDictionary<string, object>>> BuildSelectExpression<TSource>(List<string> fieldNames)
{
    var sourceType = typeof(TSource);
    var sourceParameter = Expression.Parameter(sourceType, "x");
    var dictionaryConstructor = typeof(Dictionary<string, object>).GetConstructor(Type.EmptyTypes);
    var dictionaryAddMethod = typeof(Dictionary<string, object>).GetMethod("Add");

    var elements = fieldNames.Select(fieldName =>
    {
        var property = sourceType.GetProperty(fieldName);
        if (property == null)
        {
            throw new ArgumentException($"Property '{fieldName}' does not exist on type '{sourceType}'.");
        }

        var key = Expression.Constant(fieldName);
        var value = Expression.Convert(Expression.Property(sourceParameter, property), typeof(object));
        var addMethodCall = Expression.Call(Expression.Variable(typeof(Dictionary<string, object>)), dictionaryAddMethod, key, value);
        return addMethodCall;
    }).ToArray();

    var body = Expression.Block(new[] { Expression.Variable(typeof(Dictionary<string, object>)) },
        Expression.Assign(Expression.Variable(typeof(Dictionary<string, object>)), Expression.New(dictionaryConstructor)),
        Expression.Block(elements),
        Expression.Variable(typeof(Dictionary<string, object>))
    );

    return Expression.Lambda<Func<TSource, IDictionary<string, object>>>(body, sourceParameter);
}

var selectExpression = BuildSelectExpression<MyEntityType>(fieldNames);

var distinctRecords = await _context.MyEntityDbSet
                     .Select(selectExpression)
                     .Distinct(new DictionaryComparer<string, object>())
                     .ToListAsync(cancellationToken);

And the DictionacyComparer method:

public class DictionaryComparer<TKey, TValue> : IEqualityComparer<IDictionary<TKey, TValue>>
{
    public bool Equals(IDictionary<TKey, TValue> x, IDictionary<TKey, TValue> y)
    {
        if (x == y) return true;
        if (x == null || y == null) return false;
        if (x.Count != y.Count) return false;

        return x.Keys.All(key => y.TryGetValue(key, out var value) && EqualityComparer<TValue>.Default.Equals(x[key], value));
    }

    public int GetHashCode(IDictionary<TKey, TValue> obj)
    {
        unchecked
        {
            int hash = 17;
            foreach (var entry in obj.OrderBy(e => e.Key.GetHashCode()))
            {
                hash = hash * 23 + entry.Key.GetHashCode();
                hash = hash * 23 + (entry.Value?.GetHashCode() ?? 0);
            }
            return hash;
        }
    }
}

Edit:

When I know the fields, let's say "customer_id", "customer_name" I can simply write:

dbSet
   .Select(x=> new {x.customer_id, x.customer_name})
   .Distinct()
   .ToListAsync()

The challenge is when the list of field names comes as input.

Answer 1

This is implementation of selecting Distinct values. One limitation, properties which are examined for uniqueness should be writable (with setter).

Usage is simple:

var result = await dbSet
    .SelectDistinctValues(new List<string> { "customer_id", "customer_name" })
    .ToListAsync();

As a bonus complex properties are also supported:

var result = await dbSet
    .SelectDistinctValues(new List<string> { "customer.id", "customer.name" })
    .ToListAsync();

And implementation:

public static class QueryableExtensions
{
    static readonly ConstructorInfo _dictionaryConstructor = typeof(Dictionary<string, object>).GetConstructor(Type.EmptyTypes) ?? throw new InvalidOperationException();
    static readonly MethodInfo _dictionaryAddMethodInfo = typeof(Dictionary<string, object>).GetMethod("Add") ?? throw new InvalidOperationException();

    public static IQueryable<IDictionary<string, object>> SelectDistinctValues<TSource>(this IQueryable<TSource> source,
        List<string> fieldNames)
    {
        var fields = fieldNames.Select(f => f.Split('.')).ToList();

        var param = Expression.Parameter(typeof(TSource), "e");

        var selectorBody = BuildSelectExpression(param, fields, 0);
        var selector = Expression.Lambda<Func<TSource, TSource>>(selectorBody, param);

        var dictionarySelectorBody = BuildDictionarySelectExpression(param, fields);
        var dictionarySelector = Expression.Lambda<Func<TSource, IDictionary<string, object>>>(dictionarySelectorBody, param);

        var query = source
            .Select(selector)
            .Distinct()
            .Select(dictionarySelector);

        return query;
    }


    static Expression BuildDictionarySelectExpression(Expression objParam, List<string[]> fieldNames)
    {
        var body = (Expression)Expression.ListInit(
            Expression.New(_dictionaryConstructor),
            fieldNames.Select(propName =>
                Expression.ElementInit(_dictionaryAddMethodInfo, Expression.Constant(string.Join(".", propName)),
                    Expression.Convert(MakePropPath(objParam, propName), typeof(object)))
        ));

        return body;
    }


    static Expression BuildSelectExpression(Expression objPath, List<string[]> fieldNames, int level)
    {
        if (fieldNames.Count == 0)
            throw new InvalidOperationException();

        var objType = objPath.Type;

        var grouped = fieldNames.GroupBy(path => path[level])
            .Select(x => new
            {
                PropInfo = objType.GetProperty(x.Key) ?? throw new InvalidOperationException(),
                NexPath = x.Where(x => x.Length > level + 1).ToList()
            }).OrderBy(x => x.NexPath.Count > 0);

        var assignments = grouped.Select(x =>
        {
            var access = Expression.MakeMemberAccess(objPath, x.PropInfo);
            return Expression.Bind(x.PropInfo,
                x.NexPath.Count == 0
                    ? access
                    : BuildSelectExpression(access, x.NexPath, level + 1));
        });

        var newExpr = Expression.MemberInit(
            Expression.New(objType),
            assignments
        );

        return newExpr;
    }       

    static Expression MakePropPath(Expression objExpression, string[] path)
    {
        return path.Aggregate(objExpression, Expression.PropertyOrField);
    }
}