Better way to query a page of data and get total count in entity framework 4.1?

by

The following query will get the count and page results in one trip to the database, but if you check the SQL in LINQPad, you’ll see that it’s not very pretty. I can only imagine what it would look like for a more complex query.

var query = ctx.People.Where (p => p.Name.StartsWith("A"));

var page = query.OrderBy (p => p.Name)
                .Select (p => new PersonResult { Name = p.Name } )          
                .Skip(skipRows).Take(pageSize)
                .GroupBy (p => new { Total = query.Count() })
                .First();

int total = page.Key.Total;
var people = page.Select(p => p);

For a simple query like this, you could probably use either method (2 trips to the database, or using GroupBy to do it in 1 trip) and not notice much difference. For anything complex, I think a stored procedure would be the best solution.

More Related Contents:

  1. Validation failed for one or more entities. See ‘EntityValidationErrors’ property for more details [duplicate]
  2. Entity Framework Join 3 Tables
  3. mapping multiple tables to a single entity class in entity framework
  4. There is already an open DataReader associated with this Command which must be closed first
  5. Ignoring a class property in Entity Framework 4.1 Code First
  6. Entity Framework Timeouts
  7. EF Including Other Entities (Generic Repository pattern)
  8. Why is .Contains slow? Most efficient way to get multiple entities by primary key?
  9. Entity Framework/Linq EXpression converting from string to int
  10. Why is inserting entities in EF 4.1 so slow compared to ObjectContext?
  11. EF Code First: How to get random rows
  12. The mapping of CLR type to EDM type is ambiguous with EF 6 & 5?
  13. The result of a query cannot be enumerated more than once
  14. Entity Framework Code First Lazy Loading
  15. Entity Framework – Add Navigation Property Manually
  16. Internal .NET Framework Data Provider error 1025
  17. Navigation Property without Declaring Foreign Key
  18. Entity Framework Include() is not working
  19. Creating dynamic queries with entity framework
  20. Entity Framework Code Only error: the model backing the context has changed since the database was created
  21. EF4 Code First: how to add a relationship without adding a navigation property
  22. Entity Framework 4: How to find the primary key?
  23. An error occurred while saving entities that do not expose foreign key properties for their relationships
  24. Can you get the DbContext from a DbSet?
  25. How to Refresh DbContext
  26. Entity Framework Code First Date field creation
  27. Is it possible to prevent EntityFramework 4 from overwriting customized properties?
  28. Change db table name in EF4 (entity framework 4)
  29. Repository Pattern, POCO, and Business Entities
  30. Create association on non-primary key fields with Entity Framework 4.1 Fluent API

Leave a Comment