In LINQ to Entities, the Include() method does not work with the Join() method

In using Entity Framework, we try to write queries that only incur one database roundtrip.  However, as described in this blog (http://blogs.msdn.com/b/alexj/archive/2009/06/02/tip-22-how-to-make-include-really-include.aspx), when you introduce an intermediary join or something that change the “shape” of the query, the Include() method will throw a runtime exception stating “method cannot be translated into a store expression”. 

The DbExtensions.Include extension method requires IQueryable<T> where T is the entity type that the Include() method is applied. However, when a join operator is used to join multiple tables, the ‘shape’, i.e., the query result, is changed to a new entity type. The Include() method call fails with this new query result type.

There are two workarounds proposed by the above blog and a stackoverflow.com answer by the same author (http://stackoverflow.com/questions/794283/linq-to-entities-include-method-not-loading).

The first workaround has the following code sample:

var results = 
    ((from post in dbContext.Posts
     from author in dbContext.Authors
         where author == post.Author and author.email="abc@email.com"
     select post) as ObjectQuery<Post>).Include(“Comments”);

It requires the final select be an entity that keeps the same query shape to use the Include() method. It only works for a few simple queries.

The second workaround has the following code sample:

var results =
    from post in dbContext.Posts
    from author in dbContext.Authors
         where author == post.Author and author.email="abc@email.com"

    select new { Post = post, Dummy = post.Comments };

The key is to put Dummy = post.Comments in the select clause. The "Dummy" property uses an Entity Framework featured called relationship fixup to populate the entity Properties. This is an elegant solution that works well. The blogger, Alex, explains the fixup in his EF Tips blog (http://blogs.msdn.com/b/alexj/archive/2009/02/25/tip-1-sorting-relationships-in-entity-framework.aspx ):  “In the Entity Framework Object Services automatically ties things together that are related in a process called Fix-up.  Fix-up generally occurs once the second of two related entities enter the context.”

The author (Alex D James)’s LINQ tips blog (http://blogs.msdn.com/b/alexj/archive/2009/03/26/index-of-tips.aspx) has many gems.