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().

It's inefficient because you fetch everything and process it in-memory.... This thread may be helpful: https://stackoverflow.com/questions/32619338/ef-distinctby-on-an-iqueryable — Alexandru Clonțea, Feb 01 '21 at 13:41
Hi Alexandru, the question below answered my problem, so I'll trouble you no more. My production code was running on DbSet.FromSQLRaw(), I was trying to get away from the raw SQL. — MrC aka Shaun Curtis, Feb 02 '21 at 16:00

score 1 · Accepted Answer · answered Feb 01 '21 at 20:22

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>();
    }

Thanks, that works great. I couldn't see the wood for the trees! — MrC aka Shaun Curtis, Feb 02 '21 at 16:02
Hi @ShaunCurtis if this or any answer has solved your question please consider [accepting it](https://meta.stackexchange.com/q/5234/179419) by clicking the check-mark. This indicates to the wider community that you've found a solution and gives some reputation to both the answerer and yourself. There is no obligation to do this. — Lacutah, Feb 02 '21 at 19:36
Done. You learn some thing new ever day! FYI the code is part of a generics based Data Access Layer Service for Blazor. `GetDbSet` just gets the correct DbSet from the EF DbContext. — MrC aka Shaun Curtis, Feb 03 '21 at 09:56

Using Distinct in generic DbSet queries

1 Answers1