Entity Framework 4 CTP 5 POCO - Many-to-many or Lookup table?

Question

I'm building a personal blog app with Entity Framework 4 CTP 5 POCO only, where a Post can have many Tags and a Tag can be in many Posts. My question is whether to build a many-to-many model, or to have a lookup table.

At first I was trying to use many-to-many, but I don't know how to do insertion, each time a new post is posted (with many tags selected), I'm not sure what to do so that the tags should be associated with the post (and wouldn't insert a new tag if the tag name already exists.)

I then try to build a Lookup table like so:

Post

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<PostTagLookup> PostTagLookups { get; set; }
}

Tag

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 string FriendlyName { get; set; }
    public virtual ICollection<PostTagLookup> PostTagLookups { get; set; }
}

PostTagsLookup

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

The problem is I'm not sure how EF are going to handle lookup table (how will I get the Tags of a Post, or a collection of the Posts when I select a Tag). And with the code below, I got an error saying PostTagLookup doesn't have an Id key:

var getPosts = _post.GetLatestPosts(3).ToList();

var posts = from post in getPosts
        select new PostModel
        {
            Id = post.Id,
            Title = post.Title,
            Content = post.Content,
            FriendlyUrl = post.FriendlyUrl,
            PostedDate = post.PostedDate,
            IsActive = post.IsActive,
            NumberOfComments = post.Comments.Count(),
            PostTags = post.PostTagLookups.Where(p => p.PostId == post.Id).ToList()
        };

Any suggestion on how to accomplish this task? Thank you very much!

score 0 · Answer 1 · answered Jan 03 '11 at 23:40

I think your model should work as-is with a slight tweak: add an ID column/field to the PostTagLookup entity.

public class PostTagLookup
{
    [Key]
    [DatabaseGenerated(DatabaseGenerationOption.Identity)]
    public int PostTagLookupId { get; set; }

    //etc.
}

However, I'm not sure why you wouldn't want EF to handle the underlying many-to-many on it's own. When you have a new Post object, for example, all you have to do is add any associated Tags to the instantiated Post's Tags collection before calling SaveChanges() on your context. Did this not work for you?

I've tried many-to-many but didn't work (this is my original question: http://stackoverflow.com/questions/4571188/entity-framework-4-many-to-many-insertion), it throw an error on something related to object has not been instantiated following **The_Smallest** suggestion. Then there's another suggestion saying I should be using a lookup table, without the `Id` column! — Saxman, Jan 03 '11 at 23:56

score 0 · Accepted Answer · answered Jan 06 '11 at 18:37

I've struggled with it for the past couple days and decided to go with the Lookup table as intended, and in the Lookup table, also have references to Post and Tag model as such:

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; }
}

Might not be the best way but it's working :) Thanks all for looking.

Entity Framework 4 CTP 5 POCO - Many-to-many or Lookup table?

2 Answers2

Linked