对IQueryable的EF区分不会删除重复项

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

我有一个泛型类,应该通过调用此方法来过滤,排序,投影和分页IQueryable:

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

我在这里打电话给Distinct()

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

to remove any duplicates that may appear after projection.

删除投影后可能出现的任何重复项。

My assumption was that EF6 would generate something like

我的假设是EF6会产生类似的东西

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:

即它会将DISTINCT应用于投影,因此,如果有两行具有相同的col1,col2,col3但不同的col4,则只有一行会使其成为结果。但是,我得到的SQL看起来像这样:

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:

- 没有投影,DISTINCT被转移到子查询中,好像我这样做:

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.

我希望这项服务是通用的,即可能与任何TSource和TResult一起工作,但看起来这里存在一些陷阱,而我对EF所做的事情的理解是不正确的。

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:

我相信问题出在我的转换器上。我使用以下函数生成传递给Select的lambda表达式:

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 ... },

ProviderJoinRateAndQA是提供商,他们的费率和QAs的连接。每个提供商可以拥有多种服务,费率和qas。在某些情况下,我希望视图模型隐藏速率和QA信息。我假设我可以通过为Rate和QA属性分配null来实现它,但它似乎不起作用:Distinct工作不正确。在我更换Rate = showRateAndQA之后?新QA {Grade = x.QA.Grade ...}:null,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.

显然,EF不喜欢我在lambda中将null指定为对象。

1 个解决方案

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

从内存中,默认的Distinct()实现通过使用相应类的默认Equals()方法来工作,这通常是不需要的(我发现这是我的工作至少的情况)。

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.

这应该允许对Distinct()的调用正常工作。

#1