LINQ to SQL Tricks: Building Efficient Queries that Include Reference Data or Child Entities

Problem Summary

Some times you need to read entities from a table along with some properties that are reference data or child entities. This relationship is usually represented by foreign keys in your database. LINQ to SQL makes this job extremely easy but, by default, also very inefficient. There are multiple ways this inefficiency can be addressed. Some of these alternatives are standard facilities provided by LINQ to SQL and some are custom coded patterns and tricks. This article walks your through these solutions, explaining their pros and cons and helping you decide which one is right for you.

Default Behavior

Consider the following simple subset from the AdventureWorksLT database:

Lets say your job is to select and display all the customer addresses in the database (we will limit results to first 3 to simplify output), with the following fields: First Name, Last Name, City, State, and Modified Date. You have your Data Access Layer (DAL) to get the data and your driver code to print the results. Something like this: