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

Gert Arnold · Accepted Answer · 2016-05-07 20:59:37Z

3

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().

answered May 7, 2016 at 20:59

Gert Arnold

110k36 gold badges219 silver badges319 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

codermav Over a year ago

Thanks! I wasn't sure if it's possible, but that is what I needed to know

wajiro_91 · Accepted Answer · 2016-05-06 22:34:44Z

0

You are not using the ef conventions. Refer to https://msdn.microsoft.com/en-us/data/jj819164.aspx for that. You have to do something like this:

public class Item1{

  public int Item1Id{get;set;}

  public ICollection<Item2> Item2s{ get; set; }
}

public class Item2{

  public int Item2Id{get;set;}

  //Not in db. Ignored field in mapping
  [NotMapped]
  public int Item3Count => Item3s.Count;

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

  public Item1 Item1 {get;set;}

  public ICollection<Item3> Item3s{ get; set; }
}

public class Item3{
  public int Item3Id;

  //FK
  public int Item2Id {get;set;}

  public Item2 Item2 {get;set;}

}

Now:

public IEnumerable<int> GetCounts()
{
   //get the items1 including its List of item2
   var items1 = base.Query().Include(item1 => item1.Item2s);

   //get the counts
   var counts = items1.Select(item1 => item1.Item2s.Select(item2 => item2.Item3Count));

   //now in counts you have what you want, do what you please with it
   //and return it
   return counts;

}

I haven't tried this by it should works for you.

edited May 6, 2016 at 22:34

answered May 6, 2016 at 21:08

wajiro_91

1681 silver badge9 bronze badges

2 Comments

codermav Over a year ago

Sorry, I didn't post the complete model but it is already set up the way you have shown with foreign keys and navigation properties. 'Item3Count' property is set to Ignore in entitytypeconfiguration (this.Ignore(t => t.Item3Count)). But I am getting the following error "A specified Include path is not valid. The EntityType 'Namespace.DataContext.Item2' does not declare a navigation property with the name 'Item3Count'." Appreciate your help!

codermav Over a year ago

I want to return the IEnumerable<Item1> and not the IEnumerable<int>. Also, I tried your updated solution but it throws the exception 'Value cannot be null, parameter name Source'. Looks like it is because we are not fetching the Item3s and trying to get the count directly while Item3s is null. The code block works fine if you fetch Item3s in step 1, but that defeats the purpose. I am trying to find a way to return IEnumerbale<Item1> which includes Item2s and only the count for Item3s. Thank you for looking into it!

Collectives™ on Stack Overflow

Entity Framework nested navigation properties count only

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related