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.