Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
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
Medium Priority
?
12,395 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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
Viewers will learn about arithmetic and Boolean expressions in Java and the logical operators used to create Boolean expressions. We will cover the symbols used for arithmetic expressions and define each logical operator and how to use them in Boole…
Viewers will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:

715 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