EF: "Include" navigation property, when creating a wrapper object with "Select" projection

c# entity-framework entity-framework-6 linq-to-sql

Question

I am including the navigation property in my query with Include, so that it won't be lazy loaded later. But it doesn't work, when I create an anonymous wrapper-object with Select projection.

Let me show the simplified example. The entity:

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

The query:

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 see also from generated SQL, that navigation property is not included:

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 the navigation property Friends in this case, so that User will get the data without lazy loading?

I was expecting also this to work:

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

But getting an Exception:

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

There are some workarounds I came to, but they are somehow tricky:

  1. Add addition 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();
    
  2. Map also User to an anonymous object on the DB level, and then map 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 quite good:

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

2) My UserEntity has much more other properties. Additionally, every time we add new properties, we should modify also the selectors here.

Is there some way to achieve the navigation property including from the first sample?

Thank you for reading and I hope somebody has a clue for this.

EDIT:

I will extend the entity and the query to show a real use-case.

The Entity

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

The Query

var entry = _dbCtx
    .Users
    .Include(x => x.Friends)
    .Select(user => new {
        User = user,
        Position = _dbCtx.Users.Count(y => y.Score > user.Score)
    })
    .First();
1
7
1/21/2016 2:25:01 PM

Popular Answer

Not an answer as to _why_ but wanted better code formatting...

I'm actually surprised it works that way. Perhaps EF is detecting that you're not using the Friends property directly in your projection and thus is ignoring it. What if you encapsulated the object(s) outside of the EF query:

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()
0
1/21/2016 2:01:34 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow