Nested insert in Linq to SQL?

Question

How do i put a nested insert into database using Linq to SQL

my Table is like this:

[Post]
--Post ID
--Post Title

[Attachment]
--Post ID (PFK)
--AttachmentID (PK)
--IMGID (FK)
--VIDID (FK)

[IMG]
--IMGID (PK)

it's a M:N table

basically when i submit a form , it go together with the attachments within the form. Im ok with insert Post, but strugling with the inserting attachments since i dont have any POST ID yet...

so what do you think is the best solution for this kind of problem?

Thank you

Answer 1

If you create a two-way Association between Post and Attachment, so you can do the following:

Post post = new Post { /* Set some fields perhaps */ };
Attachment attachment = new Attachment { /* Same here */ };
attachment.Post = post; // That is the association you need to create
dataContext.Posts.InsertOnSubmit(post);
dataContext.SubmitChanges();

LINQ-to-SQL will then handle the insertion of the Attachment(s) automatically.

I haven't used the DBML designer in a while, but I'm fairly sure you can say that a Post has Attachments and that an Attachment has a Post. And when you say attachment.Post = post, the setter for Post internally will do _post.Attachments.Add(attachment) where _post is a private member of the Attachment entity. Then, when you submit the Post, it will loop through the Attachments and insert them too.