Select N+1

Select N+1Select N+1
Select N + 1 is a data access anti-pattern where the database is accessed in a suboptimal way. Take a look at this code sample, then we’ll discuss what is going on. Say you want to show the user all comments from all posts so that they can delete all of the nasty comments. The naive implementation would be something like:

// SELECT * FROM Posts
var postsQuery = from post in blogDataContext.Posts
select post;

foreach (Post post in postsQuery)
{
//lazy loading of comments list causes:
// SELECT * FROM Comments where PostId = @p0
foreach (Comment comment in post.Comments)
{
//print comment…
}
}

In this example, we can see that we are loading a list of posts (the first select) and then traversing the object graph. However, we access the collection in a lazy fashion, causing Linq to SQL to go to the database and bring the results back one row at a time. This is incredibly inefficient, and the Linq to SQL Profiler will generate a warning whenever it encounters such a case.

The solution for this example is simple. Force an eager load of the collection using the DataLoadOptions class to specify what pieces of the object model we want to load upfront.

var loadOptions = new DataLoadOptions();
loadOptions.LoadWith<Post>(p => p.Comments);
blogDataContext.LoadOptions = loadOptions;

// SELECT * FROM Posts JOIN Comments …
var postsQuery = (from post in blogDataContext.Posts
select post);

foreach (Post post in postsQuery)
{
// no lazy loading of comments list causes
foreach (Comment comment in post.Comments)
{
//print comment…
}
}

In this case, we will get a join and only a single query to the database.

Note: this is the classical appearance of the problem. It can also surface in other scenarios, such as calling the database in a loop, or more complex object graph traversals. In those cases, it generally much harder to see what is causing the issue.

Having said that, Linq to Sql Profiler will detect those scenarios just as well, and give you the exact line in the source code that causes this SQL to be generated.

Other options for solving this issue are futures, part of the PLinqO extension project for Linq to SQL, which are also used to solve the issue of Too Many Queries.