Solved

Linq parent child insert causes ForeignKey error

Posted on 2011-09-16
5
1,049 Views
Last Modified: 2012-05-12
Hi i'm new to linq to sql classes and i'm having a problem with inserting a parent and child together using the code below

 Using db As New FMSDataContext
                        Dim NS As New Site
                        NS.Title = "newsite"
                        NS.Created = Now()
                        NS.CreatedBy = "me"
                        db.Sites.InsertOnSubmit(NS)


                        Dim Address As New Address
                        Address.Address1 = "test"
                        Address.Town = "testtown"
                        Address.PostCode = "teste"
                        Address.Created = Now()
                        Address.CreatedBy = "me"

                        NS.Addresses.Add(Address)
                        db.Addresses.InsertOnSubmit(Address)


                        db.SubmitChanges()

                    End Using

i can submit the changes for each table seperatly and they are written tot he database, i can't get the releations to tie together using this method (i could get the ne id and then use this and have 2 submit changes but i've read you don't need to do this).

the problem is that the above code cause the error below
System.Data.Linq.ForeignKeyReferenceAlreadyHasValueException: Operation is not valid due to the current state of the object

i belive that this is do with the id field already being written.

Thanks in advances
0
Comment
Question by:bolt81
  • 3
5 Comments
 
LVL 2

Expert Comment

by:Lord_Death
ID: 36548000
You should provide the Id Field in Address.FK_NewSiteID  then just do the following:

db.Addresses.InsertOnSubmit(Address);
db.SubmitChanges()

Your Code should look like:

                Using db As New FMSDataContext
                        Dim NS As New Site
                        NS.Title = "newsite"
                        NS.Created = Now()
                        NS.CreatedBy = "me"
                        db.Sites.InsertOnSubmit(NS)


                        Dim Address As New Address
                        Address.Address1 = "test"
                        Address.Town = "testtown"
                        Address.PostCode = "teste"
                        Address.Created = Now()
                        Address.CreatedBy = "me"
                       
                        Address.SietID = NS.ID

                        db.Addresses.InsertOnSubmit(Address)
                        db.SubmitChanges()

                    End Using

this will tie the relations together
0
 
LVL 1

Author Comment

by:bolt81
ID: 36548014
Hi the releationships the other way round "site" is the parent and "Address" is the child

i've edited the code to look like this
Using db As New FMSDataContext
                       
                        Dim Address As New Address
                        Address.Address1 = "test"
                        Address.Town = "testtown"
                        Address.PostCode = "teste"
                        Address.Created = Now()
                        Address.CreatedBy = "me"

                       db.Addresses.InsertOnSubmit(Address)

                        Dim NS As New Site
                        NS.Title = "newsite"
                        NS.Created = Now()
                        NS.CreatedBy = "me"

                        NS.AddressID = Address.AddressID
                        db.Sites.InsertOnSubmit(NS)

                        db.SubmitChanges()

                    End Using

this creates a record in each table with no errors but the new address id ins't put into the site record
0
 
LVL 2

Expert Comment

by:Lord_Death
ID: 36548072
Ok after  the last submitChanges() you have to re new your DataContext because of a refresh Issue,

do this after end using :

db As New FMSDataContext

and the new address object will be in its parent (site) record Like :

MyObjAddress.Sites
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 36570486
I usually use two submit changes. And you would use the SiteID in Address if the Site is the parent.
0
 
LVL 2

Accepted Solution

by:
Lord_Death earned 500 total points
ID: 36578641
If You have something like :

Site : SiteID , SiteName ,...
Address : AddressID,Address1,FK_SiteID

then Site  would be parent of Address , and i Would use the parent id cause you have to insert the parent at first  (Foreign Key Constraint)  and then after submitting changes you have the value of your primary key,

now when you want to add child for your parents in this case Addresses  you use the parent property in this case Site .

a simple Example would be :

 Using db As New FMSDataContext
                       
                        Dim ObjCountry As New Country
                        ObjCountry.Name = "United States of America"
                        ObjCountry.Code = "US"
                       
                        db.Countries.InsertOnSubmit(ObjCountry)
                        db.SubmitChanges()
                        'Here I assume CountryID is as AutoIdentity Field , so after
                        'SubmitChanges() I have a  value for CountryID

                        Dim ObjCity As New City
                        ObjCity.CityName = "New York"
                        ObjCity.CityCode = "NY"
                        ObjCity.CountryID = ObjCountry.CountryID

                        db.Cities.InsertOnSubmit(ObjCity)

                        Dim ObjCity2 As New City
                        ObjCity2.CityName = "Los Angeles"
                        ObjCity2.CityCode = "LA"
                        ObjCity2.CountryID = ObjCountry.CountryID

                        db.Cities.InsertOnSubmit(ObjCity2)


                        db.SubmitChanges()

        End Using
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

785 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question