Quick, how many times will this LINQ query hit the database:

Stopwatch sw = new Stopwatch();

sw.Start();

NorthwindDataContext context = new NorthwindDataContext();

context.ObjectTrackingEnabled = false ;

var result = from c in context.Customers

             select c; // new { CustomerID = c.CustomerID, Company = c.CompanyName };

for (int x = 1; x < 1000; x++ )

{ 

    foreach (Customer c in result)

    {                    

        string val = c.CustomerID;

    }

}

Response.Write("<hr>" + sw.Elapsed.Ticks.ToString("n") + " ticks");

return;

You sure?

LINQ queries are not fired when they are declared, but when they  enumerated over! Objects are created as they are enumerated. As a result the above actually fires 1000 queries against the database. So although you have a list of objects retrieved, physical and in hand the IEnumerable<T> is firing multiple times every time you get a new Enumerator in the loop.

So in essence LINQ results are forward only read once results. Any subsequent Enumeration results in re execution of the full query. The above example is frivolous but there are some scenarios where you might want to loop over lists more than once. For example if you do databinding on the retrieved data but before binding you decide you want to change values. Imagine this scenario:

NorthwindDataContext context = new NorthwindDataContext();

context.ObjectTrackingEnabled = false;

IEnumerable<Customer> result = from c in context.Customers

             select c; // new { CustomerID = c.CustomerID, Company = c.CompanyName };

foreach (Customer c in result)

{

    // *** do some data modification

    c.CompanyName = "** " + c.CompanyName;                

}

this.grdData.DataSource = result;

this.grdData.DataBind();

Here there's some pre-processing of the query data before databinding (again - frivolous: you could have done that as part of the query, but you get the idea). What do you expect to see in the GridView display for Company? Probably not what you're getting, which is the raw company name from a second database query *without* the ** prefix. 

This is something that will take some getting used to. It's an easy mistake to make and one that you may not notice right away but that can have severe performance implications. It's even more problematic with results returned from a middle tier because in that case the data may be indirectly created with LINQ and you may not realize that IEnumerable<T> originated through LINQ.

Luckily there's an easy workaround for this: You can use the ToList, ToArray, ToDictionary methods to create a concrete list. So the following code gives the expected result:

NorthwindDataContext context = new NorthwindDataContext();

context.ObjectTrackingEnabled = false;

IEnumerable<Customer> result = from c in context.Customers

             select c; 
 

IList<Customer> CustList = result.ToList();            

foreach (Customer c in CustList)

{

    // *** do some data modification

    c.CompanyName  = "** " + c.CompanyName;                

}

this.grdData.DataSource = CustList;

this.grdData.DataBind();

So in effect you need to treat a raw LINQ IEnumerable resultset as a forward only set. Once you iterate over it again it fires the query again. This is true not just for LINQ to SQL but LINQ in general.

Once you know how this works this scenario is not a problem but it is a little unexpected to the unsuspecting consumer of a list...