Expression tree for having count() > 1

Question

I'm using EF 6.1 and I would like to query my entities using the following SQL

SELECT field, count(*) 
FROM entity
GROUP BY field
HAVING COUNT(*) > 1

Here both field and entity are variable. If both were known at compile time I could use Context.Set<Entity>().GroupBy(e => e.Field).Where(f => f.Count() > 1).Select(f => f.Key)

EDIT Forgot to mention that field is always of type string.

I think it is possible using an expression tree, but I'm not very familiar with that and the learning curve is a bit steep.

public Func<TSource, what's the return type?> CountMultiple<TSource>(string field)
        {
            var parameter = Expression.Parameter(typeof(TSource), "p");
            var property = Expression.Property(parameter, field);
.
Some more Expression magic goes here                
.

            return Expression.Lambda<Func<TSource, the return type>>(?, ?).Compile();
        }

Could someone point me in the right direction?

EDIT

To clarify; I'm looking for something like this (the below will check field in entity of type TSource for null)

public Func<TSource, bool> IsNull<TSource>(string field)
        {
            var parameter = Expression.Parameter(typeof(TSource), "p");
            var property = Expression.Property(parameter, field);
            return Expression.Lambda<Func<TSource, bool>>(
                Expression.Equal(property, Expression.Constant(null, property.Type)), new[] { parameter }).Compile();
        }

I can then use it as follows

context.Set<TEntity>()
                    .Where(e => !e.AMT_ValidationStatus.Equals(ValidationStatus.FAILED.ToString()))
                    .Where(IsNull<TEntity>(f.Name))

Does this help? http://stackoverflow.com/questions/2078736/linq-with-group-by-having-count — Corak, Jul 15 '16 at 12:00
No, it doesn't, this only works if you know the entity type and field name at compile time. I want to be able to do the same where these are known at runtime. — Hintham, Jul 15 '16 at 12:02
The whole idea of EF is that you do things that are typechecked at compile time. I don't think there is any way to do this when it doesn't typecheck - for example, using the name of a property as a string. Related and needed to answer the question: How are you planning to use the CountMultiple function? — Martijn, Jul 15 '16 at 12:05
That's exactly what I'm trying to avoid by using an expression tree instead of magic strings. — Hintham, Jul 15 '16 at 12:30
Hmm, avoid magic strings by using expression tree built with magic strings. — Ivan Stoev, Jul 15 '16 at 12:46

score 0 · Accepted Answer · answered Jul 15 '16 at 17:26

OK, figured it out

public static IQueryable<IGrouping<string, TSource>> Grouper<TSource>(IQueryable<TSource> source, string field)
        {
            var parameter = Expression.Parameter(typeof(TSource), "x");
            var property = Expression.Property(parameter, field);
            var grouper = Expression.Lambda<Func<TSource, string>>(property, parameter);

            return source.GroupBy(grouper);
        }

Which can be used as (f.Name is the name of the property in TEntity)

Grouper(context.Set<TEntity>(), f.Name)
                                .Where(field => field.Count() > 1)
                                .Select(s => new { Key = s.Key, Count = s.ToList().Count })

Expression tree for having count() > 1

1 Answers1