EF: "Enable" navigation property when creating a wrapper object with the "Select" projection

Question

I am including the navigation property in my request with Include , so that it will not be lazy later. But this does not work when I create an anonymous wrapper object with a Select projection.

Let me show you a simplified example. Essence:

 public class UserEntity { public string Name {get;set;} public virtual ICollection<UserEntity> Friends { get; set; } } 

Request:

 var entry = _dbCtx .Users .Include(x => x.Friends) // Select here is simplified, but it shows the wrapping .Select(user => new { User = user }) .First(); // Here we have additional lazy loaded DB call var friends = entry.User.Friends.Select(x => x.Name).ToList(); 

And I also see from the generated SQL that the navigation property is not enabled:

 SELECT [Limit1].[Name] AS [Name], FROM ( SELECT TOP (1) [Extent1].[Name] AS [Name] FROM [dbo].[Users] AS [Extent1] ) AS [Limit1] 

Is it possible to Include Friends navigation property in this case, so that User receives data without lazy loading?

I expected this to work:

 var entry = _dbCtx .Users .Select(user => new { User = user }) .Include(x => x.User.Friends) .First(); 

But getting the exception:

InvalidOperationException: The query result type is neither EntityType nor CollectionType with the entity element type. An Include path can only be specified for a query with one of these result types.

There are some workarounds I've come to, but they are somewhat complicated:

• Add the add property to our anonymous object in Select :

   var entry = _dbCtx .Users .Select(user => new { User = user, UsersFriends = user.Friends }) .First(); // manually copy the navigation property entry.User.Friends = user.UsersFriends; // Now we don't have any addition queries var friends = entry.User.Friends.Select(x => x.Name).ToList(); 

• Also map the user to an anonymous object at the database level, and then map the properties to UserEntity in C #.

   var entry = _dbCtx .Users .Select(user => new { User = new { Name = user.Name, Friends = user.Friends } }) .Take(1) // Fetch the DB .ToList() .Select(x => new { User = new UserEntity { Name = x.Name, Friends = x.Friends } }) .First(); // Now we don't have any addition queries var friends = entry.User.Friends.Select(x => x.Name).ToList(); 

So now there is a LEFT OUTER JOIN for Friends , but both workarounds are not very good:

1) Additional properties and copy are not a clean way.

2) My UserEntity has many more other properties. In addition, every time we add new properties, we must also change the selectors.

Is there a way to achieve a navigation property, including the first sample?

Thanks for reading, and I hope someone has the key to this.

EDIT:

I will continue the essence and the request to show a real use case.

Essence

 public class UserEntity { public string Name {get;set;} public int Score {get;set;} public virtual ICollection<UserEntity> Friends { get; set; } } 

Request

 var entry = _dbCtx .Users .Include(x => x.Friends) .Select(user => new { User = user, Position = _dbCtx.Users.Count(y => y.Score > user.Score) }) .First(); 

Answer 1

Not an answer to the question about _why_, but better code formatting is required ...

I am really surprised that it works that way. Perhaps EF discovers that you are not using the Friends property directly in your projection and therefore ignore it. What if you encapsulated an object outside of an EF request:

 var entry = _dbCtx .Users .Include(x => x.Friends) .Take(1); // replicate "First" inside the EF query to reduce traffic .AsEnumerable() // shift to linq-to-objects // Select here is simplified, but it shows the wrapping .Select(user => new { User = user }) .First()