Create an entity for a 'many to many' mapping table Code First

Question

I will start by saying this may not be conceptually correct so I will post my problem also, so if someone can help with the underlying problem I won't need to do this.

Here is a simplified version of my model.

public class MenuItem
{
    public int MenuItemId { get; set; }
    public string Name { get; set; }
    public virtual ICollection<Department> Departments { get; set; }

    private ICollection<MenuSecurityItem> _menuSecurityItems;
    public virtual ICollection<MenuSecurityItem> MenuSecurityItems
    {
        get { return _menuSecurityItems ?? (_menuSecurityItems = new HashSet<MenuSecurityItem>()); }
        set { _menuSecurityItems = value; }
    }
}

public class Department
{
    public int DepartmentId { get; set; }
    public string Name { get; set; }
    public virtual ICollection<MenuItem> MenuItems { get; set; }
}

My underlying problem is that I want to select all MenuItems that belong to a Department (Department with DepartmentId = 1 for arguments sake) and also include all MenuSecurityItems.

I am unable to Include() the MenuSecurityItems as the MenuItems navigation collection is of type ICollection and doesn't support Include(). This also doesn't seem to work Department.MenuItems.AsQueryable().Include(m => m.MenuSecurityItems)

The way I "fixed" this issue was creating an entity for the many-to-many mapping table Code First creates.

public class DepartmentMenuItems
{
    [Key, Column(Order = 0)]
    public int Department_DepartmentId { get; set; }
    [Key, Column(Order = 1)]
    public int MenuItem_MenuItemId { get; set; }
}

I then was able to join through the mapping table like so. (MenuDB being my DBContext)

var query = from mItems in MenuDb.MenuItems
            join depmItems in MenuDb.DepartmentMenuItems on mItems.MenuItemId equals depmItems.MenuItem_MenuItemId
            join dep in MenuDb.Departments on depmItems.Department_DepartmentId equals dep.DepartmentId
            where dep.DepartmentId == 1
            select mItems;

This actually worked for that particular query... however it broke my navigation collections. Now EF4.1 is throwing an exception as it is trying to find an object called DepartmentMenuItems1 when trying to use the navigation collections.

If someone could either help me with the original issue or the issue I have now created with the mapping table entity it would be greatly appreciated.

score 1 · Accepted Answer · edited May 23 '17 at 09:59

Eager loading of nested collections works by using Select on the outer collection you want to include:

var department = context.Departments
    .Include(d => d.MenuItems.Select(m => m.MenuSecurityItems))
    .Single(d => d.DepartmentId == 1);

You can also use a dotted path with the string version of Include: Include("MenuItems.MenuSecurityItems")

Edit: To your question in comment how to apply a filter to the MenuItems collection to load:

Unfortunately you cannot filter with eager loading within an Include. The best solution in your particular case (where you only load one single department) is to abandon eager loading and leverage explicite loading instead:

// 1st roundtrip to DB: load department without navigation properties
var department = context.Departments
    .Single(d => d.DepartmentId == 1);

// 2nd roundtrip to DB: load filtered MenuItems including MenuSecurityItems
context.Entry(department).Collection(d => d.MenuItems).Query()
    .Include(m => m.MenuSecurityItems)
    .Where(m => m.Active)
    .Load();

This requires two roundtrips to the DB and two queries but it's the cleanest approach in my opinion which actually only loads the data you need.

Other workarounds are 1) either to apply the filter later in memory (but then you have to load the whole collection first from the DB before you can filter) or 2) to use a projection. This is explained here (the second and third point):

EF 4.1 code-first: How to order navigation properties when using Include and/or Select methods?

The examples in this answer are for ordering but the same applies to filtering (just replace OrderBy in the code snippets by Where).

If the MenuItem class contains a bool Active property, how can I add a where clause to the above query? var department = context.Departments.Include(d => d.MenuItems.Where(m => m.Active).Select(m => m.MenuSecurityItems)).Single(d => d.DepartmentId == 1) doesn't work — Matt Demler, Oct 11 '11 at 00:14

Create an entity for a 'many to many' mapping table Code First

1 Answers1