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,148 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
  • 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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

What is Node.js? Node.js is a server side scripting language much like PHP or ASP but is used to implement the complete package of HTTP webserver and application framework. The difference is that Node.js’s execution engine is asynchronous and event…
Have you tried to learn about Unicode, UTF-8, and multibyte text encoding and all the articles are just too "academic" or too technical? This article aims to make the whole topic easy for just about anyone to understand.
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…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…

759 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now