Linq-to-sql POCO - insert fails because of NULL associations

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:

Location l = new Location
                 {
                     Description = text,
                     FloorId = 0,
                     IsCurrentLoc = false,
                     LastMove = DateTime.Now,
                     MoveId = 0,
                     SiteId = 1 
                 };

LocationTable.InsertOnSubmit(l);
LocationTable.Context.SubmitChanges();

However when I try to save the location row I 开发者_开发技巧see this error:

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.

I've set the location siteid (site id 1 exists in the database).

My linq-to-sql classes look like this:

[Table(Name = "Locations")]
public class Location
{

    private List<InstallLocation> _InstallLocations = new List<InstallLocation>();


    [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<InstallLocation> InstallLocations
    {
        get
        {
            return this._InstallLocations;
        }
        set
        {
            this._InstallLocations = value;
        }
    }

}

EDIT - So I know why this happens but not how to fix it...

Thanks to this 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.

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".

Any ideas?

You may need to implement on Site property:

DeleteOnNull="true"

Obtained from this link:

http://blogs.msdn.com/b/bethmassi/archive/2007/10/02/linq-to-sql-and-one-to-many-relationships.aspx

Found the solution for this.

Basically, my original posting was close. What I needed to do was to allow my Foreign key property to be nullable in my class. This seems counter-intuitive since its not nullable in the DB, but L2S will set this to null before setting it to the correct value later..

Full working code looks like this, showing how Location is associated with Site:

[Table(Name = "Locations")]
public class Location
{

    [Column(IsPrimaryKey = true, IsDbGenerated = true, AutoSync = AutoSync.OnInsert)]
    internal int LocationId { get; set; }

    [Column]        
    public int? SiteId { get; set; }

    [Association(ThisKey = "SiteId", OtherKey = "SiteId", IsForeignKey = true)]        
    public Site Site
    {
        get;
        set;
    }

}