Entity Framework Navigation Property preload/reuse

Question

Why is Entity Framework executing queries when I expect objects can be grabbed from EF cache?

With these simple model classes:

public class Blog
{
    public int Id { get; set; }
    public string Name { get; set; }
    public virtual ICollection<Post> Posts { get; set; }
}

public class Post
{
    public int Id { get; set; }
    public string Content { get; set; }
    public virtual Blog Blog { get; set; }
}

public class BlogDbContext : DbContext
{
    public BlogDbContext() : base("BlogDbContext") {}

    public DbSet<Blog> Blogs { get; set; }
    public DbSet<Post> Posts { get; set; }
}

I profile the queries of following action

public class HomeController : Controller
{
    public ActionResult Index()
    {
        var ctx = new BlogDbContext();

        // expecting posts are retrieved and cached by EF
        var posts = ctx.Posts.ToList();
        var blogs = ctx.Blogs.ToList();

        var wholeContent = "";

        foreach (var blog in blogs)
            foreach (var post in blog.Posts) // <- query is executed
                wholeContent += post.Content;

        return Content(wholeContent);
    }
}

Why doesn't EF re-use the Post entities which I had already grabbed with the var posts = ctx.Posts.ToList(); statement?

Further explanation:

An existing application has an Excel export report. The data is grabbed via a main Linq2Sql query with a tree of includes (~20). Then it is mapped via automapper and additional data from manual caches (which previously slowed down the execution if added to the main query) is added.

Now the data is grown and SQL Server crashes when trying to execute the query with an error:

The query processor ran out of internal resources and could not produce a query plan.

Lazy loading would result in >100.000 queries. So I thought I could preload all the required data with a few simple queries and let EF use the objects automatically from cache during lazy loading.

There I initial had additional problems with limits of the TSQL IN() clause which I solved with MoreLinq´s Batch extension.

Answer 1

When you have Lazy Loading enabled, EF will still reload the Collection Navigation Properties. Probably because EF doesn't know whether you have really loaded all the Posts. EG code like

   var post = db.Posts.First();
   var relatedPosts = post.Blog.Posts.ToList();

Would be tricky, as the Blog would have one Post already loaded, but obviously the others need to be fetched.

In any case when relying on the Change Tracker to fix-up your Navigation Properties, you should disable Lazy Loading anyway. EG

using (var db = new BlogDbContext())
{
    db.Configuration.LazyLoadingEnabled = false;
    . . .