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.

score 1 · Accepted Answer · edited May 23 '17 at 11:57

1

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.

edited May 23 '17 at 11:57

Community

1
1

answered Dec 11 '13 at 20:30

Lin

15,078
4
47
49

Thanks for the advice, @Lin. I have tried both IQueryable and IEnumerable but the SQL produced still scans the whole table and the takes what is needs. Even when creating a sql script in sql server management studio and using the 'rank() over(Order by)' function, the number of reads are the same. – Peter Channon Dec 11 '13 at 23:45
hi @pchannon, what is the return type of GetRepo().All(), if it's List, you need to change it to IQqueryable. – Lin Dec 12 '13 at 00:06
The return type of GetRepo() is IRepository. Please a little code snippet below `public static IRepository GetRepo() where T : class { var factory = new PayrollRepositories(); return factory.GetRepository(); }` – Peter Channon Dec 12 '13 at 00:10
I guess you should check the "All" method definition in the IRepository. It should look like "IQueryable All()". also I'm sure why you're returning static IRepository. – Lin Dec 12 '13 at 00:26
Thanks for the assistance @Lin. I found a way to lower the reads on the database by using the original statement by adding **.AsQueryable** to the call. Eg `return GetRepo().All(null).AsQueryable().where(p => p.IsDeleted == false).OrderBy(p => p.EmployeeNumber).Skip(10).Take(10).ToList();`. This has brought the reads down from 104 to 6. This will save the database load in the future. – Peter Channon Dec 12 '13 at 02:16
did the SQL sent to DB as viewed in SQL profiler actually change? – phil soady Dec 12 '13 at 11:57

Retrieving 10 records at a time only using LINQ

1 Answers1