Linq Query reuse

Question

I have a Linq Query, Which Returns alot of results From SQL Server, I want to make sure, I get the records from database ONCE and then simply loop through the records on demand within the page without hitting DB.

Here is a Simple Query:

Dim c1 = (From c2 In _db.CategoryRelationShip
        Select c2)

Now Somewhere in Page I do:

For Each item In c1

Next

And Then Again:

For Each item In c1

Next

Does this mean in every For Loop, I am hitting database again and again? or my orignal Linq retieved all the records and i am simply looping through the array/results?

I heard about toList() method, so i am confused do i need it or not?... i mean what if I do:

Dim c1 = (From c2 In _db.CategoryRelationShip
        Select c2).toList()

Answer 1

LINQ uses deferred execution, which means that nothing gets evaluated until you need the data. This statement:

Dim c1 = (From c2 In _db.CategoryRelationShip
          Select c2)

does not fetch any data from the database. As Gert Arnold put it in the comments to the other answer:

c1 is just a query waiting to happen (deferred execution).

So yes. You will execute the query twice if you just iterate over c1. You can easily verify this:

Dim myCollection As New List(Of Integer)(New () {1, 2})

Dim result = (From c In myCollection
              Select c)

For Each i As Integer In result
    Console.WriteLine(i)    ' Will print 1 and 2
Next

myCollection.Add(3)
Console.WriteLine()

For Each i As Integer In result
    Console.WriteLine(i)    ' Will print 1, 2 and 3
Next

You can do the same test with your database.

To avoid calling the DB twice you can use .ToList() which will "force" the query to be evaluated and the result stored in a list. If you were to replace (From c In myCollection Select c) with (From c In myCollectionc Select c).ToList() in the above example, you would print 1 and 2 twice.