c# - Insert Records in multiple table which are dependent on primary / foreign key using Linq-to-SQL -

i have 3 tables:

1. master_upload (master upload has primary key auto increment named master_upload_id)

2. master_upload_files (this consist of 2 columns , refer master_upload_id above table )

3. master_upload_tags (same second)

in 2nd , 3rd table there can multiple rows 1st table.

now insert in 2nd , 3rd table need master_upload_id after inserting. hence had call db.submitchanges @ least 3 times. if there multiple values 2nd , 3rd table had call db.submitchanges each row in 2 table. times insertion in 2nd or 3rd table can fail due rule violation.

hence need roll in these cases. how can that?

i use these things via sql server stored procedure, need in linq.

// here code sample using (dbdatacontext db = new dbdatacontext()) {     db.master_uploads.insertonsubmit(mu);// toget mu.upload_id      try     {         db.submitchanges();         master_upload_file mf = new master_upload_file();         mf.master_upload_id = mu.upload_id;         mf.upload_file_id = uploadedfile.file_id;          db.master_upload_files.insertonsubmit(mf);          (int = 0; < tags.length; i++)         {             master_upload_tag mt = new master_upload_tag();             mt.master_upload_id = mu.upload_id;             mt.tag = tags[i];             db.master_upload_tags.insertonsubmit(mt);         }          db.submitchanges();         gtu.writetext("0",context);     }     catch (exception)     {         gtu.writetext("1:file upload add error", context);     } } 

i using sql server 2008.

thanks

you're doing way complicated! use force, man!! :-)

try code:

// define context using (uploadcontextdatacontext ctx = new uploadcontextdatacontext()) {     try     {         // create new upload         upload = new upload();         up.uploadname = "some test name";          // define 2 new upload files          uploadfile file1 = new uploadfile { filename = "testfile1.zip" };         uploadfile file2 = new uploadfile { filename = "testfile2.zip" };          // *add* 2 new upload files "upload"         up.uploadfiles.add(file1);         up.uploadfiles.add(file2);          // define 3 new upload tags         uploadtag tag = new uploadtag { tagname = "tag #1" };         uploadtag tag2 = new uploadtag { tagname = "tag #2" };         uploadtag tag3 = new uploadtag { tagname = "tag #3" };          // *add* 3 new upload tags "upload"         up.uploadtags.add(tag);         up.uploadtags.add(tag2);         up.uploadtags.add(tag3);          // add "upload" context - *includes* files , tags!         ctx.uploads.insertonsubmit(up);          // call submitchanges *just once* store everything!         ctx.submitchanges();     }     catch (exception exc)     {         string msg = exc.gettype().name + ": " + exc.message;     } 

you set object graph - basic upload object - , add files , tags upload object. need add upload object data context - other (added) subobjects tagging along automatically!

and in case, need call submitchanges() a single time , insert new objects, set necessary foreign key / primary key relationships , you. no fiddling around primary keys, multiple calls database - just use magic of linq-to-sql!