Composite Keys and separated Identity Key

Question

Please read that question: Create code first, many to many, with additional fields in association table

Here is the scenario from OP:

public class Member
{
    public int MemberID { get; set; }

    public string FirstName { get; set; }
    public string LastName { get; set; }

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

public class Comment
{
    public int CommentID { get; set; }
    public string Message { get; set; }

    public virtual ICollection<Member> Members { get; set; }
}

public class MemberComment
{
    public int MemberID { get; set; }
    public int CommentID { get; set; }
    public int Something { get; set; }
    public string SomethingElse { get; set; }
}

The accepted answer was:

public class MemberComment
{
    [Key, Column(Order = 0)]
    public int MemberID { get; set; }
    [Key, Column(Order = 1)]
    public int CommentID { get; set; }

    public virtual Member Member { get; set; }
    public virtual Comment Comment { get; set; }

    public int Something { get; set; }
    public string SomethingElse { get; set; }
}

Right now, my question is: What if I want add another identity field like a Guid or AutoInc int, by example:

    public int MemberCommentID { get; set; }

I'm not in discussing about it's the better method or no, I want know how I do it?

I was thinking something like this

public class MemberComment
{
    [Key, Column(Order = 0)]
    public int MemberCommentID { get; set; }

    [Key, Column(Order = 1)]
    public int MemberID { get; set; }
    [Key, Column(Order = 2)]
    public int CommentID { get; set; }

    public virtual Member Member { get; set; }
    public virtual Comment Comment { get; set; }

    public int Something { get; set; }
    public string SomethingElse { get; set; }
}

But then EF will (I think) use the 3 fields in the key, when the right is MemberCommentID is alone 1 key, and MemberID+CommentID is another key.

Answer 1

Why don't you use a ForeignKey on MemberID and CommentID in such a case?

public class MemberComment
{
    [Key]
    public int MemberCommentID { get; set; }

    [ForeignKey]
    public int MemberID { get; set; }
    [ForeignKey]
    public int CommentID { get; set; }

    public virtual Member Member { get; set; }
    public virtual Comment Comment { get; set; }

    public int Something { get; set; }
    public string SomethingElse { get; set; }
}

---

As you can see it will actually allow you to have duplicate MemberComments (that connect the same Member and Comment instances).

If you want to enforce that Member and Comment can be linked only once, then you can achieve it with Unique Constraint.

But if that is your requirement (one MemberComment per each Member Comment pair), then why do you want to add a MemberCommentID key?

MemberID, CommentID is a perfect and natural primary key, that does all the job done without additional fields/indices.