Ran into an odd behavior today with a many
to many mapping of one of my tables in
LINQ to SQL. Many
to many mappings aren’t transparent in
LINQ to SQL and it maps the link table the same way the
SQL schema has it when creating one. In other words
LINQ to SQL isn’t smart about many
to many mappings and just treats it like the 3 underlying tables that make up the many
to many relationship. Iain Galloway has a nice blog entry about Many
to Many relationships in
LINQ to SQL. I can live with that – it’s not really difficult
to deal with this arrangement once mapped, especially when reading data back. Writing is a little more difficult as you do have
to insert into two entities for new records, but nothing that can’t be handled in a small business object method with a few lines of code. When I created a database I’ve been using
to experiment around with various different OR/Ms recently I found that for some reason
LINQ to SQL was completely failing
to map even
to the linking table. As it turns out there’s a good reason why it fails, can you spot it below? (read on :-}) Here is the original database layout: There’s an items table, a category table and a link table that holds only the foreign keys
to the Items and Category tables for a typical M->M relationship. When these three tables are imported into the model the *look* correct – I do get the relationships added (after modifying the entity names
to strip the prefix): The relationship looks perfectly fine, both in the designer as well as in the XML document: <Table Name="dbo.wws_Item_Categories" Member="ItemCategories">
<Type Name="ItemCategory">
<Column Name="ItemId" Type="System.Guid" DbType="uniqueidentifier NOT NULL" CanBeNull="false" />
<Column Name="CategoryId" Type="System.Guid" DbType="uniqueidentifier NOT NULL" CanBeNull="false" />
<Association Name="ItemCategory_Category" Member="Categories" ThisKey="CategoryId" OtherKey="Id" Type="Category" />
<Association Name="Item_ItemCategory" Member="Item" ThisKey="ItemId" OtherKey="Id" Type="Item" IsForeignKey="true" />
</Type>
</Table>
<Table Name="dbo.wws_Categories" Member="Categories">
<Type Name="Category">
<Column Name="Id" Type="System.Guid" DbType="UniqueIdentifier NOT NULL" IsPrimaryKey="true" IsDbGenerated="true" CanBeNull="false" />
<Column Name="ParentId" Type="System.Guid" DbType="UniqueIdentifier" CanBeNull="true" />
<Column Name="CategoryName" Type="System.String" DbType="NVarChar(150)" CanBeNull="true" />
<Column Name="CategoryDescription" Type="System.String" DbType="NVarChar(MAX)" CanBeNull="true" />
<Column Name="tstamp" AccessModifier="Internal" Type="System.Data.
Linq.Binary" DbType="rowversion" CanBeNull="true" IsVersion="true" />
<Association Name="ItemCategory_Category" Member="ItemCategory" ThisKey="Id" OtherKey="CategoryId" Type="ItemCategory" IsForeignKey="true" />
</Type>
</Table>
However when looking at the code generated these navigation properties (also on Item) are completely missing:
[global::System.Data.
Linq.Mapping.TableAttribute(Name="dbo.wws_Item_Categories")]
[global::System.Runtime.Serialization.DataContractAttribute()]
public partial class ItemCategory : Westwind.BusinessFramework.EntityBase
{
private System.Guid _ItemId;
private System.Guid _CategoryId;
public ItemCategory()
{
}
[global::System.Data.
Linq.Mapping.ColumnAttribute(Storage="_ItemId", DbType="uniqueidentifier NOT NULL")]
[global::System.Runtime.Serialization.DataMemberAttribute(Order=1)]
public System.Guid ItemId
{
get
{
return this._ItemId;
}
set
{
if ((this._ItemId != value))
{
this._ItemId = value;
}
}
}
[global::System.Data.
Linq.Mapping.ColumnAttribute(Storage="_CategoryId", DbType="uniqueidentifier NOT NULL")]
[global::System.Runtime.Serialization.DataMemberAttribute(Order=2)]
public System.Guid CategoryId
{
get
{
return this._CategoryId;
}
set
{
if ((this._CategoryId != value))
{
this._CategoryId = value;
}
}
}
}
Notice that the Item and Category association properties which should be EntityRef properties are completely missing. They’re there in the model, but the generated code – not so much.
So what’s the problem here?
The problem – it appears – is that
LINQ to SQL requires primary keys on all entities it tracks. In order
to support tracking – even of the link table entity – the link table requires a primary key. Real obvious ain’t it, especially since the designer happily lets you import the table and even shows the relationship and implicitly the related properties.
Adding an Id field as a Pk
to the database and then importing results in this model layout:
which properly generates the Item and Category properties into the link entity.
It’s ironic that
LINQ to SQL *requires* the PK in the middle – the Entity Framework requires that a link table have *only* the two foreign key fields in a table in order
to recognize a many
to many relation. EF actually handles the M->M relation directly without the intermediate link entity unlike
LINQ to SQL.
[updated from comments – 12/24/2009]
Another approach is
to set up both ItemId and CategoryId in the database which shows up in
LINQ to SQL like this:
This also work in creating the Category and Item fields in the ItemCategory entity. Ultimately this is probably the best approach as it also guarantees uniqueness of the keys and so helps in database integrity.
It took me a while
to figure out WTF was going on here – lulled by the designer
to think that the properties should be when they were not. It’s actually a well documented feature of L2S that each entity in the model requires a Pk but of course that’s easy
to miss when the model viewer shows it
to you and even the underlying XML model shows the Associations properly.
This is one of the issue with L2S of course – you have
to play by its rules and once you hit one of those rules there’s no way around them – you’re stuck with what it requires which in this case meant changing the database.© Rick Strahl, West Wind Technologies, 2005-2010Posted in ADO.NET LINQ
