New records inserted in foreign key table when inserting in parent table

asp.net-mvc-4 entity-framework-6

Question

I am new to Asp.net MVC and working on a simple blog application (Asp.Net MVC5, EF6) for learning.

I am using repository pattern for the solution architecture with EF Code first migration, Ninject for DI. On the client side, I am using jQuery Grid for Admin to manage Posts, Categories and Tags.

- Blog.Model: Post.cs, Category.cs, Tags.cs

public class Post
{
    [Required(ErrorMessage = "Id is required")]
    public int Id { get; set; }

    [Required(ErrorMessage = "Title is required")]
    [StringLength(500, ErrorMessage = "Title cannot be more than 500 characters long")]
    public string Title { get; set; }

    [Required(ErrorMessage = "Short description is required")]
    public string ShortDescription { get; set; }

    [Required(ErrorMessage = "Description is required")]
    public string Description { get; set; }

    public bool Published { get; set; }

    [Required(ErrorMessage = "PostedOn date is required")]
    public DateTime PostedOn { get; set; }

    public DateTime? ModifiedOn { get; set; }

    [ForeignKey("Category")]
    public virtual int CategoryId { get; set; }

    public virtual Category Category { get; set; }

    public virtual IList<Tag> Tags { get; set; }
}

public class Category
{
    [Key]
    public int CategoryId { get; set; }

    [Required(ErrorMessage = "Category Name is required")]
    [StringLength(500,ErrorMessage = "Category name length cannot exceed 500")]
    public string Name { get; set; }

    [Required(ErrorMessage = "Category Name is required")]
    [StringLength(500, ErrorMessage = "Category name length cannot exceed 500")]     
    public string Description { get; set; }

    [JsonIgnore] 
    public virtual IList<Post> Posts { get; set; }
}

public class Tag
{
    public int Id { get; set; }

    [Required(ErrorMessage = "Name is required")]
    [StringLength(500, ErrorMessage = "Name length should not exceed 500 characters")]
    public string Name { get; set; }

    public string Description { get; set; }

    [JsonIgnore]
    public IList<Post> Posts { get; set; }

}

- Blog.Repository: BlogRepository, IBlogRepository, BlogContext

public interface IBlogRepository
{
    int SavePost(Post post);

    //Other methods...
}

public class BlogRepository : BlogContext, IBlogRepository
{
    public BlogContext _db;

    public BlogRepository(BlogContext db)
    {
        _db = db;
    }

    public int SavePost(Post post)
    {
        _db.Posts.Add(post);
        _db.SaveChanges();
        return post.Id;
    }

    //Other implementations...
}

public class BlogContext : DbContext, IDisposedTracker
{
    public BlogContext() : base("BlogDbConnection") { }

    public DbSet<Post> Posts { get; set; }
    public DbSet<Tag> Tags { get; set; }
    public DbSet<Category> Categories { get; set; }

    public bool IsDisposed { get; set; }

    protected override void Dispose(bool disposing)
    {
        IsDisposed = true;
        base.Dispose(disposing);
    }

- Blog.Web: AdminController.cs, NinjectWebCommon.cs

AdminController sends/consumes data in Json format.

public class AdminController : Controller
{    
    private readonly IBlogRepository _blogRepository;

    public AdminController(IBlogRepository blogRepository)
    {
        _blogRepository = blogRepository;
    }

    //POST: /Admin/CreatePost
    [HttpPost, ValidateInput(false)]
    public ContentResult CreatePost([ModelBinder(typeof(PostModelBinder))] Post model)
    {
        string json;

        ModelState.Clear();

        if (TryValidateModel(model))
        {
            var id = _blogRepository.SavePost(model);
            json = JsonConvert.SerializeObject(
                new
                {
                    id = id,
                    success = true,
                    message = "Post saved successfully."
                });
        }
        else
        {
            json = JsonConvert.SerializeObject(
                new
                {
                    id = 0,
                    success = false,
                    message = "Post not saved."
                });
        }
        return Content(json, "application/json");
    }
}

public static class NinjectWebCommon 
{
  private static void RegisterServices(IKernel kernel)
    {
        kernel.Bind<BlogContext>().ToSelf(); //This isn't helping either
        kernel.Bind<IBlogRepository>().To<BlogRepository>();
    }   
}

I am using Custom Model Binding because I was getting validation exception while saving post since list of Categories and Tags received from grid do not map to actual objects in the application model. Therefore in the custom model binding, I am populating Post object with actual objects received from grid. This Post object is Sent to controller which Save to database using DbContext and Repository.

public class PostModelBinder : DefaultModelBinder
{
    public override object BindModel(ControllerContext controllerContext,  ModelBindingContext bindingContext)
    {
        var post = (Post)base.BindModel(controllerContext, bindingContext);

        **var blogRepository = new BlogRepository(new BlogContext());**//I think here I need to inject the dependency for BlogContext, but don't know how to do that.

        if (post.Category != null)
        {
            post.Category = blogRepository._db.Categories.AsNoTracking().Single(c => c.CategoryId == post.Category.CategoryId);
        }

        var tags = bindingContext.ValueProvider.GetValue("Tags").AttemptedValue.Split(',');

        if (tags.Length > 0)
        {
            post.Tags = new List<Tag>();

            foreach (var tag in tags)
            {
                var id = int.Parse(tag.Trim());
                post.Tags.Add(blogRepository._db.Tags.AsNoTracking().Single(t => t.Id == id));
            }
        }

        if (bindingContext.ValueProvider.GetValue("oper").AttemptedValue.Equals("edit"))
            post.ModifiedOn = DateTime.UtcNow;
        else
            post.PostedOn = DateTime.UtcNow;

        return post;
    }
}

Issue: When the Post is saved, data context inserts new rows for Category and Tags in their respective tables. The newly created post refers to new Category (Id:22) under Foreign key column.

Post:

enter image description here

Category:

enter image description here

Tag:

enter image description here

I think the reason for this is that when entity is saved it is attached to a different ObjectContext and I need to attach it to current context but do not know how? I found similar question asked before but there isn't an accepted answer to that. Any help would be greatly appreciated.

Accepted Answer

I was able to resolve above issue by attaching category and tags value to objectcontext manually, which indicates EF the changes it needs to make. This way it doesn't create new entries in Category and Tag's parent tables.

  public int SavePost(Post post)
    {
        //attach tags to db context for Tags to tell EF 
        //that these tags already exist in database
        foreach (var t in post.Tags)
        {
            _db.Tags.Attach(t);
        }

        //tell EF that Category already exists in Category table
        _db.Entry(post.Category).State = EntityState.Modified;

        _db.Posts.Add(post);

        _db.SaveChanges();

        return post.Id;
    }

 public void EditPost(Post post)
    {
        if (post == null) return;

        //get current post from database
        var dbPost = _db.Posts.Include(p => p.Tags).SingleOrDefault(p => p.Id == post.Id);

        //get new list of tags
        var newTags = post.Tags.Select(tag => new Tag() { Id = tag.Id, Name = tag.Name, Description = tag.Description }).ToList();

        if (dbPost != null)
        {
            //get category from its parent table and assign to db post
            dbPost.Category = _db.Categories.Find(post.Category.CategoryId); ;

            //set scalar properties
            _db.Entry(dbPost).CurrentValues.SetValues(post);

            //remove tags from post in database
            foreach (var t in dbPost.Tags.ToList())
            {
                if (!newTags.Contains(t))
                {
                    dbPost.Tags.Remove(t);
                }
            }

            //add tags to post in database
            foreach (var t in newTags)
            {
                if (dbPost.Tags.All(p => p.Id != t.Id))
                {
                    var tagInDb = _db.Tags.Find(t.Id);
                    if (tagInDb != null)
                    {
                        dbPost.Tags.Add(tagInDb);
                    }
                }
            }
        }

        //save changes
        _db.SaveChanges();
    }



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why