Using Distinct in generic DbSet queries

Question

I'm trying to run a Distinct query against an Entity Framework DbSet. The lists are used to populate Selects in the UI for filtering datasets of TRecord. The full method is listed below. fieldName is one of the fields in DbSet. The code below works but's inefficient. If you try to use Distinct() directly on the DbSet it doesn't do a distinct - just returns the full dataset. I'm assuming the problem lies in the way I'm using reflection to get the value. Is there a solution?

public async static Task<List<string>> GetDistinctListAsync<TRecord>(this DbContext context, string fieldName) where TRecord : class, IDbRecord<TRecord>, new()
{
     // Get the DbSet for TRecord
     var dbset = GetDbSet<TRecord>(context, null);
     // declare list as an empty list
     var list = new List<string>();
     // Get the filter propertyinfo object
     var x = typeof(TRecord).GetProperty(fieldName);
     if (dbset != null && x != null)
     {
          // we get the full list and then run a distinct because we can't run a distinct directly on the dbSet
          var fulllist = await dbset.Select(item => x.GetValue(item).ToString()).ToListAsync();
          list = fulllist.Distinct().ToList();
     }
     return list ?? new List<string>();
}

I'm reworking my older code that used a SQL Distinct Query called through DbSet.FromSQLRaw().

Answer 1

With the caveat that I don't know what magic GetDbSet<TRecord> does, here's a replacement function that'll take care of it, inspired by: Create generic selector for Select() while using Entity Framework

public static async Task<List<string>> GetDistinctListAsync<TRecord>(this DAL.DbContext context, string fieldName) where TRecord : class
    {
        var dbset = context.Set<TRecord>();

        // Return value.
        List<string> list = null;

        if (dbset != null) // Found matching dbSet
        {
            // Convert our property into a lambda expression.
            var arg = Expression.Parameter(typeof(TRecord), "current");
            var property = Expression.Property(arg, fieldName);
            var conv = Expression.Convert(property, typeof(object));
            var exp = Expression.Lambda<Func<TRecord, object>>(conv, new ParameterExpression[] { arg });

            list = 
                (await dbset.Select(exp).Distinct().ToArrayAsync()) // This runs against database / SQL
                .Select(x => x.ToString()).ToList(); // Convert return values into strings locally if necessary.
        }
        return list ?? new List<string>();
    }