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.