Databases
StackOverflow2013
Postsdbo
PORemoving FK when there are no related record (linq-to-sql)
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PORemoving FK when there are no related record (linq-to-sql)
    primarykey
    data
    text
    <h2>Problem</h2> <p>I got two related tables, with a foreign key that doesn't enforce FK contraints. The "child" table should be allowed to specify a "parent" key, which is either NULL or non-existing ID.</p> <p>My data access is built using Linq2Sql and it generates a one-to-many relation in the model of my tables.</p> <p>The problem comes in some cleanup code, which will daily look through all the changes for the last day and fix any "errors" in the data.</p> <h2>Example</h2> <pre><code>foreach (var user in data.Users) { // Check if the user has a specified office, which does not exists. if (user.OfficeId != null &amp;&amp; user.Office == null) { user.OfficeId = null; // This throws exception } } </code></pre> <p>Exception: System.Data.Linq.ForeignKeyReferenceAlreadyHasValueException: Operation is not valid due to the current state of the object</p> <p>This is normal behavior and somewhat expected, so I tried to set the reference object instead. But how do I define an "empty" Office?</p> <pre><code>var emptyOffice = new Office(); user.Office = emptyOffice; </code></pre> <p>The above code sets the OfficeId to NULL on the user instance, but obviously it fails during update as it tries to add a new "empty" Office to the database.</p> <h2>Possible solution</h2> <p>What I'm left with is running my own SQL which does the update, though it would be nice to actually be able to do this through Linq2Sql as there are other columns on the same row I'm updating when the related "parent" is non-existing.</p> <h2>Notes</h2> <p>There are some requirements here that is important for any comments:</p> <ul> <li>The database cannot be changed.</li> <li>Multiple systems is dependent on the same database schema.</li> <li>Can potentially change to Entity Framework, if it supports such a scenario.</li> <li>Running a custom SQL is not a problem and I'll be doing that until I find a better solution.</li> <li>Would like to learn and know how to do this without any special code.</li> </ul>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USSondreB
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. PORemoving FK when there are no related record (linq-to-sql)
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PORemoving FK when there are no related record (linq-to-sql)
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PORemoving FK when there are no related record (linq-to-sql)
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload