Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Linq parent child insert causes ForeignKey error

Posted on 2011-09-16
5
Medium Priority
?
1,107 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Suggested Courses

604 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