I have a query with a left join in it:
var query = (from v in context.Vehicles
//left join vehicleAttributes
join va in context.VehicleAttributes on v.VehicleId equals va.VehicleId into vAttributes
from vehicleAttributes in vAttributes.DefaultIfEmpty()
where v.FleetId == fleetId
select new { v, vehicleAttributes });
And now I need to do a paging on it.
this works but gets all rows, so much more than i actually need
query.ToList().Select(x => x.v).Distinct().Skip(10 * (page - 1)).Take(10).ToList();
this is what I tried instead but now I don't have the joint values
query.Select(x => x.v).Distinct().ToList().Skip(10 * (page - 1)).Take(10).ToList();
The ToList() triggers the call to the database so you need to only do this after you apply the Skip and Take. You'll need an OrderBy clause as well.
You should be able to do something like this:
var data = (from v in context.Vehicles
join va in context.VehicleAttributes on v.VehicleId equals va.VehicleId into vAttributes
from vehicleAttributes in vAttributes.DefaultIfEmpty()
where v.FleetId == fleetId
select new { v, vehicleAttributes })
.OrderBy(p => p.v.FleetId)
.Skip(10 * (page - 1))
.Take(10)
.ToList();