Entity Framework nested navigation properties count only

Question

I have three model classes

public class Item1{

    public int Id;

    public List<Item2> Item2List { get; set; }
}

public class Item2{
    public int Id;

    //this is the FK
    public int Item1Id {get;set;}

    public Item1 Item1 {get;set;}

    //Not in db. Ignored field in EntityTypeConfiguration
    public int Item3Count;

    public List<Item3> Item3List { get; set; }
}

public class Item3{
    public int Id;

    //this is the FK
    public int Item2Id {get;set;}

    public Item2 Item2 {get;set;}    
}

I want to return the list of Item1 along with list of associated Item2, and load the COUNT of Item3List associated with Item 2 without loading the Item3List.

Here is what I am doing right now:

public IEnumerable<Item1> GetItems()
{
    return base.Query().Include(item1 => item1.Item2List.Select(item2 => item2.Item3List)).ToList();
}

This returns me the list of all 3 objects Item1, Item2 and Item3. But I only need the count of Item3List in Item3Count, and not the entire Item3List list. How can I achieve that? I tried this below, but it throws error.

return base.Query().Include(item1 => item1.Item2List.Select(item2 => new Item2 {
Item3Count = item2.Item3List.Count()
})).ToList();

The Include path expression must refer to a navigation property defined on the type. Use dotted paths for reference navigation properties and the Select operator for collection navigation properties. Parameter name: path

Answer 1

What you want is not possible. Of course you can't populate a not-mapped property in an EF LINQ query, because that's the idea of not mapping it. But you already knew that.

What you'd really like to do is something like this:

context.Item1s.Select(item1 => new Item1
{
    Id = item1.Id,
    Item2s = item1.Item2List.Select(item2 => new Item2List
    {
        Id = item2.Id,
        Item3Count = item2.Item3List.Count()
    })
})

But EF doesn't allow you to construct an entity object in an EF query.

The alternatives are not appealing.

You could build a structure of anonymous types ...

context.Item1s.Select(item1 => new
{
    Item1 = item1,
    Item2s = item1.Item2List.Select(item2 => new
    {
        Item2 = item2,
        Item3Count = item2.Item3List.Count()
    })
})

... and use this to construct a list of Item1 objects, each having their Item2Lists of Item2s with Item3Count values.

Better, but still not close to what would be ideal, is to use AutoMapper and map the entities to DTOs:

Mapper.CreateMap<Item1,Item1Dto>();
Mapper.CreateMap<Item2,Item2Dto>();

You can use AutoMapper's flattening feature to populate Item3Count. To do this, Item2Dto should have a property Item3ListCount and AutoMapper will translate this to Item3List.Count().