Linq-to-sql Add item and a one-to-many record at once

Question

I have a function where I can add articles and users can comment on them. This is done with a one to many relationship like= "commentId=>ArticleId". However when I try to add the comment to the database at the same time as I add the one to many record, the commentId is not known. Like this code:

Comment comment = new Comment();
comment.Date = DateTime.UtcNow;
comment.Text = text;
comment.UserId = userId;
db.Comments.InsertOnSubmit(comment);
comment.Articles.Add(new CommentsForArticle() { ArticleId = articleId, CommentId = comment.CommentId });

The commentId will be 0 before i press submit. Is there any way arround not having to submit in between or do I simply have to cut out the part where I have a one-to-many relationship and just use a CommentTable with a column like "ArticleId".

What is best in a performance perspective?

I understand the underlying issue, I just want to know which solution works best.

Answer 1

You're asking two different questions.

First, if a comment can only be for one article, it makes much more sense from a database design perspective to have ArticleId in the Comment table rather than creating a link table. An intermediate link table is typically only used for many:many relationships, since they can't be modeled explicitly and need that level of indirection.

That being said, if you have your foreign keys defined properly, then Linq2SQL will detect them and allow you to work with objects rather than keys (or you can use either). For example, your last line could be something like this, assuming you left your database design as-is:

comment.Articles.Add(new CommentsForArticle() { ArticleId = articleId, Comment = comment }

Notice that I'm using the actual Comment object rather than its ID. Doing it this way, Linq2SQL can automatically do that mapping for you. If you have a handle to the actual article, you could replace ArticleId = articleID with Article = article as well.