Use eager loading / ".Include" with custom type select?

Question

This is kind a follow up question to THIS.

I have about the same problem, but the solution does not work 100% for me.
I have a query like the following:

item = db.Categories
       .Include(i => i.AccessRight.Roles).Include(i => i.AccessRight.Permissions)
       .Select(i => new ContentItemWithRevision<Category, ContentRevision>()
           {
               Item = i,
               AccessRight = i.AccessRight,
               Roles = i.AccessRight.Roles,
               Permissions = i.AccessRight.Permissions,
               Revision = i.Revisions.OrderByDescending(r => r.DateCreated).FirstOrDefault()
           })
       .FirstOrDefault(c => c.Item.Id == id);

The AccessRight, Roles and Permissions in the select where added to "trick" the eager loading as the Includes in the query are ignored, as I do not query an entity type.

But this does not work as I whished. item.Item.AccessRight is loaded by the AccessRight = i.AccessRight in the Select and can be used in my view where I only pass in item.Item, but item.Item.AccessRight.Roles and item.Item.AccessRight.Permissions are not (but item.Roles and item.Permission are loaded correctly).
So it seems this "trick" does only work for "one level".

Is there a way to solve this?
Is there a way now with new versions of EF to make the Include work, as this would be the best solution IMO?
Or can I make at least the trick work with "multiple levels"?

The only solution I can get working at the moment is to not pass item.Item but the complete item and use item.Roles instead of item.Item.AccessRight.Roles, but this is not very intuitive (as it crashes when some uses item.Item.AccessRight.Roles as he is not aware of the problem) and would require a few changes throughout my application.
I also tried using the Include after the Select, but that gives me an exception as after the Select I do not have an entity type anymore.

Maybe there is also a completely different solution for my base problem:
I just want to select my Category and the newest Revision of it and NOT all revisions (as these can be many). If there is a solution to do that without my custom type I am also fine with that solution.

UPDATE:
This is a simplified model of my DB:

So there is a 1:n for Category:Revisions, 1:1 for Category:AccessRight and n:m for AccessRights:Roles and AccessRights:Permissions.

Following query would also work, but get all revisions and not just the newest one:

var category = db.Categories
                   .Include(i => i.AccessRight.Roles)
                   .Include(i => i.AccessRight.Permissions)
                   .Include(i => i.Revisions)
                   .FirstOrDefault(i => i.Id == id);

Answer 1

Now that you have updated your question I can explain what you can and should do or not, and why.

In your working query you're obtaining a whole entity (Category) from the database, with all related navigation properties (AccesRight.Roles, AccesRight.Permissions and Revisions) eagerly loaded. So far so good:

var category = db.Categories
               .Include(i => i.AccessRight.Roles)
               .Include(i => i.AccessRight.Permissions)
               .Include(i => i.Revisions)
               .FirstOrDefault(i => i.Id == id);

But now your requirement is to load only the last revision in the Revisions collection. If you did so you would be cheating: semantically category.Revisions is "all the revisions of this category". If you used that property to load only the last revision, you'd be breaking that semantics. Even so, that's possible, but not advisable. However, it cannot be done using a single query: it must be done loading all the related entities, but Revisions, and then explicitly loading the revisions, but filtering them, like so:

ctx.Configuration.LazyLoadingEnabled = false;

var catWithRev = ctx.Categories
                .Include(c => c.AccessRight.Roles)
                .Include(c => c.AccessRight.Permissions)
                //.Include(c => c.Revisions) --  not eagerly loaded
                .FirstOrDefault(i => i.Id == id);

// Explicitly load the filtered revisions
ctx.Entry(catWithRev).Collection(cwr => cwr.Revisions).Query()
                .OrderByDescending(r => r.DateCreated).Take(1)
                .Load();

Now, you have what you wanted, but take these things into account:

• you should disable lazy loading. If not, your porperty could be lazy loaded, bringing all revisions into the property
• semantically is "crappy" because you have only the last revision in a collection that should contain all the revisions.

So it would be much better to create an object that holds the category, with all related navigation properties, but the revisions, and the last revision, like this:

var catWithRev = ctx.Categories
                .Include(c => c.AccessRight.Roles)
                .Include(c => c.AccessRight.Permissions)
                //.Include(c => c.Revisions)
                .Select(c => new
                {
                    Category = c,
                    LastRevision = c.Revisions
                        .OrderByDescending(r => r.DateCreated)
                        .FirstOrDefault()
                })
                .FirstOrDefault(i => i.Id == id);

In this way, all the data is loaded in a single query, and it's semantically correct. You can use an anonymous type, like in the sample code, or create a class specifically for this.

(NOTE: this code is tested and working )