EF Distinct on IQueryable doesn't remove duplicates

Question

I have a generic class that is supposed to filter, order, project and paginate an IQueryable by calling this method:

public async Task<PagedResult<TResult>> GetFilteredOrderedPageAsync<TResult>(IQueryable<TSource> source,
            IFilterModel filterModel,
            ISortModel sortModel,
            int page, int pageSize,
            Expression<Func<TSource, TResult>> converter)
            where TResult : class 
        {
            var filtered = Filter(source, filterModel);

            var projected = filtered
                .Select(converter)
                .Distinct();

            var ordered = Sort<TResult>(projected, sortModel);

            var result = await GetPageAsync<TResult>(ordered, page, pageSize, converter);

            return result;
        }

I am calling Distinct() here

        var projected = filtered
            .Select(converter)
            .Distinct();

to remove any duplicates that may appear after projection.

My assumption was that EF6 would generate something like

SELECT DISTINCT Col1, Col2, Col3 FROM (SELECT Col1, Col2, Col3, Col4 FROM SOME_TABLE WHERE <some conditions on co1, col2, col3, col4 ...>)

i.e. that it would apply DISTINCT to the projection, so, if there are two rows with same col1, col2, col3 but different col4, only one row would make it into the result. However, the SQL that I get looks like this:

SELECT Col1, Col2, Col3, Col4 FROM (SELECT DISTINCT Col1, Col2, Col3, Col4 FROM SOME_TABLE)

- no projection, and DISTINCT is shifted into a subquery, as if I were doing this:

var projected = filtered                    
                .Distinct()
                .Select(converter) 

I want this service to be a generic, i.e. potentially working with any TSource and TResult, but looks like there's some pitfall here and my understanding of what EF does is not correct.

What's going on here?

UPDATE

I believe the problem is in my converter. I used the following function to generate the lambda expression passed to Select:

public class ProvidersViewModel
{
    public string Name { get; set; }
    public Rate Rate { get; set; } 
    publi QA QA { get; set; }
    ...
    public static Expression<Func<ProviderJoinRateAndQA, ProvidersViewModel>> FromProvider(bool showRateAndQA) 
    {
       return x => new ProvidersViewModel {
        Name = x.Name, 
        Rate = showRateAndQA ? new Rate { Amount = x.Rate.Amount ... } : null,
        Rate = showRateAndQA ? new QA { Grade = x.QA.Grade ... } : null
    };
}

}

ProviderJoinRateAndQA is a join of providers, their rates and QAs. Each Provider can have multiple services, rates and qas. In some cases I want view model to hide the rate and QA info. I assumed I could do it by assigning a null to Rate and QA properties, but it doesn't seem to work: Distinct works incorrectly. After I replaced
Rate = showRateAndQA ? new QA { Grade = x.QA.Grade ... } : null with Rate = new QA { Grade = showRateAndQA ? x.QA.Grade : null ... },

Distinct worked correctly.

Apparently EF didn't like my assigning null to object in my lambda.

Answer 1

From memory, the default Distinct() implementation works by using the default Equals() method for the respective class, which is often unwanted (I've found this to be the case with my work atleast).

You need to set up something like the following;

public class ProvidersViewModel : IEqualityComparer<ProvidersViewModel>
{
    public bool Equals(ProvidersViewModel x, ProvidersViewModel y)
    {
        if (x.col1 == y.col1 && x.col2 == y.col2 && x.col3 == y.col3)
            return true;
        else
            return false;
    }

    public int GetHashCode(ProvidersViewModel obj)
    {
        int hCode = obj.col1 ^ obj.col2 ^ obj.col3;
        return hCode.GetHashCode();
    }

    // Existing code, fields, etc.
}

This should then allow the call to Distinct() to work properly.