insert new is trying to insert FK-related members

Jul 15, 2009 at 5:30 PM
Edited Jul 16, 2009 at 11:04 AM

All --

Please help with this insert-new issue, where the entity has several non-null FK members for code-table values.

//Why does this code...

Business.Entities.ProcessingRecord myProcessingRecord = new ProcessingRecord();
myProcessingRecord.SetAsChangeTrackingRoot(EntityState.New);
myProcessingRecord.SetAsInsertOnSubmit(false);

HeldCode myHeldCode = HeldCode.GetDefaultEx();
myProcessingRecord.HeldCodes = myHeldCode;

SoughtCode mySoughtCode = SoughtCode.GetDefaultEx();
myProcessingRecord.SoughtCodes = mySoughtCode;

StatusCode myStatusCode = StatusCode.GetDefaultEx();
myProcessingRecord.StatusCodes = myStatusCode;

Ssb mySsb = Ssb.GetDefaultEx();
myProcessingRecord.Ssbs = mySsb;

using (DataClassesContext myContext = new DataClassesContext(Business.Components.EntityHelper.GetConnectionString()))
{
 myContext.DeferredLoadingEnabled = false;
 myProcessingRecord.SynchroniseWithDataContext(myContext);
 myContext.SubmitChanges();
}

//...try to insert the FK-related entity and throw this RTE...

//Violation of UNIQUE KEY constraint 'UK_Held_Code_Name'. Cannot insert duplicate key in object 'dbo.Held_Code'.

Do you have any ideas?

Please advise.

Thank you.

-- Mark Kamoski

Coordinator
Jul 15, 2009 at 10:18 PM

Hi Mark,

I would suggest doing this:

...

using(..)

{

    TextWriter log = new StringWriter();

    myContext.Log = log;

    myContext.DeferredLoadingEnabled = false;

    myProcessingRecord.SynchroniseWithDataContext(myContext);

    myContext.SubmitChanges();

}

Then when it fails, have a look at the log variable which should containt the SQL Statements that were used against the database.

Either that or use SQL Profiler to get the script that was used.

From there you might be able to see what's going wrong.

Here's my guess - because you are assigning references to the ProcessingRecord (i.e. HeldCode, SoughtCode, StatusCode), and the processing record is new - it may be mistaking these as "New" objects and trying to re-insert them (as it has no idea of your intention because they have no changed tracking status at all and assumes because they are being added to a new object they are also new themselves).  Just put a line for each saying SetAsNoChangeOnSubmit(true) for each of the references and hopefully that will avoid the issue.

Cheers

Matt.

Jul 16, 2009 at 10:57 AM

Matt --

Regarding this...

>>>Just put a line for each saying SetAsNoChangeOnSubmit(true) for each of the references

...I have tried that and a few other things, to no avail, as follows...

//Test01. myHeldCode.SetAsNoChangeOnSubmit(true); //throws RTE: You cannot change the Entity State when the Entity is not change tracked

//Test02. myHeldCode.SetAsDeleteOnSubmit(true); //throws RTE: You cannot change the Entity State when the Entity is not change tracked

//Test03. myHeldCode.SetAsChangeTrackingRoot(true); //throws RTE: Violation of UNIQUE KEY constraint 'UK_DHS_Processing_Badge_Held_Code_Name'. Cannot insert duplicate key in object 'dbo.Dhs_Processing_Badge_Held_Code'.

//Test04. <nothing_set> //throws RTE: Violation of UNIQUE KEY constraint 'UK_DHS_Processing_Badge_Held_Code_Name'. Cannot insert duplicate key in object 'dbo.Dhs_Processing_Badge_Held_Code'.

...so I am still stuck.

For Test01 and Test02 and Test03, the RTE is thrown from DisconnectedLinq itself. As such, they are strictly forbidden. Therefore, it appears that they are not ever going work in this case.

For Test04, regarding profiling, I suspect that that since I am getting an error of...

>>>Violation of UNIQUE KEY constraint 'UK_Held_Code_Name'. Cannot insert duplicate key in object 'dbo.Held_Code'.

...it is most probably the case that Linq is trying to insert a new record.

If you have any more ideas, please do send them along.

Thank you.

-- Mark Kamoski

Jul 16, 2009 at 11:13 AM
Edited Jul 16, 2009 at 11:16 AM

Matt --

In the code that I posted above, do I need to Serialize/Deserialize the related objects?

For example, I am think that maybe I need to do something like this, where the new lines are in green...

Business.Entities.ProcessingRecord myProcessingRecord = new ProcessingRecord();
myProcessingRecord.SetAsChangeTrackingRoot(EntityState.New);
myProcessingRecord.SetAsInsertOnSubmit(false);

HeldCode myHeldCode = HeldCode.GetDefaultEx();
string myHeldCodeSerial = LINQHelper.SerializeEntity(myHeldCode);
myHeldCode = (HeldCode)LINQHelper.DeserializeEntity(myHeldCodeSerial, typeof(HeldCode));
myProcessingRecord.HeldCodes = myHeldCode;

SoughtCode mySoughtCode = SoughtCode.GetDefaultEx();
string mySoughtCodeSerial = LINQHelper.SerializeEntity(mySoughtCode);
mySoughtCode = (SoughtCode)LINQHelper.DeserializeEntity(mySoughtCodeSerial, typeof(SoughtCode));
myProcessingRecord.SoughtCodes = mySoughtCode;

StatusCode myStatusCode = StatusCode.GetDefaultEx();
string myStatusCodeSerial = LINQHelper.SerializeEntity(myStatusCode);
myStatusCode = (StatusCode)LINQHelper.DeserializeEntity(myStatusCodeSerial, typeof(StatusCode));
myProcessingRecord.StatusCodes = myStatusCode;

Ssb mySsb = Ssb.GetDefaultEx();
string mySsbSerial = LINQHelper.SerializeEntity(mySsb);
mySsb = (Ssb)LINQHelper.DeserializeEntity(mySsbSerial, typeof(Ssb));
myProcessingRecord.Ssbs = mySsb;

using (DataClassesContext myContext = new DataClassesContext(Business.Components.EntityHelper.GetConnectionString()))
{
 myContext.DeferredLoadingEnabled = false;
 myProcessingRecord.SynchroniseWithDataContext(myContext);
 myContext.SubmitChanges();
}

...what do you think?

Please advise.

Thank you.

-- Mark Kamoski

Jul 16, 2009 at 11:22 AM

Matt --

Regarding serialization and deseriization, I tried the code that I posted above, "".

The result is...

 //Test05. Use serialize and deserialize...

//string myHeldCodeSerial = LINQHelper.SerializeEntity(myHeldCode);

//myHeldCode = (HeldCode)LINQHelper.DeserializeEntity(myHeldCodeSerial, typeof(HeldCode));

//...throws RTE: Violation of UNIQUE KEY constraint 'UK_Held_Code_Name'. Cannot insert duplicate key in object 'dbo.Held_Code'.

...so that is not good news.

If you have ideas, then please post them.

Thank you.

-- Mark Kamoski

Coordinator
Jul 16, 2009 at 11:53 AM

Hi Mark,

Sorry to hear you are still having troubles.

1) In regards your tests, you need to put the SetAsNoChangeOnSubmit after you attach the object.  The reason it is being rejected is because it's state is "NotTracked", if you call SetAsNoChangeOnSubmit(true) after setting the reference (attaching) on your ProcessingRecord it should not reject it because it's change tracked, and this should take care of the unique constraint issue because it won't try and insert them as duplicates.

Example:

HeldCode myHeldCode = HeldCode.GetDefaultEx();

myProcessingRecord.HeldCodes = myHeldCode;

myProcessingRecord.HeldCodes.SetAsNoChangeOnSubmit(true);

2) Having a closer look at the code above, is myProcessingRecord.HeldCodes a collection of held codes or just a single one?  Please clarify?

3) You do not have to serialize your objects unless you want to store them for retrieval later (for example in a session or viewstate between ASP.NET page requests).

Cheers

Matt.

Jul 16, 2009 at 12:01 PM

Matt --

Regarding this...

>>>is myProcessingRecord.HeldCodes a collection of held codes or just a single one? 

...it is just a single one.

(The pluralization is mixed up a bit. It may be due to my error. It may be due to the error of a plugin that I am using. Or something else. I am certain, however, each realated object is a single.)

(I am working the "call SetAsNoChangeOnSubmit(true) after setting the reference" and will post details ASAP.)

Thank you.

-- Mark Kamoski

Jul 16, 2009 at 12:12 PM

Matt --

Regarding this...

>>>In regards your tests, you need to put the SetAsNoChangeOnSubmit after you attach the object.  The reason it is being rejected is because it's state is "NotTracked", if you call SetAsNoChangeOnSubmit(true) after setting the reference (attaching) on your ProcessingRecord it should not reject it because it's change tracked, and this should take care of the unique constraint issue because it won't try and insert them as duplicates.

...I have tried it and still have had no luck, as follows... 

   //Test06, code...

   Business.Entities.WorkProcessingRecord myProcessingRecord = new WorkProcessingRecord();
   myProcessingRecord.SetAsChangeTrackingRoot(EntityState.New);
   myProcessingRecord.SetAsInsertOnSubmit(false);

   WorkProcessingBadgeHeldCode myHeldCode = WorkProcessingBadgeHeldCode.GetDefaultEx();
   myProcessingRecord.WorkProcessingBadgeHeldCodes = myHeldCode;
   myProcessingRecord.WorkProcessingBadgeHeldCodes.SetAsNoChangeOnSubmit(true);

   WorkProcessingSuitabilitySoughtCode mySoughtCode = WorkProcessingSuitabilitySoughtCode.GetDefaultEx();
   myProcessingRecord.WorkProcessingSuitabilitySoughtCodes = mySoughtCode;
   myProcessingRecord.WorkProcessingSuitabilitySoughtCodes.SetAsNoChangeOnSubmit(true);

   WorkProcessingSuitabilityStatusCode myStatusCode = WorkProcessingSuitabilityStatusCode.GetDefaultEx();
   myProcessingRecord.WorkProcessingSuitabilityStatusCodes = myStatusCode;
   myProcessingRecord.WorkProcessingSuitabilityStatusCodes.SetAsNoChangeOnSubmit(true);

   SsbRecord mySsb = SsbRecord.GetDefaultEx();
   myProcessingRecord.SsbRecords = mySsb;
   myProcessingRecord.SsbRecords.SetAsNoChangeOnSubmit(true);

   //Test06, result...
   //failed: System.ApplicationException : You cannot change the Entity State when the Entity is not change tracked

...so the search goes on...

Please post more ideas if you have them.

Regardless, I appreciate your more than kind attention thus far.

Thank you.

-- Mark Kamoski

Coordinator
Jul 16, 2009 at 12:53 PM

Mark, this is really strange

this should work, perhaps a bug somewhere there...

Let me check the entity base code...

BTW, remove the myProcessingRecord.SetAsInsertOnSubmit(false) - this is unnecessary.

 

Also, which line exactly is it failing on?

Coordinator
Jul 16, 2009 at 12:54 PM

Also, are u using the latest entitybase?

Coordinator
Jul 16, 2009 at 1:16 PM

Questions:

1) Which line is it failing on?

2) Are you using the latest entity base 

3) When you get an object via xxxx.GetDefaultEx(), what is the LINQEntityState property of the returned object equal to?

4) What direction is the relationship with these objects

Example, on the dbml file does it look like:

WorkProcessingRecord-->BadgeHeldCode

OR

WorkProcessingRecord<--BadgeHeldCode

I'm thinking it's the later, which means that all the solutions I have come up with so far are not appropriate - sorry (hard to tell without DBML in front of me).

If possible, can you email a picture of one of the relationships of the DBML to me @gmail.com address which you already have?

 

 

 

Jul 16, 2009 at 1:23 PM

Mike --

Regarding this...

>>>BTW, remove the myProcessingRecord.SetAsInsertOnSubmit(false) - this is unnecessary.

...I have done that now.

(I am working on your other recent suggestions and questions.)

Thank you.

-- Mark Kamoski

Jul 16, 2009 at 1:30 PM

Mike --

Regarding this...

>>>Also, are u using the latest entitybase?

...the answer is "yes".

(I am still working on your other questions and suggestions and will reply ASAP.)

Thank you.

-- Mark Kamoski

Jul 16, 2009 at 2:01 PM

Mike --

Regading this...

>>> 1) Which line is it failing on?

...it is failing on myContext.SubmitChanges(), as follows...

using (DataClassesContext myContext = new DataClassesContext(Business.Components.EntityHelper.GetConnectionString()))
{
 myContext.DeferredLoadingEnabled = false;
 myProcessingRecord.SynchroniseWithDataContext(myContext);

 //It fails on this line...
 myContext.SubmitChanges();
}

Regarding this...

>>> 2) Are you using the latest entity base

...yes, I was sure but, for good measure, I just downloaded and updated and ran my Nunit tests and got the same results.

Regarding this...

>>> 3) When you get an object via xxxx.GetDefaultEx(), what is the LINQEntityState property of the returned object equal to?

...the LINQEntityState is "NotTracked" in all cases, as shown in the following...

Business.Entities.WorkProcessingRecord myProcessingRecord = new WorkProcessingRecord();
myProcessingRecord.SetAsChangeTrackingRoot(EntityState.New);

WorkProcessingBadgeHeldCode myHeldCode = WorkProcessingBadgeHeldCode.GetDefaultEx();
Debug.WriteLine("myHeldCode.LINQEntityState.ToString()='" + myHeldCode.LINQEntityState.ToString() + "'");
//myHeldCode.LINQEntityState.ToString()='NotTracked'
myProcessingRecord.WorkProcessingBadgeHeldCodes = myHeldCode;

WorkProcessingSuitabilitySoughtCode mySoughtCode = WorkProcessingSuitabilitySoughtCode.GetDefaultEx();
Debug.WriteLine("mySoughtCode.LINQEntityState.ToString()='" + mySoughtCode.LINQEntityState.ToString() + "'");
//mySoughtCode.LINQEntityState.ToString()='NotTracked'
myProcessingRecord.WorkProcessingSuitabilitySoughtCodes = mySoughtCode;

WorkProcessingSuitabilityStatusCode myStatusCode = WorkProcessingSuitabilityStatusCode.GetDefaultEx();
Debug.WriteLine("myStatusCode.LINQEntityState.ToString()='" + myStatusCode.LINQEntityState.ToString() + "'");
//myStatusCode.LINQEntityState.ToString()='NotTracked'
myProcessingRecord.WorkProcessingSuitabilityStatusCodes = myStatusCode;

SsbRecord mySsb = SsbRecord.GetDefaultEx();
Debug.WriteLine("mySsb.LINQEntityState.ToString()='" + mySsb.LINQEntityState.ToString() + "'");
//mySsb.LINQEntityState.ToString()='NotTracked'
myProcessingRecord.SsbRecords = mySsb;

Regarding this...

>>> 4) What direction is the relationship with these objects
>>> Example, on the dbml file does it look like:
>>> WorkProcessingRecord-->BadgeHeldCode
>>> OR
>>> WorkProcessingRecord<--BadgeHeldCode
>>> I'm thinking it's the later, which means that all the
>>>
solutions I have come up with so far are not appropriate -
>>> sorry (hard to tell without DBML in front of me).

...Yes, it is the "later"-- that is, it looks like this...

WorkProcessingRecord<--BadgeHeldCode

Regarding this...

>>>If possible, can you email a picture of one of the relationships of the DBML to me @gmail.com address which you already have?

...I have done that.

I have done that.

Given all that, what do you suggest?

Please advise.

Thank you.

-- Mark Kamoski

Jul 16, 2009 at 2:49 PM

Hi Mark and Matt,

 

I'm just jumping in here because this seems to be a similar issue to one I had posted back in May of 2008; Matt, you might remember this post.

I had a similar problem and inserted the lines of code below in the SyncroniseWithDataContext(DataContext targetDataContext, bool cascadeDelete) method:

                 else if ( entity.LinqEntityState == EntityState.New )
{
//SFD addition: if the entity's state is new, traverse non-new foreign keys and attach to datacontext to prevent insertion errors (duplicate primary keys)
if ( entity.LinqEntityState == EntityState.New )
{
LinqEntityBase fkProp;
foreach ( PropertyInfo fkPropInfo in _cacheAssociationFKProperties[entity.GetType( )].Values )
{
fkProp = fkPropInfo.GetValue( entity, null ) as LinqEntityBase;
if ( fkProp != null && fkProp.LinqEntityState != EntityState.New )
{
try
{
targetDataContext.GetTable( fkProp.GetType( ) ).Attach( fkProp, false );
}
catch
{
}
}
}
}
//End SFD addition
targetDataContext.GetTable( entity.GetEntityType( ) ).InsertOnSubmit( entity );
}

Maybe this might be of some value.

Cheers!

Stephan

 

 

Jul 16, 2009 at 3:27 PM

Stephan --

Great.

I made that change you noted.

Preliminary testing indicates that it works.

All the Nunit tests passed.

That is VERY good news so far.

I will add more details if I have them and if necessary and etc.

(I would be interested to hear Mike's assessment of this and whether or not the change could/should be rolled in the realase, etc.)

Thank you VERY much.

-- Mark Kamoski

Jul 16, 2009 at 4:24 PM

You're welcome, Mark. Glad I could be of help. Like you mentioned, we'll see if this is something that Matt would want to incorporate into the actual code base.

Cheers!

Stephan

Jul 16, 2009 at 4:32 PM
Edited Jul 17, 2009 at 1:30 PM

Stephan and Mike --

This is a follow-up to my post above, about testing the change that Stephan had suggested.

FYI, I have done some (fairly) complete Nunit-based testing on that entity for create and retrieve and update.

It seems to work fine.

I just wanted to let you know.

Update -- FYI, I am not using DisconnectedLinq for delete and I am doing delete a slightly different way. For details, see this other thread: http://linq2sqleb.codeplex.com/Thread/View.aspx?ThreadId=62661 

Thank you.

-- Mark Kamoski

Coordinator
Jul 16, 2009 at 9:35 PM

Hi Stephan,

Thanks, will have a look at the change, still not sure why it occurs but will resolve it for you soon.

I do remember something about this but thought I had resolved it a different way.

Mark - Sorry about the mix up, I thougth for some reasons the relationships were the other way before (for some reason).

Cheers

Matt

Jul 17, 2009 at 1:06 PM

Sounds good to me, Matt. Maybe you did resolve it another way, I didn't check actually; I know that when I downloaded your release 1.1 a few days ago, I just incorporated the six or seven tweaks I had created in that old version from 2008.

Cheers!

Stephan

Coordinator
Jul 23, 2009 at 10:55 AM

Hi Guys,

I've repeated the issue and confirmed that Stephan's fix does the job, so I've added this in to the latest source.

Thanks so much for finding the issue - I don't think I have found it before because I've always serialized/deserialized in this situation (loosing the attached entities) - though it kinda looks obvious now !

Cheers

Matt.

Jul 30, 2009 at 7:32 PM
mhunter wrote:

...I've added this in to the latest source...

Regarding that, Matt,...

I noticed your post is dated "Jul 23 at 6:55 AM".

I went the link http://linq2sqleb.codeplex.com/Release/ProjectReleases.aspx for Downloads.

I noticed the latest build is dated "Jul 13 2009".

So, I am wondering where the new version is?

Etc.

Please advise.

Thank you.

-- Mark Kamoski

 

Coordinator
Jul 30, 2009 at 9:43 PM

Hi Mark,

it's in this section:

http://linq2sqleb.codeplex.com/SourceControl/ListDownloadableCommits.aspx

Build 24047

You can download it from there.

Cheers

Matt.

Jul 31, 2009 at 1:07 PM
mhunter wrote:

...it's in this section:

http://linq2sqleb.codeplex.com/SourceControl/ListDownloadableCommits.aspx

Build 24047... You can download it from there..

Matt --

Great.

I got it, plugged it in, ran the unit tests, watched them all pass, and now am all set.

I appreciate your excellent help.

Thank you.

-- Mark Kamoski