EntityFramework does swap on demand with connection

Question

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 the paging.

this works, but gets all the rows, much more than I really need

 query.ToList().Select(x => xv).Distinct().Skip(10 * (page - 1)).Take(10).ToList(); 

this is what i tried instead, but now i don't have common values

 query.Select(x => xv).Distinct().ToList().Skip(10 * (page - 1)).Take(10).ToList(); 

any ideas?

thanks

Answer 1

ToList () launches a call to the database, so you only need to do this after applying Skip and Take. You will also need an OrderBy offer.

You should 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 => pvFleetId) .Skip(10 * (page - 1)) .Take(10) .ToList(); 

Answer 2

Do not call ToList until Skip . This will return all records in the database matching your query.

To use Skip in an EntityFramework request, you need to have an IOrderedQueryable instance, so you need an IOrderedQueryable clause.

 query .Select(x => xv) .Distinct() .OrderBy(v => v.FleetId) .Skip(10 * (page - 1)) .Take(10).ToList(); 

I have a project that takes care of this feature. It is available on NuGet (with MVC copy ) and Google Code .

Using this would look something like this:

 var factory = new Pagination.PageSourceFactory { MaxItemsPerPage = 50, DefaultItemsPerPage = 20 }; var source = factory.CreateSource(query, page);