Lazy Loading Properties in Linq To SQL

Linq to SQL provides deferred loading of foreign key object such as EntityRef and EntitySet so that you get the best performance and do not end up loading the entire object graph until you need it. However there are situations when you have binary or varchar(max) columns on a table which you do not want to load until you access the property. A real world example for this scenario would be like you are storing images in the product table or product table has description column which is set to varchar(max).  If all you are doing is querying the product table to bring  product collection and wont be touching description or image column than there really is no need to be loading those column ahead of time. If you are going to be using the Visual Studio designer than simply go to the property and set the Delay Loaded to true to turn on Delay Loading. On changing the setting in the properties window, Linq to SQL designer will create a private variable of type System.Data.Linq<T> where T is the type of property  like string, int etc. Here is an example where I have marked description column to be lazy loaded.

After setting my description column to be lazy loaded, querying category table would generate SQL that would not include description column. Here is the query output that confirms the behavior.

Setting lazy loading in the designer is a good option but what happens when you have a genuine need to have the properties not be lazy loaded on certain queries. In those cases you can use the familiar LoadWith option to load properties as well. LoadWith not only works with foreign key tables but you can use that to eagerly load lazy loaded properties. Here is an an example that confirms the behavior.

In the above code, I am using LoadOptions to tell Linq to SQL to immediately load Description property of Category entity despite the fact that it is marked as deferred. The SQL generated confirms that our description column is loaded immediately when we request for Category entity.