title: Linq-to-sql POCO - insert fails because of NULL associations
body: <p>I have a table "Location" that has a foreign key relationship to a "Site". I am using linq to sql to try and add a new location record:</p>
  
  <pre><code>Location l = new Location
                   {
                       Description = text,
                       FloorId = 0,
                       IsCurrentLoc = false,
                       LastMove = DateTime.Now,
                       MoveId = 0,
                       SiteId = 1 
                   };
  
  LocationTable.InsertOnSubmit(l);
  LocationTable.Context.SubmitChanges();
  </code></pre>
  
  <p>However when I try to save the location row I see this error:</p>
  
  <pre><code>An attempt was made to remove a relationship between a Site and a Location. However, one of the relationship's foreign keys (Location.SiteId) cannot be set to null.
  </code></pre>
  
  <p>I've set the location siteid (site id 1 exists in the database). </p>
  
  <p>My linq-to-sql classes look like this:</p>
  
  <pre><code>[Table(Name = "Locations")]
  public class Location
  {
  
      private List&lt;InstallLocation&gt; _InstallLocations = new List&lt;InstallLocation&gt;();
  
  
      [Column(IsPrimaryKey = true, IsDbGenerated = true, AutoSync = AutoSync.OnInsert)]
      internal int LocationId { get; set; }
  
      [Association(ThisKey = "SiteId", OtherKey = "SiteId", IsForeignKey = true)]        
      public Site Site
      {
          get;
          set;
      }
  
      [AssociationAttribute(ThisKey = "LocationId", OtherKey = "LocationId")]
      public List&lt;InstallLocation&gt; InstallLocations
      {
          get
          {
              return this._InstallLocations;
          }
          set
          {
              this._InstallLocations = value;
          }
      }
  
  }
  </code></pre>
  
  <p><strong>EDIT - So I know why this happens but not how to fix it...</strong></p>
  
  <p>Thanks to <a href="http://social.msdn.microsoft.com/forums/en-US/linqprojectgeneral/thread/c8bc8448-38e5-435d-94af-3190b07ebac4" rel="nofollow">this</a> post I now see what is happening. The "Site" property takes presendese over SiteId. Since I don't set Site to anything it tries to set SiteId to null - which is not allowed hence it fails.</p>
  
  <p>What I can't quite understand is how to work with this. I don't want to load up the Site entity from the database and take a DB hit just to set the Site. I have the SiteId and that is all I should need in order to persist my "Location". </p>
  
  <p>Any ideas?</p>