none
Ling to SQL Server Compact 3.5: ForeignKeyReferenceAlreadyHasValueException RRS feed

  • Question

  • In my VB Windows Forms Application under VS2010 I am using Linq classes as an OR/M to handle CRUD operations on a simple SQL Server Compact database on the local desktop.

    I am working with two tables: 1) a table of houses (homes) and 2) a table of photos (images) related to the homes by a single foreignkey.  When inserting a new entity into the database, I always get the following exception when I attempt to submitChanges() on the datacontext:

    ForeignKeyReferenceAlreadyHasValueException was unhandled by user code
    Operation is not valid due to the current state of the object.

    My code (simplified for presentation) looks something like this:

    ' database of Homes and Images
    db = New DataContext(connString)
    db.ObjectTrackingEnabled = True
    ' tables of Homes and Pictures
    homeTbl = db.GetTable(Of HomeData)()
    imgTbl = db.GetTable(Of Picture) ()
    ' new property record and photo
    dim home as new HomeData with
    	{
    	.Address = "1234 Main Street"
    	.City = "Boston"
    	..... additional object properties
    	.Value = 200000
    	.Picture = new Picture with {
    		.Image = Picturebox1.image
    		.Caption = "1234 Main Street"
    		..... additional object properties
    		.Date = "12/1/2010" }
    	}
    'insert	new record in database
    homeTbl.insertOnSubmit(home)
    db.submitChanges()					<----------- Exception occurs here

    I don't understand why I am getting this error, as I have intentionally not populated the ID or foreign key reference fields of either record.  I have also tried setting datacontext object tracking false, but then the submit() doesn't work.

    What is the appropriate method of inserting related entities?  I have tried with/without explicit creation of the new() photo, but the only approach I found that works is to:

    1) turn object tracking off, 2) insert the related record, 3) get its ID, 4) write the primary record with the related ID, 5) read the primary ID record#, 6) then update the related record. That's a total of two inserts and one update to set up the foreign key relationship, and just about the same way it had to be done with ADO.NET.

    If Linq is so smart, why can't it write the primary record, related record, and calculate the associations in one pass.  What am I missing?

    Thanks for any assistance.


    -BGood

    • Edited by BGood Sunday, October 16, 2011 4:28 AM
    Saturday, October 15, 2011 10:18 PM

Answers

  • Hi Alan,

    Sorry, but the syntax you are using does not look familiar to me.  This may be because I am using Linq to SQL and it looks like your code may be for Linq to Entities.  Is this correct?

    I got the code working using the old ADO approach described in my follow-up post, but I am still looking for a more concise Linq approach.

    Thanks.


    -BGood
    Saturday, October 22, 2011 2:42 AM

All replies

  • After attacking this again for the nth time, my conclusion is that there should be an easier way, but this is what works:

    1) objectTracking.enabled must be TRUE on the datacontext
    2) mark the parent table object for insertion: <parentTable>.insertOnSubmit(parentObject)
    3) update the datacontext: <datacontext>.submitChanges()
    4) mark the child table object for insertion: <childTable>.insertOnSubmit(childObject), including any desired back-references to the parentObject.ID which is now known
    5) update the datacontext: <datacontext>.submitChanges()
    6) assign the parentObject foreign key reference which is now known: parentObject.<ForeignKeyField> = childObject.ID
    7) update the datacontext: <datacontext>.submitChanges()

    This seems a lot like ADO.NET to me and I think there should be some way for Linq to do this on its own.


    -BGood
    Sunday, October 16, 2011 3:56 PM
  • Hi BGood,

    Welcome, thanks for your question here!

    I think your Have entity will have Photos property of "System.Data.Linq.EntitySet<Photo>" type, right? You can try:

    1. new a parentTable --->var home= new Home(){Image= new EntitySet<photo>};

    2. home.Image.add(new photo(){});

    3. Context.Homes.InsertOnSubmit(home);

    4. Savechanges

    Have a nice day.

     


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, October 18, 2011 6:35 AM
    Moderator
  • Hi,

    I am writing to check the status of the issue on your side. Would you mind letting us know the result of the suggestions?

    If you need further assistance, please feel free to let me know. I will be more than happy to be of assistance.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, October 20, 2011 8:56 AM
    Moderator
  • Hi Alan,

    Sorry, but the syntax you are using does not look familiar to me.  This may be because I am using Linq to SQL and it looks like your code may be for Linq to Entities.  Is this correct?

    I got the code working using the old ADO approach described in my follow-up post, but I am still looking for a more concise Linq approach.

    Thanks.


    -BGood
    Saturday, October 22, 2011 2:42 AM
  • Hi BGood,

    I think you can refer the 1 to many insertion in LINQ to SQL:

    http://msdn.microsoft.com/en-us/vbasic/bb737928#ins1tomany

    To tell you truth, I'm not good at VB.NET syntax, thanks for understanding.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, October 24, 2011 7:37 AM
    Moderator