none
Retrieving 10 records at a time only

    Question

  • Hi All,

    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.

    1. When returning all the records - it gets a sql profiler read of 104
    2. 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.

    Wednesday, December 11, 2013 8:00 PM

Answers

All replies

  • I was able to solve the above issue. Please see the below link,

    http://stackoverflow.com/questions/20528942/retrieving-10-records-at-a-time-only-using-linq/20529312#20529312

    Thursday, December 12, 2013 2:18 AM
  • Hello,

    Glad to hear that you have found the solution and share it with us.

    Usually, as said in the link, we use the IQuerable<T> because it will translate the method like Take() and Skip() to sql statement, but ToList<T> and IEnumerable<T> will not, they just do the filter in memory.

    In a word,  cheers.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, December 12, 2013 7:44 AM