ASP MVC many-to-many relationship - retrieve data from associated table

Question

I am trying to create a database with a many-to-many relationship using EF code first.

public class Item
{
    public int ItemId { get; set; }
    public String Description { get; set; }
    public ICollection<Tag> Tags { get; set; }

    public Item()
    {
        Tags = new HashSet<Tag>();
    }
}

public class Tag
{
    public int TagId { get; set; }
    public String Text { get; set; }
    public ICollection<Item> Presentations { get; set; }

    public Tag()
    {
        Presentations = new HashSet<Item>();
    }
}

public class ItemsEntities : DbContext
{
    public DbSet<Item> Items { get; set; }
    public DbSet<Tag> Tags { get; set; }
}

After that I'm adding an Item to the database

var tag = new Tag { Text = "tag1" };
var item = new Item
{
    Description = "description1",
    Tags = new List<Tag>()
};
item.Tags.Add(tag);
using (var db = new ItemsEntities())
{
    db.Items.Add(item);
    db.SaveChanges();
}

The problem is that I can't output items with their associated tags. The controller looks like this:

public ActionResult Index()
{
    ItemsEntities db = new ItemsEntities();
    return View(db.Items.ToList());
}

and the view page has the following code:

@foreach (var item in Model) 
{
   <tr>
    <td>
        @Html.DisplayFor(model => item.Description)
    </td>
    <td>
        @foreach (var tag in item.Tags)
        {
            @tag.Text
        }
    </td>
</tr>
}

I expect the table to contain "description1" and "tag1" but I get only "description1". I really don't understand where the problem is. What is the correct way to do this?

score 2 · Accepted Answer · answered Jul 05 '12 at 03:33

2

Your navigation properties need to be marked virtual.

public class Item
{
    public int ItemId { get; set; }
    public String Description { get; set; }
    public virtual ICollection<Tag> Tags { get; set; }

    public Item()
    {
        Tags = new HashSet<Tag>();
    }
}

public class Tag
{
    public int TagId { get; set; }
    public String Text { get; set; }
    public virtual ICollection<Item> Presentations { get; set; }

    public Tag()
    {
        Presentations = new HashSet<Item>();
    }
}

answered Jul 05 '12 at 03:33

danludwig

46,965
25
159
237

Thanks a lot! That's exactly where the problem was – Ashton H. Jul 05 '12 at 03:42
No problem, it is a common first-timer gotcha. Note that this is also the case with non-collection navigation properties. – danludwig Jul 05 '12 at 03:53
I disagree. It is not the `virtual` keyword that introduces the n+1 problem, and that is all I introduced. N+1 can be avoided by eager loading the related entities, yes. But that is more information than the question asked for. – danludwig Jul 05 '12 at 04:16

score 1 · Answer 2 · edited May 23 '17 at 12:17

To make your code work, you could mark your collection properties as virtual stated by @danludwig. By marking the collection properties as virtual EF Code First will lazy load those properties when iterating over the items in your view. You run into a SELECT N+1 problem using this approach. Let's examine your view code:

@foreach (var item in Model) 
{
   <tr>
     <td>
       @Html.DisplayFor(model => item.Description)
     </td>
     <td>
       @foreach (var tag in item.Tags)
       {
         @tag.Text
       }
     </td>
   </tr>
}

In this foreach loop you iterate over all items in your Model that were selected using the EF data context.

db.Items.ToList()

This is your first select. But in your view above every time you access an item's Tags property another select is executed. The important thing is FOR EVERY ITEM. That means if you have 100 Items in db.Items DbSet, you'll execute 101 selects. This is not acceptable for most systems.

A better approach is to pre select the tags for each item. One approach is to use Include or to select the tags related to an item into dedicated object.

public class ItemWithTags 
{
    public Item Item { get;set; }
    public IEnumerable<Tag> Tags { get;set; }
}

public ActionResult Index()
{
    ItemsEntities db = new ItemsEntities();

    var itemsWithTags = db.Items.Select(item => new ItemWithTags() { Item = item, Tags = item.Tags});
    return View(itemsWithTags.ToList());
}

In your view you can iterate over the itemsWithTags collection, access items's properties and for tags you access the Tags property of ItemWithTags.

Another problem with your code is, that the ItemsEntities DbContext is opened in your code but never closed. You can use the VS MVC Templates to generate a Controller that handles DbContext opening and closing correctly!

You can use a tool like MVC Mini Profiler for to inspect the commands executed against the database. This Stackoverflow Question shows how to set up MVC Mini Profiler with EF Code First.

-1 for introducing entity model that cannot employ lazy loading for navigation properties. — danludwig, Jul 05 '12 at 04:11
Thanks for the answer, I'll keep this in mind. Could you please explain the part about closing ItemsEntities? I thought that `using (var db = new ItemsEntities()) {}` should handle disposal after ItemsEntities is used — Ashton H., Jul 05 '12 at 04:24
In your code ItemEntities is closed correctly when you add new items but not when you query these items in the `Controller.Index` method — saintedlama, Jul 05 '12 at 05:56

ASP MVC many-to-many relationship - retrieve data from associated table

2 Answers2