Solved

How do I handle Many-to-Many Inserts, Updates, and Deletions with MVC 3 using Entity Framework 4 CTP5 code-first approach?

Posted on 2011-03-25
4
12,339 Views
Last Modified: 2013-11-08
Hello Experts.  I'm getting into this MVC thing with Entity Framework and I'm loving it.  However, I've been hitting snags left and right when it comes to this many-to-many relationships.  Here you'll see the database diagram:
Database Diagramand here are the POCOs:
 
public class ItemCategory
{
    [Key]
    [ScaffoldColumn(false)]
    public int CategoryID { get; set; }


    [Required(ErrorMessage = "Please enter a name for this category")]
    public string Category { get; set; }


    [DataType(DataType.MultilineText)]
    public string Description { get; set; }


    public int? ParentCategoryID { get; set; }


    public virtual ItemCategory ParentCategory { get; set; }


    public virtual ICollection<ItemCategory> Subcategories { get; set; }


    public virtual ICollection<AuctionItem> AuctionItems { get; set; }


    public override string ToString()
    {
        return CategoryID.ToString();
    }
}

Open in new window

public class AuctionItem
{
    [Key]
    [ScaffoldColumn(false)]
    public int ItemID { get; set; }


    [Required(ErrorMessage = "Please enter a name for this item.")]
    public string ItemName { get; set; }


    [DataType(DataType.MultilineText)]
    public string Description { get; set; }


    [Required(ErrorMessage = "Please specify a quantity.")]
    [Range(0, int.MaxValue, ErrorMessage = "Please enter a number greater than or equal to 0.")]
    public int Quantity { get; set; }

        
    [Required(ErrorMessage = "Please select a company for this item.")]
    [Display(Name = "Auction Company")]
    public int CompanyID { get; set; }


    public virtual AuctionCompany AuctionCompany { get; set; }


    public virtual ICollection<ItemImage> ItemImages { get; set; }


    public virtual ICollection<ItemCategory> ItemCategories { get; set; }


    public virtual ICollection<LiveAuctionLot> LiveAuctionLots { get; set; }


    public override string ToString()
    {
        return ItemID.ToString();
    }
}

Open in new window

public class LiveAuctionContext : DbContext
{
    public DbSet<AuctionItem> AuctionItems { get; set; }
        
    public DbSet<ItemCategory> ItemCategories { get; set; }
        
    // Other Entities
 
    protected override void OnModelCreating(System.Data.Entity.ModelConfiguration.ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();

        // Assists mapping because of bug
        modelBuilder.Entity<ItemCategory>().HasOptional(c => c.ParentCategory).WithMany(c => c.Subcategories).HasForeignKey(c => c.ParentCategoryID);

        // Attempt at many-to-many
        modelBuilder.Entity<AuctionItem>().HasMany(i => i.ItemCategories)
                                            .WithMany(c => c.AuctionItems)
                                            .Map(m => {
                                                        m.ToTable("ItemCategoryAssociation");
                                                        m.MapLeftKey(i => i.ItemID, "ItemID");
                                                        m.MapRightKey(c => c.CategoryID, "CategoryID");
                                                    });
    }
}

Open in new window


Here's an example Create page for an Auction Item.
 
@model KaidAuction.Admin.UI.Models.CategoryCompanyItemViewModel

@{
    ViewBag.Title = "Create";
}

<h2>Create</h2>

<script src="@Url.Content("~/Scripts/jquery.validate.min.js")" type="text/javascript"></script>
<script src="@Url.Content("~/Scripts/jquery.validate.unobtrusive.min.js")" type="text/javascript"></script>

@using (Html.BeginForm()) {
    @Html.ValidationSummary(true)
    <fieldset>
        <legend>AuctionItem</legend>

        <div class="editor-label">
            @Html.LabelFor(model => model.AuctionItem.ItemName)
        </div>
        <div class="editor-field">
            @Html.EditorFor(model => model.AuctionItem.ItemName)
            @Html.ValidationMessageFor(model => model.AuctionItem.ItemName)
        </div>

        <div class="editor-label">
            @Html.LabelFor(model => model.AuctionItem.Description)
        </div>
        <div class="editor-field">
            @Html.EditorFor(model => model.AuctionItem.Description)
            @Html.ValidationMessageFor(model => model.AuctionItem.Description)
        </div>

        <div class="editor-label">
            @Html.LabelFor(model => model.AuctionItem.Quantity)
        </div>
        <div class="editor-field">
            @Html.EditorFor(model => model.AuctionItem.Quantity)
            @Html.ValidationMessageFor(model => model.AuctionItem.Quantity)
        </div>

        <div class="editor-label">
            @Html.LabelFor(model => model.AuctionItem.CompanyID)
        </div>
        <div class="editor-field">
            @Html.DropDownListFor(model => model.AuctionItem.CompanyID, new SelectList(Model.Companies, "CompanyID", "Name"))
            @Html.ValidationMessageFor(model => model.AuctionItem.CompanyID)
        </div>

        <div class="editor-label">
            @Html.LabelFor(model => model.AuctionItem.ItemCategories) (Hold Ctrl to select multiple)
        </div>
        <div class="editor-field">
            @Html.DecodedListBoxFor(model => model.AuctionItem.ItemCategories, CategoryHelper.GetCategoriesAsListItems(Model.Categories), new Dictionary<string, object> {{ "class", "categoryList" }, {"size", "10"}}, true)
            @*@Html.ValidationMessageFor(model => model.AuctionItem.ItemCategories)*@
        </div>

        <p>
            <input type="submit" value="Create" />
        </p>
    </fieldset>
}

<div>
    @Html.ActionLink("Back to List", "Index")
</div>

Open in new window

The Html.DecodedListBoxFor is a modified version of the listboxfor I made that does not HTML Encode the text so I can allow multiple selections of the categories like so:
 DecodedListBoxForI'm passing in this viewmodel:
 
public class CategoryCompanyItemViewModel
{
    public List<ItemCategory> Categories { get; set; }

    public List<AuctionCompany> Companies { get; set; }
        
    public AuctionItem AuctionItem { get; set; }
}

Open in new window

and here is my controller actions for create/edit (Note that only the create works - edit returns "Invalid values '1,5,7' because of validation but inner exception shows conflicting foreign key):
 
public ActionResult Create()
{
    CategoryCompanyItemViewModel viewModel = new CategoryCompanyItemViewModel
    {
        Categories = GetAllCategories(),
        Companies = _liveAuctionContext.AuctionCompanies.OrderBy(c => c.Name).ToList(),
        AuctionItem = new AuctionItem { Quantity = 1 }
    };
    return View(viewModel);
}


[HttpPost]
public ActionResult Create(CategoryCompanyItemViewModel viewModel, FormCollection collection)
{
    try
    {
        string categories = collection["AuctionItem.ItemCategories"];
        var categoryIDs = categories.Split(',');
        foreach (string categoryID in categoryIDs)
        {
            int CategoryID = int.Parse(categoryID);
            ItemCategory category = _liveAuctionContext.ItemCategories.SingleOrDefault(c => c.CategoryID == CategoryID);
            viewModel.AuctionItem.ItemCategories.Add(category);
        }
        _liveAuctionContext.AuctionItems.Add(viewModel.AuctionItem);
        _liveAuctionContext.SaveChanges();
        TempData["message"] = "Item created successfully.";
        return RedirectToAction("Index");
    }
    catch
    {
        viewModel.Categories = GetAllCategories();
        viewModel.Companies = _liveAuctionContext.AuctionCompanies.OrderBy(c => c.Name).ToList();

        return View(viewModel);
    }
}
        

public ActionResult Edit(int ItemID)
{
    var item = from i in _liveAuctionContext.AuctionItems
                where i.ItemID == ItemID
                select i;
    if (item.Count() > 0)
    {
        CategoryCompanyItemViewModel viewModel = new CategoryCompanyItemViewModel
        {
            Categories = GetAllCategories(),
            AuctionItem = item.Single()
        };

        return View(viewModel);
    }

    TempData["message"] = "That item cannot be found.";
    return RedirectToAction("Index");
}


[HttpPost]
public ActionResult Edit(CategoryCompanyItemViewModel viewModel, FormCollection collection)
{
    try
    {
        _liveAuctionContext.AuctionItems.Attach(viewModel.AuctionItem);
        viewModel.AuctionItem.ItemCategories.Clear();

        string categories = collection["AuctionItem.ItemCategories"];
        var categoryIDs = categories.Split(',');
        foreach (string categoryID in categoryIDs)
        {
            int CategoryID = int.Parse(categoryID);
            ItemCategory category = _liveAuctionContext.ItemCategories.SingleOrDefault(c => c.CategoryID == CategoryID);
            viewModel.AuctionItem.ItemCategories.Add(category);
        }

        _liveAuctionContext.Entry(viewModel.AuctionItem).State = EntityState.Modified;
        _liveAuctionContext.SaveChanges();
        TempData["message"] = "Item updated successfully.";
        return RedirectToAction("Index");
    }
    catch
    {
        viewModel.Categories = GetAllCategories();
        viewModel.Companies = _liveAuctionContext.AuctionCompanies.OrderBy(c => c.Name).ToList();

        return View(viewModel);
    }
}

Open in new window


So, what is the best way to create, update, and delete items associated with multiple categories?  Also - do I really have to query the DB and get each category and add it to the context when I create an item as I do here in my Create action?
 
string categories = collection["AuctionItem.ItemCategories"];
var categoryIDs = categories.Split(',');
foreach (string categoryID in categoryIDs)
{
    int CategoryID = int.Parse(categoryID);
    // get the category from the database and add to the model/context for saving 
    // This seems pretty excessive/costly when the SQL way of doing it is just inserting the ID
    // into the join table
    ItemCategory category = _liveAuctionContext.ItemCategories.SingleOrDefault(c => c.CategoryID == CategoryID);
    viewModel.AuctionItem.ItemCategories.Add(category);
}

Open in new window

0
Comment
Question by:Astynax777
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 3

Accepted Solution

by:
Astynax777 earned 0 total points
ID: 35234086
I e-mailed Scott Guthrie and he forwarded my question on to Jeff Derstadt who was able to provide me with a solution for this.

 
[HttpPost]
public ActionResult Edit(CategoryCompanyItemViewModel viewModel, FormCollection collection)
{
    try
    {
        // Retrieve the item
        var item = _liveAuctionContext.AuctionItems.Find(viewModel.AuctionItem.ItemID);
        // get the entry, so we can manipulate its state
        var itemEntry = _liveAuctionContext.Entry<AuctionItem>(item);
        // Make the entity modified
        itemEntry.State = EntityState.Modified;
        // Load the existing associated categories
        itemEntry.Collection(i => i.ItemCategories).Load();
        // Remove all the existing associated categories
        item.ItemCategories.Clear();
                
        // Retrieve the list of ids from the form submission (comes in comma-delimited string)
        string categories = collection["AuctionItem.ItemCategories"];
        // Split to an array
        var categoryIDs = categories.Split(',');
        // Iterate through each ID
        foreach (string categoryID in categoryIDs)
        {
            int CategoryID = int.Parse(categoryID);
            // Add Items that aren't already present
            item.ItemCategories.Add(_liveAuctionContext.ItemCategories.Find(CategoryID));
        }

        _liveAuctionContext.SaveChanges();
        TempData["message"] = "Item updated successfully.";
        return RedirectToAction("Index");
    }
    catch
    {
        viewModel.Categories = GetAllCategories();
        viewModel.Companies = _liveAuctionContext.AuctionCompanies.OrderBy(c => c.Name).ToList();

        return View(viewModel);
    }
}

Open in new window


He stated:

There are a couple of important points here:
 

1

The collection must be loaded before you clear it. I cannot tell from your forum post whether that is the case or not, but I suspect not.

2

You should use the “Find” method on DbSet rather than using Single/SingleOrDefault since Find first looks in the local set of entities the DbContext is tracking to see if the entity is already loaded prior to going to the database.

3

You can use the strongly typed “Entry” method on DbContext to get a nice experience around loading a collection.
I'm asking a couple more questions so I can get a better understanding of conditional update/delete methods - which I will post here afterwards.
0
 
LVL 3

Author Closing Comment

by:Astynax777
ID: 35237651
Sought the answer through Microsoft development team.
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL several years ago, it seemed like now was a good time to update it for object-oriented PHP.  This article does that, replacing as much as possible the pr…
SASS allows you to treat your CSS code in a more OOP way. Let's have a look on how you can structure your code in order for it to be easily maintained and reused.
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)
Suggested Courses

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question