• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1167
  • Last Modified:

Linq parent child insert causes ForeignKey error

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
bolt81
Asked:
bolt81
  • 3
1 Solution
 
Lord_DeathCommented:
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
 
bolt81Author Commented:
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
 
Lord_DeathCommented:
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
 
CodeCruiserCommented:
I usually use two submit changes. And you would use the SiteID in Address if the Site is the parent.
0
 
Lord_DeathCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now