I a currently "paging" through an IQueryable that has many fields { Table1Id, Table1Label, IsActive, ...} and there can be mulitple records in IQueryable result for a PdcId, like the following:
var result = from a in _entities.table1
join b in _entities.table2 on new { a.Table1Id, a.Table1LevelId } equals new { Table1Id = b.Table2Id, Table1LevelId = b.Table2LevelId }
join c in _entities.Table3 on a.Table1Id equals c.Table1Id into cc from ccc in cc.DefaultIfEmpty()
where a.Valide == true
select new MeteringPointDetailModel
{
Table1Id = a.Table1Id,
Table1Label = a.Label,
IsActive = a.IsActive,
Table3Label = ccc.Label,
};
I tried this code but when i see the result generating in sql using sql profiler, the query is running without stop !! (generate multiple sql query ).
var data = await result.Where(i => result.GroupBy(p => p.Table1Id)
.Select(t => t.Key)
.Skip(query.Page).Take(query.PageSize)
.Contains(i.Table1Id)).ToListAsync();
How would I do this if I wanted to first group by Table1Id and then skip and take on the distinct Table1Id.
for example: if I have a result that has two records, one ends up being # 50 and the other 51, if I page by 50, I won't get record 51, and I want it.
That's what i did and worked for me :
Solution 1
var dataTable1 = (from a in _entities.table1
where a.Valide == true
select new {...}).Skip(query.Page).Take(query.PageSize);
var result = from a in dataTable1
join b in _entities.table2 on new { a.Table1Id, a.Table1LevelId } equals new { Table1Id = b.Table2Id, Table1LevelId = b.Table2LevelId }
join c in _entities.Table3 on a.Table1Id equals c.Table1Id into cc from ccc in cc.DefaultIfEmpty()
select new MeteringPointDetailModel
{
Table1Id = a.Table1Id,
Table1Label = a.Label,
IsActive = a.IsActive,
Table3Label = ccc.Label,
};
var data = await result.ToListAsync();
Solution 2
var result = from a in _entities.table1
join b in _entities.table2 on new { a.Table1Id, a.Table1LevelId } equals new { Table1Id = b.Table2Id, Table1LevelId = b.Table2LevelId }
join c in _entities.Table3 on a.Table1Id equals c.Table1Id into cc from ccc in cc.DefaultIfEmpty()
where a.Valide == true
select new MeteringPointDetailModel
{
Table1Id = a.Table1Id,
Table1Label = a.Label,
IsActive = a.IsActive,
Table3Label = ccc.Label,
};
result = !string.IsNullOrWhiteSpace(query.Table3Label) ? result.Where(c => c.Table3Label.ToLower().Contains(query.Table3Label.ToLower())) : result;
var table1Ids = await result.OrderBy(p => p.Table1Id)
.Select(p => p.Table1Id).Distinct()
.Skip(query.Page).Take(query.PageSize)
.ToListAsync();
var data = await result.Where(p => table1Ids.Contains(p.Table1Id)).ToListAsync();
Solution 2 is more useful if there are clause condition (search criteria) in join tables as specified in the example.
Hope it will help someone.