I have in my database an Object
which has an object Owner
. Owner
has a string Name
. Object
also holds object Details
.
I need the Object
, it's Details
, and the Owner.Name
. I want to prevent loading the whole Owner
object (as detailled here) and I want only a single database query.
I tried:
DataContext.Objects.Select(o => new { Object = o, OwnerName= o.Owner.Name })
.Include(o => o.Object.Details);
but it throws:
System.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.
Also tried calling Include
before Select
:
var d = DataContext.Objects.Include(o => o.Details)
.Select(o => new { Object = o, OwnerName= o.Owner.Name });
But this ignores the Include
, ie. Accessing d.Details
causes a DB query.
Any ideas?
You're touching on an issue that does exist in EF.
The problem lies in how EF handles the loading of data. It loads all scalar properties of an object, but not the navigational properties.
Include
influences this behavior, by telling EF to also include a specified navigational property (with all of its scalar properties)
But then we get to Select
. When you use this, you are essentially giving a fixed list of columns that you want to retrieve. This overrides the default behavior of loading all scalar properties, which sadly also overrides the Include
statements that you added.
The simplest solution is to explicitly tell EF to retrieve the details:
var d = DataContext.Objects.Include(o => o.Details)
.Select(o => new {
Object = o,
ObjectDetails = o.Details,
OwnerName= o.Owner.Name
});
As an aside, since the Select
explicitly states which columns EF needs to retrieve, you don't need the Include
statement anymore.
This behavior can already be seen by you retrieving o.Owner.Name
without actually calling Include(o => o.Owner)
.