Insert and Update

Feb 3, 2010 at 9:55 PM
Edited Feb 4, 2010 at 1:34 AM

I resolved my original issue but now have another issues.

I have a table structure of UnitMonth->PIVPatient->PIVSite

When I update the patient I set PIVPatient.PIVSites.Add(_PIVSites); I was trying to update Patient and it's list of PIVSites at the same time. The sites could be removed completely or added to. The problem with this is it adds them all to the db again.

How do I handle the changes to the PIVSite table at the same time? it is detached while it is being manipulated, do I do the same thing as I did to PIVPatient ... SynchroniseWithDataContext for each PIVSite? Not sure how to do that since it is a private static List<PIVSite> _pivSite


Feb 4, 2010 at 8:30 AM

Hi Dan,

A little confused here are you trying to remove the PIVSites?  If you are just trying to use PIVPatient.PIVSites.Remove(_PIVSites), this only detaches the PIVSites record from the Patient - it doesn't delete it (standard LINQ2SQL functionality).  If you want to remove it, use the SetAsDeletedOnSubmit() method on the entity.

If that's not what you are trying to do, then can you list in order the steps you are taking and the expected results (in point form)?



Feb 4, 2010 at 2:48 PM

The SetAsDeletedOnSubmit does help but not if the user just did some edits. _PIVSites not _PIVSite sorry. Also, PIVSites is already detached from PIVPatient.

  • Before the user clicks update _PIVSites is set to a datagrid
  • during edit, items can be removed,edited, deleted from _PIVSites
  • when the user clicks update I was to pass _PIVSites back in with its update, edit, and/or deletes.

I realize i may be trying to do too much at once and may need to do live update, edit, inserts to PIVSites but didn't want to until the user clicked update.

Thanks for your help.


Feb 4, 2010 at 9:51 PM

Hi again Dan,

Is PIVSites owned by PIVPatient (i.e. in the pyhsical database, PIVSites table has a field that is a FK to the PIVPatient table)?

If so, it should work as long as PIVSites are loaded when you load PIVPatient using the LoadWith Command when you retrieve the data.  That way there is a Parent-->Child ( relationship that can be tracked by LINQ2SQL EB.

If this is not the case, your LINQ to SQL DBML file may be the wrong way round, I'm saying this because PIVSites sounds a little like it's referenced data and not owned data (referenced meaning PIVSites is shared between many PIVPatients).  If this is the case then changes to PIVSites will not be tracked under PIVPatient, and you would need to handle PIVSite record update individually.





Feb 4, 2010 at 11:56 PM
Edited Feb 5, 2010 at 12:02 AM

In my first post I list my table structure as: UnitMonth->PIVPatient->PIVSite this is all one to many so PIVPatients can have many sites but a site can only have one patient. Your first statement was correct.

I think I just have to rework it a little so the gridview is linked to the patient.PIVSites instead of a variable i put at the top of the class that I pass patient.PIVSites to. That seemed like the best way to go at the time because it was reusable for new patient with no sites yet.

edit: Another problem with not putting site into it's own variable is I can't use it as a temporary store to remove/add/delete before the user updates. Let me know if that does not make sense.

Also, I do not see a loadwith command. Can you link any documentation to it?



Feb 5, 2010 at 12:40 AM

Hi Dan,

here you go..

Also, I have an example in the source provided with the LINQ2SQL Entity base, you can have a look there as well - it's fairly straight forward.

If you make UnitMonth (or Patient?) the LINQ 2 SQL Entity Base root, then why don't you just have that as the variable?  Don't forget you can also use the serialization functionality on the LINQ to SQL EB to store this state in session/viewstate between post backs.



Feb 5, 2010 at 3:42 AM
Edited Feb 5, 2010 at 3:57 AM

I've switched to using a session variable to store the patient data, and patient is the base root, I have also set DeferredLoadingEnabled = false; so I can reattach later but now I do not have the child entities, so PIVSites are no longer available.

I know that if I remove the DeferredLoadingEnabled it would have the values but then I would get "An attempt has been made to Attach or Add an entity that is not new, perhaps having been loaded from another DataContext.  This is not supported." when I synchronize the data context.

Is there something I am missing that would allow me to keep the patient->site relationship in the session variable and still be able to synchronise with data context later?

edit: I added the WithLoad to the context and get this error when I SubmitChanges (I already ran patient.SynchroniseWithDataContext(context, true);

An attempt was made to remove a relationship between a UnitMonth and a PIVPatient. However, one of the relationship's foreign keys (PIVPatient.unitMonthId) cannot be set to null.





Feb 5, 2010 at 9:48 AM

Hi Dan,

i've got all the instructions on how to do it on the Home Tab above - have a good read ;).

Also, make sure you see the link to my blog on the tricks with detached entities on the home Tab as well.

In regards to the latest error you are getting, I'll take a guess that your database scheme does not allow null on this column (PIVPatient.unitMonthId), and LINQ to SQL has generated it's DBML to reflect this - and stops it in the .NET code before it attempts the operation on the database.  If this is the case, you'll need to allow nulls in both locations (Database and DBML).  When you remove a relationship, it sets the FK on the child object to null to do this.



Feb 5, 2010 at 2:08 PM

Once I had the Withload option set, I forgot to remove the UnitMonth=null; so it cleared the fk. Everything works now.