LINQ IQueryable GroupBy giving wrong results

494 Views Asked by At

first some code (simplified for the question's purpose):

Entity:

[View( "SupplierSearchViewView", 1 )]
public class SupplierSearchViewView : IViewDefinition
{
    public int SupplierId { get; set; }
    public int SkillId { get; set; }
    public Nullable<int> DocumentTypeId { get; set; }
    public Nullable<int> MainContactId { get; set; }
    public int SourceLanguageId { get; set; }
    public int TargetLanguageId { get; set; }
    public int TaskId { get; set; }
    public Nullable<int> SectorId { get; set; }
    public Nullable<int> DisciplineId { get; set; }
    public Nullable<decimal> Price { get; set; }
    public Nullable<int> SkillLevelId { get; set; }
}

A Linq Query:

    public IQueryable<SupplierSearchViewView> GetQuery( SupplierSearchCriteria sc, int? supplierId = null )
    {
        return this.supplierSearchViewViewRepository.GetManyNoTracking(
            x => new int[] { 84 }.Contains( x.SourceLanguageId )
                &&
                new int[] { 2 }.Contains( x.TargetLanguageId )
                &&
                new int[] { 35, 36 }.Contains( x.TaskId )
                &&
                x.SkillLevelId.HasValue && new int[] { 2, 3 }.Contains( x.SkillLevelId.Value )
                );
    }

(of course original method uses sc object - but this one is simplified to hardcoded values for test/presentation purposes)

this query returns 495 total count with 14 unique suppliers (which is correct)

and now the Group By thing...

        var query = GetQuery( sc );

        var groupedQuery = query.GroupBy( x => x.SupplierId );

        var tmp = groupedQuery.OrderBy( x => x.Key ).Skip( skip ).Take( take ).ToList();

when query done like that result is: 14 groups (which is correct of course, and all have correct IDs too) but... the groups are filled with much less items they should (edit: basically each group has some missing elements)

but... if i do a little trick in this line:

        var groupedQuery = query.ToList().GroupBy( x => x.SupplierId );

the result is... correct.

also in database when i use an sql like that:

    select x.SupplierId, count(*) from
    (
        select distinct *
        from SupplierSearchViewView ssvv
        where ssvv.TaskId in (35,36)
        and ssvv.SourceLanguageId = 84
        and ssvv.SkillLevelId in (2,3)
        and ssvv.TargetLanguageId = 2
    ) x 
    group by x.SupplierId

i get results like in 2nd case (the one with "early ToList()" )

i will really appreciate any ideas.

0

There are 0 best solutions below