Insert multiple child records using linq to sql

I'm creating a flick web application.

I have a flickrUser table and a flickrPhoto table

I've already inserted the flickrUser record and now i want to insert some flickrPhoto records;

FlickrPhoto:
 -photoid
 -flickruserid
 -id (PK)

here's how i'm trying to insert the flickrphoto records (flickrphoto is stored in MyPhoto table)

public void insertList(List<string> photoids, string flickruserid)
    {
        PicsPreciousLinqDataContext db = new PicsPreciousLinqDataContext();
        for (int i = 0; i < photoids.Count; i++)
        {              
                MyPhoto mf = new MyPhoto();
                mf.photoId = photoids[i];
                mf.source = "flickr";

                var query = from b in db.FlickrUsers
                            where b.nsid == flickruserid
                            select b;

                FlickrUser fUser = query.FirstOrDefault();
                mf.FlickrUserId = fUser.nsid;
                db.MyPhotos.InsertOnSubmit(mf);                
        }
        db.SubmitChanges();  <= Cannot add an entity with a key that is already in use.
    }

I've tried a few variations on this but also without success. What is the correct way to insert multiple child records?

Any help is appreciated.

Answers


If the value of id set by the database (i.e. NEWID() as default value, or auto-increment), then in your dbml designer, click on the column that's the primary key and ensure that the following properties have these values:

Auto Generated Value: True
Auto-Sync: OnInsert
Primary Key: True

If it is not set by the database, then instead of setting the first two properties above, you must create unique values for the Id column in your C# code for each row you insert.

This can be done with:

 Guid.NewGuid();

So in your code:

PicsPreciousLinqDataContext db = new PicsPreciousLinqDataContext();
...
MyPhoto mf = new MyPhoto();
            mf.photoId = photoids[i];
            mf.source = "flickr";
            mf.id = Guid.newGuid();
...
db.MyPhotos.InsertOnSubmit(mf);  

for columns that are uniqueIdentifier type columns in the database.


Please check your MyPhotos Table, i suspect that it has the userId column as primary key . It should be foreign key/reference key column.

add the following instead your code,

 for (int i = 0; i < photoids.Count; i++)
    {              
            MyPhoto mf = new MyPhoto();
            mf.photoId = photoids[i];
            mf.source = "flickr";

            var query = from b in db.FlickrUsers
                        where b.nsid == flickruserid
                        select b;

            FlickrUser fUser = query.FirstOrDefault();
            mf.FlickrUserId = fUser.nsid;
            db.MyPhotos.Add(mf);               
    }
db.SubmitChanges();

Need Your Help

Problems downcasting a hibernate query

java hibernate casting

I'm handling a problem in downcasting a query.uniqueResult() using hibernate. I have 2 classes:

how to get timestamp from oracle DB using jdbc

java sql jdbc

how to get timestamp from oracle DB using JDBC? on sql i can use