none
My SQL query is pulling back everything RRS feed

  • Question

  • hi,

    I'm following a simple example of doing paging on a ASP.NET GridView using LINQ.

    I'm looking at this tutorial: http://www.dbtutorials.com/display/linq-to-sql-paging-cs.aspx, however, most I find say the same thing.

    I have a simple table with about 5 columns, and I don't need to do any sorting or where clauses (yet). Here is my code:

            Public Shared Function [Select]() As IEnumerable(Of t_Job)
                Dim db As New JobDataContext()
                Return db.t_Jobs
            End Function
    
            Public Shared Function SelectPage(ByVal startRowIndex As Integer, ByVal maximumRows As Integer) As IEnumerable(Of t_Job)
                Return [Select]().Skip(startRowIndex).Take(maximumRows)
            End Function
    
            Public Shared Function SelectCount() As Integer
                Return [Select]().Count()
            End Function
    I hook SelectPage() into an ObjectDataSource which is hooked to my grid, all with Paging enabled. Everything renders and looks great! But when I put a SQL Trace using SQL Server PRofiler on the database and see what query is executing, it looks like this:

    SELECT [t0].[JobID], [t0].[TimeStarted], [t0].[TimeCompleted], [t0].[Status], [t0].[PriceFile], [t0].[RunDate], [t0].[SubmittedBy], 
    [t0].[JobType], [t0].[FamilyAffected], [t0].[ExportType], [t0].[Cancelled]
    FROM [dbo].[t_Job] AS [t0]
    See what I mean? It's just a very simple query, pulling *everything* from the table. Why isnt Skip() and Take() working properly to make the query more dynamic and use TOP and such, so only the records I need are pulled from SQL? I read other tutorials and now I am completely lost as to why my SQL is not doing this... what am I missing here?

    Thanks very much for your help
    Monday, May 11, 2009 10:12 PM

Answers

  • Try exposing the return value of your Select function as IQueryable(Of t_Job) instead of IEnumerable(Of t_Job). When you chain together the other extension methods like Skip, Take, and Count on IEnumerable, you'll get a client-side evaluation, but when you use IQueryable you will get the SQL you desire. Another way to do this is to force using IQueryable by using the AsQueryable extension method.
    Tuesday, May 12, 2009 3:12 AM
    Answerer

All replies

  • Try exposing the return value of your Select function as IQueryable(Of t_Job) instead of IEnumerable(Of t_Job). When you chain together the other extension methods like Skip, Take, and Count on IEnumerable, you'll get a client-side evaluation, but when you use IQueryable you will get the SQL you desire. Another way to do this is to force using IQueryable by using the AsQueryable extension method.
    Tuesday, May 12, 2009 3:12 AM
    Answerer
  • thanks very much, this worked just how I needed it to!!

    thanks again
    Friday, May 15, 2009 2:50 PM
  • I'm actually still having an issue though.

    I'm trying to do the same exact thing except using a stored procedure call.

    I have my stored procedure which does some query logic for displaying a grid. Now when I call it using skip().Take() like in the above, and look at the SQL Profiler - all I see is a call to the stored proc.

    Was I asking for too much thinking it would someone wrap the custom/intelligent paging code inside /outside the stored proc? IS this not possible with LINQ? Maybe there is some other way to call a stored procedure and have it modify the SP query to do intelligent paging automatically? OR do i have to write manual paging code into my stored procedures?

    thanks again
    Friday, May 15, 2009 2:52 PM
  • Stored procedures cannot be embeded within a query (you cannot apply further querying on the server to the result of a stored procedure.)  You could do this if you changed the stored procedure into a table-valued function.


    Wayward LINQ Lacky
    Monday, May 18, 2009 5:22 AM
    Moderator