0

I'm not very familiar with the many-to-many insertion process using Entity Framework 4, POCO. I have a blog with 3 tables: Post, Comment, and Tag. A Post can have many Tags and a Tag can be in many Posts. Here are the Post and Tag models:

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

    [Required]
    [StringLength(25, ErrorMessage = "Tag name can't exceed 25 characters.")]
    public string Name { get; set; }

    public virtual ICollection<Post> Posts { get; set; }
}

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

    [Required]
    [StringLength(512, ErrorMessage = "Title can't exceed 512 characters")]
    public string Title { get; set; }

    [Required]
    [AllowHtml]
    public string Content { get; set; }

    public string FriendlyUrl { get; set; }
    public DateTime PostedDate { get; set; }
    public bool IsActive { get; set; }

    public virtual ICollection<Comment> Comments { get; set; }
    public virtual ICollection<Tag> Tags { get; set; }
}

Now when I'm adding a new post, I'm not sure what would be the right way to do. I'm thinking that I'll have a textbox where I can select multiple tags for that post (this part is already done), in my controller, I will check to see if the tag is already exists or not, if not, then I will insert the new tag. But I'm not even sure based on the models that I've created for EF, will they create a PostsTags table, or they are creating just a Tags and a Posts table and links between the two?

How would I insert the new Post and set the tags to that post? Is it just newPost.Tags = Tags (where Tags are the one that got selected, do I even need to check to see if they already exists?), and then something like _post.Add(newPost);?

Thanks.

Saxman
  • 5,009
  • 11
  • 51
  • 72
  • Hey Saxman, are you setting a custom Initializer? (DbDatabase.SetInitializer) If you are changing your model, you should let EF also build your database so you model matches the database. This may be the reason why your Many-to-Many did not work like it should have. If you need to know how to do this let me know! IMHO, you REALLY should not be defining a lookup entity in your domain model. EF will do this work for you and make it transparent. – Paul Feb 04 '11 at 17:45
  • Hi Paul, I do have EF set to `DropCreateDatabaseIfModelChanges`, I will try the approach that you have on the other post again to see if many-to-many relationship work. Thanks. – Saxman Feb 04 '11 at 23:04

3 Answers3

1

I would advise against maintaining many-to-many relationships in your database.

You should consider creating a lookup table called something like PostTag or PostTagLookup.

This would consist of two columns - PostId and TagId.

Entity framework handles lookup tables wonderfully, as mentioned by @Moyo in his answer:

Entity Framework will convert the mapping table into a collection of entities on both sides and the table itself will essentially disappear.

Community
  • 1
  • 1
Dan Atkinson
  • 11,391
  • 14
  • 81
  • 114
  • I'm a bit confused, do you think that this is a better approach than having a many-to-many relationship? And what I need to change on my model for to utilize this lookup table approach? Thank you. – Saxman Dec 31 '10 at 18:28
  • It depends on how you implement it. It can be as simple as adding the table and updating your solution. – Dan Atkinson Dec 31 '10 at 19:18
  • Just a note: You should only put the two entity ids in the lookup table (PostId, TagId)and leave out the extra Id column. If you add that Id column, EF won't 'hide' the lookup table as described in the referenced answer. – Eric King Jan 01 '11 at 17:08
  • Do I need to include the Post and Tag model as well? For example: `public int PostId { get; set; } public virtual Post Post { get; set; }` ? Thanks. – Saxman Jan 02 '11 at 18:08
  • I'm sorry but I don't quite understand what you asked. You shouldn't need to do much else here in order for it to work. – Dan Atkinson Jan 02 '11 at 18:39
  • Maybe I should re-phrase my question: I'm having trouble getting back the Tags by just having the PostId and TagId in a model called `PostTagsLookup`. It seems like I can only get the TagIDs, if I want to traverse back to the Tag table, I need to run another query. Thanks. – Saxman Jan 03 '11 at 18:55
  • If you have the PostID and TagID columns in the PostTagsLookup table, *and no other columns*, EF should create the many-to-many relationship for you and you should be able to reference the Tags property directly from the Post object. You may have to remove the PostTagsLookup table completely from your entity model, and then add it back before EF creates the relationship correctly. Simply doing an 'update model from database' operation may not work. – Eric King Jan 05 '11 at 18:24
  • @Eric, do I need to have any configuration (HasMany, HasKey, etc...) for the PostTagsLookup model? Also, in my Tag and Post model, do I have to include this: `public virtual ICollection PostTagsLookups {get;set;};` ? Thanks. – Saxman Jan 06 '11 at 17:38
  • By the way, I'm having this error when not declaring an Id for the PostTagsLookup table: `System.Data.Edm.EdmEntityType: : EntityType 'PostTagLookup' has no key defined. Define the key for this EntityType. System.Data.Edm.EdmEntitySet: EntityType: The EntitySet PostTagLookups is based on type PostTagLookup that has no keys defined.` – Saxman Jan 06 '11 at 17:51
  • @DanAtkinson can you advice me how would I insert just into lookup table if the tagId already exists. I created a post, http://stackoverflow.com/questions/26092567/mvc4-save-data-using-many-to-many-relationship Much appreciated if you can help me out a link or example. I am stuck and not sure what needs to be done. – Benk Sep 29 '14 at 23:37
1

I think that this is what you want.

var tag = context.Tags.FirstOrDefault(x=>x.Name == name);
if (tag == null)
    context.Tags.AddObject(tag = new Tag { Name = name });
if (!post.Tags.Contains(tag))
    post.Tags.Add(tag);

Happy New-Year coding))

The Smallest
  • 5,713
  • 25
  • 38
  • You're not doing a null check on `tag` until line 4, after already using it. Therefore, there's a chance you'd get a `NullReferenceException` in line 2. – Dan Atkinson Dec 31 '10 at 17:25
0

I can't seems to get it working without the Id for the Lookup table, this is what I've done to make it work. Might not be the best and most efficient way but it's working:

public class PostTagLookup
{
    public int Id { get; set; }
    public int PostId { get; set; }
    public int TagId { get; set; }

    public virtual Post Post { get; set; }
    public virtual Tag Tag { get; set; }
}
Saxman
  • 5,009
  • 11
  • 51
  • 72