Why is GetPaged() Executing two database calls?

Question

I'm a bit new to subsonic (i.e. evaluating 3.0.0.3) and have come across a strange behavior in GetPaged(int pageIndex, int pageSize). When I execute the method it does two SQL calls. Any ideas why ?

Details

Lets say I have a "Cultures" table with 200 rows. In my code I do something like ...

var sonicCollection = from c in RTE.Culture.GetPaged(1, 25)
                       select c;

Now, I would expect this executes a single query returning the first 25 entries in my cultures table. When I watch SQL profiler I see two queries run by.

First this--

SELECT [dbo].[Cultures].[cultureCode], [dbo].[Cultures].[cultureName]
FROM [dbo].[Cultures]

Then This--

SELECT *
FROM     (SELECT ROW_NUMBER() OVER (
    ORDER BY cultureID ASC) AS Row, 
    [dbo].[Cultures].[cultureCode], [dbo].[Cultures].[cultureName]
 FROM [dbo].[Cultures]
)
AS PagedResults
WHERE  Row >= 1 AND Row <= 25

I expect the 2nd query to roll by, as it is the one returning the 25 rows I politely requested of subsonic. The first query, however, appears to return 200 rows (at least according to SQL profiler).

Any ideas what's going on?

Answer 1

You just want to use RTE.Culture.GetPaged() - it runs the paged query for you.