EntityFramework do Paging on a query with a join

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 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();

any ideas?

Thanks

Do you implement paging successfully, because i want to implement paging on a repeater an i get stuck. — Fares Ayyad, Dec 29 '16 at 10:26

score 7 · Accepted Answer · answered Jun 11 '13 at 15:07

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();

yes I know that ToList() calls the database but how do I solve this? I tried this: query.Select(x => x.v).Distinct().ToList().Skip(10 * (page - 1)).Take(10).ToList(); but then I won't get the joint values because I get more than one row per vehicle because it has many attributes... — Timothy, Jun 11 '13 at 18:35

score 0 · Answer 2 · answered Jun 11 '13 at 15:15

Don't call ToList before Skip. Doing so will return all the records in the database that match your query.

To use Skip on an EntityFramework query, you need to have an instance of IOrderedQueryable, so you need an OrderBy clause.

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

I've got a project that takes care of a lot of this functionality. It's available on NuGet (with an MVC counterpart) and Google Code.

Using it would look something like this:

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

Thanks for your answer but your suggestion doesn't work because I get more then one row per vehicle because of the join... If I do what you said I won't get the joint values... — Timothy, Jun 11 '13 at 18:38

EntityFramework do Paging on a query with a join

2 Answers2

Linked