Probably the coolest (and most frustrating) thing about LINQ is the fact that it supports lazy loading in certain situations, like Linq2Sql and in Entity framework. We discussed this in our Podcast regarding Linq. So let’s explore what we mean here…

The thing to know about lazy loading is that if your query you wrote isn’t needed, it’ll never be executed! That’s both fantastic and frustrating. The beauty here is that it can reduce the impact on your database. The downside is that it may sneak up on you if you don’t know why you’re not getting the results you were looking for. So, let’s check out an example.

private static void SimpleQuerySyntaxLazy(AdventureWorks2012Entities db)
{
	Console.WriteLine(@"
===============================================================================
Start SimpleQuerySyntax
===============================================================================");

	// Query Expression
	var salesPeople = from sp in db.SalesPersons
						select sp;

	Console.WriteLine("Notice that the SalesPersons query hasn't dumped it's sql yet - that's because it hasn't run");

	var employees = from e in db.Employees
					select e;

	Console.WriteLine(@"The Employees query will never show up because it's never used

");

	foreach (var sp in salesPeople)
	{
		Console.WriteLine(String.Format("Sales Persion ID: {0}", sp.BusinessEntityID));
	}

	Console.WriteLine(@"
Now we've seen that the query executed right before the enumerator because the results were needed.");
	Console.WriteLine(@"
===============================================================================
End SimpleQuerySyntax
===============================================================================");
}

private static void SimpleQuerySyntaxLazy(AdventureWorks2012Entities db)

{

Console.WriteLine(@"

===============================================================================

Start SimpleQuerySyntax

===============================================================================");

// Query Expression

var salesPeople = from sp in db.SalesPersons

select sp;

Console.WriteLine("Notice that the SalesPersons query hasn't dumped it's sql yet - that's because it hasn't run");

var employees = from e in db.Employees

select e;

Console.WriteLine(@"The Employees query will never show up because it's never used

");

foreach (var sp in salesPeople)

{

Console.WriteLine(String.Format("Sales Persion ID: {0}", sp.BusinessEntityID));

}

Console.WriteLine(@"

Now we've seen that the query executed right before the enumerator because the results were needed.");

Console.WriteLine(@"

===============================================================================

End SimpleQuerySyntax

===============================================================================");

}

So here’s the cool part about what happens above. You’ll notice there are two LINQ queries. The first LINQ statement is designed to retrieve all the sales people from the SalesPersons Entity. The second LINQ statement is set up to retrieve all the employees from the Employees Entity. Now here’s where the fun begins. If you’re used to standard procedural programming, and you’re thinking this is going to work just like a standard SQL query, you’d be wrong. Here’s the output:

===============================================================================
 Start SimpleQuerySyntax
 ===============================================================================
 Notice that the SalesPersons query hasn't dumped it's sql yet - that's because it hasn't run
 The Employees query will never show up because it's never used
SELECT
 [Extent1].[BusinessEntityID] AS [BusinessEntityID],
 [Extent1].[TerritoryID] AS [TerritoryID],
 [Extent1].[SalesQuota] AS [SalesQuota],
 [Extent1].[Bonus] AS [Bonus],
 [Extent1].[CommissionPct] AS [CommissionPct],
 [Extent1].[SalesYTD] AS [SalesYTD],
 [Extent1].[SalesLastYear] AS [SalesLastYear],
 [Extent1].[rowguid] AS [rowguid],
 [Extent1].[ModifiedDate] AS [ModifiedDate]
 FROM [Sales].[SalesPerson] AS [Extent1]
-- Executing at 2/25/2014 11:09:51 PM -05:00
-- Completed in 24 ms with result: SqlDataReader
Sales Persion ID: 274
 Sales Persion ID: 275
 Sales Persion ID: 276
 Sales Persion ID: 277
 Sales Persion ID: 278
 Sales Persion ID: 279
 Sales Persion ID: 280
 Sales Persion ID: 281
 Sales Persion ID: 282
 Sales Persion ID: 283
 Sales Persion ID: 284
 Sales Persion ID: 285
 Sales Persion ID: 286
 Sales Persion ID: 287
 Sales Persion ID: 288
 Sales Persion ID: 289
 Sales Persion ID: 290
Now we've seen that the query executed right before the enumerator because the results were needed.
 ===============================================================================
 End SimpleQuerySyntax
 ===============================================================================

===============================================================================

Start SimpleQuerySyntax

===============================================================================

Notice that the SalesPersons query hasn't dumped it's sql yet - that's because it hasn't run

The Employees query will never show up because it's never used

SELECT

[Extent1].[BusinessEntityID] AS [BusinessEntityID],

[Extent1].[TerritoryID] AS [TerritoryID],

[Extent1].[SalesQuota] AS [SalesQuota],

[Extent1].[Bonus] AS [Bonus],

[Extent1].[CommissionPct] AS [CommissionPct],

[Extent1].[SalesYTD] AS [SalesYTD],

[Extent1].[SalesLastYear] AS [SalesLastYear],

[Extent1].[rowguid] AS [rowguid],

[Extent1].[ModifiedDate] AS [ModifiedDate]

FROM [Sales].[SalesPerson] AS [Extent1]

-- Executing at 2/25/2014 11:09:51 PM -05:00

-- Completed in 24 ms with result: SqlDataReader

Sales Persion ID: 274

Sales Persion ID: 275

Sales Persion ID: 276

Sales Persion ID: 277

Sales Persion ID: 278

Sales Persion ID: 279

Sales Persion ID: 280

Sales Persion ID: 281

Sales Persion ID: 282

Sales Persion ID: 283

Sales Persion ID: 284

Sales Persion ID: 285

Sales Persion ID: 286

Sales Persion ID: 287

Sales Persion ID: 288

Sales Persion ID: 289

Sales Persion ID: 290

Now we've seen that the query executed right before the enumerator because the results were needed.

===============================================================================

End SimpleQuerySyntax

===============================================================================

Notice that up above, even though there are TWO LINQ queries on the page, only one actual SQL query was run. Also, of importance is that the first query didn’t actually run until right before the lines of code where the collection was to be enumerated. Again, this is glorious! Once you’re aware of this, there are truly numerous possibilities – for instance, you can stack your LINQ queries and rather than having multiple database queries being run, LINQ will compile them into one query that is issued to the database. I’ll do a demo of that in the next posting.

I used the AdventureWorks database and I generated an Entity Framework EDMX for these samples. If you’d like to try these out for yourself you can grab the sample databases here:
http://msftdbprodsamples.codeplex.com/

I recommend using the database restore method if you download the database.