How to update/create many-to-many relational data in MVC Code-first using EF?

Question

I have pored through StackOverflow, Google and asp.net trying to find a clear cut, basic example of how to do this. All the examples have been abstract or involved complications that do not apply. I haven't been able to extract much useful from them. So far, none of them have completely answered my question or addressed my issue(s).

I am working on an MVC project with the following model:

Article.cs:

public class Article
{

    public int ArticleId { get; set; }
    public string Title { get; set; }
    .
    .
    .
    public virtual ICollection<Category> Categories { get; set; }

    public Article()
    {
        Categories = new HashSet<Category>();
    }
}

Category.cs:

public class Category
{
    public int CategoryId { get; set; }
    public string Name { get; set; }

    public virtual ICollection<Article> Articles { get; set; }

    public Category()
    {
        Articles = new HashSet<Article>();
    }
}

ArticleEntities.cs:

public class ArticleEntities : DbContext
{
    public DbSet<Article> Articles { get; set; }
    public DbSet<Category> Categories { get; set; }

}

An article can have many categories and a category can belong to many articles.

So far I can save/update/create all the article fields except the Categories.

I am representing them as a checkboxes in the view. I can get the values for the selected checkboxes into the controller, but, every attempt I have made to store them in the db with the article has failed.

How do I:

1) When saving an edited article, update the existing relations in the relation table without creating duplicates?

2) When saving a new article, create the chosen relations in the relation table?

Slauma · Accepted Answer · 2012-07-08 23:08:09Z

10

I assume that you get a list of CategoryIds from the controller post action, a List<int> or more general just an IEnumerable<int>.

1) When saving an edited article, update the existing relations in the relation table without creating duplicates?

Article article; // from post action parameters
IEnumerable<int> categoryIds; // from post action parameters

using (var ctx = new MyDbContext())
{
    // Load original article from DB including its current categories
    var articleInDb = ctx.Articles.Include(a => a.Categories)
        .Single(a => a.ArticleId == article.ArticleId);

    // Update scalar properties of the article
    ctx.Entry(articleInDb).CurrentValues.SetValues(article);

    // Remove categories that are not in the id list anymore
    foreach (var categoryInDb in articleInDb.Categories.ToList())
    {
        if (!categoryIds.Contains(categoryInDb.CategoryId))
            articleInDb.Categories.Remove(categoryInDb);
    }

    // Add categories that are not in the DB list but in id list
    foreach (var categoryId in categoryIds)
    {
        if (!articleInDb.Categories.Any(c => c.CategoryId == categoryId))
        {
            var category = new Category { CategoryId = categoryId };
            ctx.Categories.Attach(category); // this avoids duplicate categories
            articleInDb.Categories.Add(category);
        }
    }

    ctx.SaveChanges();
}

Note that the code also works when you have a ArticleViewModel instead of an Article, given that the property names are the same (SetValues takes an arbitrary object).

2) When saving a new article, create the chosen relations in the relation table?

More or less the same idea as above but simpler because you don't need to compare with an original state in the database:

Article article; // from post action parameters
IEnumerable<int> categoryIds; // from post action parameters

using (var ctx = new MyDbContext())
{
    foreach (var categoryId in categoryIds)
    {
        var category = new Category { CategoryId = categoryId };
        ctx.Categories.Attach(category); // this avoids duplicate categories
        article.Categories.Add(category);
        // I assume here that article.Categories was empty before
    }
    ctx.Articles.Add(article);

    ctx.SaveChanges();
}

edited Jul 8, 2012 at 23:08

answered Jul 8, 2012 at 18:48

Slauma

177k60 gold badges407 silver badges424 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

panzerblitzer Over a year ago

"I assume that you get a list of CategoryIds from the controller post action, a List<int> or more general just an IEnumerable<int>."

panzerblitzer Over a year ago

Yes, I am categoryIds in as a <int> collection (Ienumerable, actually). Everything looks like it should work, but one line of code won't compile: "if (!categoryIds.Any(c => c.CategoryId == categoryInDb.CategoryId))" I get an error that -'int' does not contain a definition for CategoryId.-

Slauma Over a year ago

@panzerblitzer: Yes, that was a mistake, sorry. I've corrected the code (using Contains, see above). if (!categoryIds.Any(c => c == categoryInDb.CategoryId)) would work as well.

panzerblitzer Over a year ago

Absolutely freaking awesome. Thank you so much for this. I've been trying to decipher others semi-applicable code to do this simple task for the last two days. This worked spot on, smooth as silk after the edit.

Collectives™ on Stack Overflow

How to update/create many-to-many relational data in MVC Code-first using EF?

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related