Your ORM supports lazy loading, but don't be so lazy yourself

If you will only use one field from the navigated object, don't rely on lazy loading, don't do code like this:

foreach(var p in db.Persons)
{
    Console.WriteLine("{0} {1}", p.PersonName, p.Country.CountryName);
}

That will produce these queries:

SELECT 
[Extent1].[PersonID] AS [PersonID], 
[Extent1].[PersonName] AS [PersonName], 
[Extent1].[TheCountryID] AS [TheCountryID]
FROM [dbo].[Person] AS [Extent1]


exec sp_executesql N'SELECT 
[Extent1].[CountryID] AS [CountryID], 
[Extent1].[CountryName] AS [CountryName], 
[Extent1].[Population] AS [Population]
FROM [dbo].[Country] AS [Extent1]
WHERE [Extent1].[CountryID] = @EntityKeyValue1',N'@EntityKeyValue1 int',@EntityKeyValue1=1


exec sp_executesql N'SELECT 
[Extent1].[CountryID] AS [CountryID], 
[Extent1].[CountryName] AS [CountryName], 
[Extent1].[Population] AS [Population]
FROM [dbo].[Country] AS [Extent1]
WHERE [Extent1].[CountryID] = @EntityKeyValue1',N'@EntityKeyValue1 int',@EntityKeyValue1=2

That will fetch the entire properties of Country. In this case, even if you didn't use the Population property, it also get populated by the database. This is a trivial example, though in a fairly-complex application, fetching fields that are not needed could put an unnecessary load to your application.


Use projections instead:

foreach (var p in 
    (from x in db.Persons select new { x.PersonName, x.Country.CountryName } ) )
{
    Console.WriteLine("{0} {1}", p.PersonName, p.CountryName);
}

This is the query produced by the ORM with the above Linq. Very efficient.

SELECT 
[Extent1].[TheCountryID] AS [TheCountryID], 
[Extent1].[PersonName] AS [PersonName], 
[Extent2].[CountryName] AS [CountryName]
FROM  [dbo].[Person] AS [Extent1]
INNER JOIN [dbo].[Country] AS [Extent2] ON [Extent1].[TheCountryID] = [Extent2].[CountryID]

So what's the use of lazy loading? It's useful if you need to display multiple headers with multiple details. Think DataRepeater. With lazy loading, you can already display some of the page content without waiting for the whole batch of data to arrive. You can already display the header without waiting for the header's details to arrive.


Though in our trivial code example, it doesn't warrant lazy loading, the Country is not many-to-one to Person, there's only one country per person. The person and its country are immediately displayed together. Aside from country, think of other foreign keys of Person, say there are 10 foreign keys in your Person object, it's a lousy practice to lazy load those 10 related tables, when most of the time you are just getting one field (mostly Name or Description) from those 10 tables.


And if you really need to use all the properties of a navigated property, e.g. CountryName, Population, CountryCode, etc; just eager load the related table by using Include on your ORM's query. This way, you can also avoid writing tedious projection ( select new { field, goes, here, etc })

foreach(var p in db.Persons.Include("Country"))
{
    Console.WriteLine("{0} {1} {2}", p.PersonName, p.Country.CountryName, p.Country.Population);
}            

The generated SQL:

SELECT 
[Extent1].[PersonID] AS [PersonID], 
[Extent1].[PersonName] AS [PersonName], 
[Extent1].[TheCountryID] AS [TheCountryID], 
[Extent2].[CountryID] AS [CountryID], 
[Extent2].[CountryName] AS [CountryName], 
[Extent2].[Population] AS [Population]
FROM  [dbo].[Person] AS [Extent1]
INNER JOIN [dbo].[Country] AS [Extent2] ON [Extent1].[TheCountryID] = [Extent2].[CountryID]

Even you don't use the Population property...

foreach(var p in db.Persons.Include("Country"))
{
    Console.WriteLine("{0} {1}", p.PersonName, p.Country.CountryName);
}

...the above code still produces the same SQL as above. So that's the other side of the coin of eager loading, it is overkill if you will just read only one property from the navigated property; might as well you use projections on your ORM's Linq/HQL, so you can streamline the network load your query utilizes. That's the behavior of eager loading :-)