Sunday 3 July 2011

Linq to Sql many to many delete

My current project is using linq2sql, we had a small problem deleting a record from a many to many relationship that used a link table which only contained 2 required foreign key columns.

product with productId
order with orderId
productOrder with 2 foreign keys productId and orderId

System.Data.Linq.DuplicateKeyException : Cannot add an entity with a key that is already in use.
System.InvalidOperationException : An attempt was made to remove a relationship between a product and a productOrder.

However, one of the relationship's foreign keys (productOrder.productId) cannot be set to null.

The trick was to add DeleteOnNull="true" to the association

to delete modify collections (many to many link tables)

<Table Name="dbo.product" Member="product">
  <Type Name="product">
    <Column Name="productId" Type="System.Int32" DbType="Int NOT NULL IDENTITY" IsPrimaryKey="true" IsDbGenerated="true" CanBeNull="false" />
    <Column Name="Title" Type="System.String" DbType="VarChar(255)" CanBeNull="true" />
    <Association Name="product_productOrder" Member="productOrders" ThisKey="productId" OtherKey="productId" Type="productOrder" DeleteOnNull="true"/>
  </Type>
</Table>

<Table Name="dbo.productOrders" Member="productOrders">
  <Type Name="productOrder">
    <Column Name="productId" Type="System.Int32" DbType="Int NOT NULL" IsPrimaryKey="true" CanBeNull="false" />
    <Column Name="orderId" Type="System.Int32" DbType="Int NOT NULL" IsPrimaryKey="true" CanBeNull="false" />
    <Association Name="product_productOrder" Member="productId" ThisKey="productId" OtherKey="productId" Type="product" IsForeignKey="true" DeleteOnNull="true"/>
    <Association Name="order_productOrder" Member="order" ThisKey="orderId" OtherKey="orderId" Type="order" IsForeignKey="true" DeleteOnNull="true"/>
  </Type>
</Table>

product.ProductOrders.Clear();
product.ProductOrders.AddRange(myNewProductOrders);

I found help on this matter here: http://blogs.msdn.com/b/bethmassi/archive/2007/10/02/linq-to-sql-and-one-to-many-relationships.aspx

No comments:

Post a Comment