Linq Not Retrieving Correct Items in a Big Table

Question

I'm with a very strange problem.

I've a table with more than 800.000 records and 2GB mdf Database.

When I try to get the last records:
I'm only getting the records until one month ago, the last ones doesn't show up.

Dim Items = From Item In DB.Items _
            Where Item.CatID = CatID _
            Order By Item.PubDate Descending _
            Select Item Take 100

But if I limit the Select to the last IDs, then I get the expected result.

Dim Items = From Item In DB.Items _
            Where Item.CatID = CatID _
            And Item.ID > 600000 _
            Order By Item.PubDate Descending _
            Select Item Take 100

So, what's going on here.
Does Linq have a limit of the records it can query?

Answer 1

Perhaps this might do it:

Dim Items = From Item In DB.Items _
        Where Item.CatID = CatID _
        Order By Item.PubDate _
        Select Item Take 100

Since you were ordering by Item.PubDate Descending I removed the Descending so that you would take the first 100 records from the earliest records respective of the Item.PubDate field.