Retrieving 10 records at a time only using LINQ

Question

I'm currently looking into server side paging and data calls for the kendoUI grid. I can get the grid to perform the server side transaction but I have notices that even if a select all or select the top 10 the reads to the SQL database are the same.

When returning all the records - it gets a sql profiler read of 104 When returning only 10 records - it gets a sql profiler read of 104, as well

Function used to return the data

public List<Employee> GetEmployees(int take, int skip)

{

return GetRepo<Employee>().All(null).where(p => p.IsDeleted == false).OrderBy(p => p.EmployeeNumber).Skip(10).Take(10).ToList();

}

When the LINQ performs the 10 record call, I have used the built in .Skip() and .Take() functions. This method require an .OrderBy() function to work, and I have order by the Id column on the table.

When you use the .Skip() and .Take() functions, it wraps a top 10 on the outside of the same query used to call all the records at once. Please see the sql extract taken from sql profiler,

SELECT TOP (10) *
FROM ( SELECT *, row_number() OVER (ORDER BY [Project1].[EmployeeNumber] ASC) AS [row_number]
FROM ( SELECT 
[Extent1].[Id]
AS [Id], 
[Extent1].[FirstName]
AS [FirstName], 
[Extent1].[LastName]
AS [LastName], 
[Extent2].[EmployeeNumber]
AS [EmployeeNumber], 
[Extent2].[WeeklyHours]
AS [WeeklyHours], 
'0X0X'  AS [C1]

FROM [dbo].[Person]
AS [Extent1]
INNER JOIN  [dbo].[Employee] AS [Extent2] ON [Extent1].[Id] = [Extent2].[Id]
WHERE 0 = [Extent2].[IsDeleted]
)  AS [Project1]
)  AS [Project1]
WHERE [Project1].[row_number] > 0
ORDER BY [Project1].[EmployeeNumber] ASC

The project is a asp.net MVC project and using framework 4.5.

Is there a way to return the 10 records back without having to scan through the whole table? As the employee and person table will grow to be more than 1 million active records at any time.

If this is in the wrong category, my apologies for that.

Any help would be greatly appreciated.

Thanks.

Lin Lin · Accepted Answer · 2013-12-11T20:30:45

Instead of using List, try to use IQueryable or IEnumerable. Below is an example:

public IQueryable<Employee> GetEmployees(int take, int skip)
{
   return GetRepo<Employee>().All(null).where(p => p.IsDeleted == false).OrderBy(p => p.EmployeeNumber).Skip(10).Take(10);
}

Note:

IQueryable is intended to allow a query provider (for example, an ORM like LINQ to SQL or the Entity Framework) to use the expressions contained in a query to translate the request into another format. In other words, LINQ-to-SQL looks at the properties on the entities that you're using along with the comparisons you're making and actually creates a SQL statement to express (hopefully) an equivalent request.

IEnumerable is more generic than IQueryable (though all instances of IQueryable implement IEnumerable) and only defines a sequence. However, there are extension methods available within the Enumerable class that define some query-type operators on that interface and use ordinary code to evaluate these conditions.

List is just an output format, and while it implements IEnumerable, is not directly related to querying.

For more details, take a look at below link:

Differences between IQueryable, List, IEnumerator?

Update

If you have a filter defined in the IRepository interface class like below

IQueryable<T> Filter(Expression<Func<T, bool>> filter);

You can change the GetEmployees method like below:

public IQueryable<Employee> GetEmployees(int take, int skip)
{
   return GetRepo<Employee>().Filter(p => p.IsDeleted == false).OrderBy(p => p.EmployeeNumber).Skip(10).Take(10);
}

The reason is to make the query deferred.

Retrieving 10 records at a time only using LINQ

1 Answers