Select 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   
    post.Comments.Load();
    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 Entity Frmaework to go to the database and bring the results back one row at a time. This is incredibly inefficient, and the Entity Frmaework 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 Include method to specify what pieces of the object model we want to include in the initial query.

// SELECT * FROM Posts JOIN Comments ...
var postsQuery = (from post in blogDataContext.Posts.Include("Comments")
                 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 it generally much harder to see what is causing the issue.

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