Rick Strahl's Weblog  

Wind, waves, code and everything in between...
.NET • C# • Markdown • WPF • All Things Web
Contact   •   Articles   •   Products   •   Support   •   Advertise
Sponsored by:
Markdown Monster - The Markdown Editor for Windows

LINQ to SQL and missing Many to Many EntityRefs


:P
On this page:

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:

Schema

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):

Model

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:

ModelLinkWithPk

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:

CompoundPrimary Key

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.

Posted in ADO.NET  LINQ  

The Voices of Reason


 

Hugo
December 24, 2009

# re: LINQ to SQL and missing Many to Many EntityRefs

Hi Rick, why don't you have the primary key on table ItemCategory set to columns ItemId and CategoryId?

Augustlights
December 24, 2009

# re: LINQ to SQL and missing Many to Many EntityRefs

Agreed, there's no reason that linking table should not have a PK (which would have prevented your problem with the missing properties), since that's the way most db modelers would implement this. Otherwise, I'd be basing my database model on the ORM du jour. Sucks though that EF seems to think differently...

Damien Guard
December 24, 2009

# re: LINQ to SQL and missing Many to Many EntityRefs

I'd agree with the other posters here that the PK for ItemCategory should be ItemId & CategoryID. This is how I would model the database and LINQ to SQL would be happy with it.

It also enforces a useful constraint which is that while Item to Category is many-to-many and both Items and Categorys can have multiple entries there should only be a single row for an Item & Category combination - e.g. while you can have 1,1 1,2 and 2,1 you shouldn't have another 1,1 in there.

[)amien

Rick Strahl
December 24, 2009

# re: LINQ to SQL and missing Many to Many EntityRefs

Thanks for the comments - didn't realize that the compound key would allow L2S to work as expected - I've updated the post with the info and changed my code accordingly as well.

Steve
December 26, 2009

# re: LINQ to SQL and missing Many to Many EntityRefs

This raises some choices. Database design vs. making the ORM and LINQ behave.

Without a Primary Key/clustered index, the table is a "heap". Adding a PK/clustered index on both ItemId and CategoryId solves your ORM/LINQ problem, but then hopefully this table doesn't have many deletes.

The CI will force a physical order on the database pages -- adding rows in a non-sorted order will force SQL Server to work more. Deleting and inserting will cause fragmentation and page splits. For non-rapid inserts and intense OLTP processing this will usually be OK.

If Items logically belong to a Category, then putting CategoryId before ItemId may be important; that way if you read all Items for a particular Category they would hopefully all exist on 1 page, thus reducing your reads.

Another (probably less desirable) option would be to add a new PK, and put a non-clustered index on CategoryId and ItemId. This way all of your inserts would be at the end of the newest page, but CategoryId and related ItemIds could be scattered across multiple pages, which would be inefficient.

A good article here: http://www.mssqltips.com/tip.asp?tip=1254

Dennis Bailey
December 27, 2009

# re: LINQ to SQL and missing Many to Many EntityRefs

I want to make sure I'm not missing something here. I'm playing around with something similar, but in our databases all of the tables, including those that have two foreign keys always have a single unique key to denote a record. For example:

wws_item_categories table would have three keys
wwsitcpk
wwsitemidfk
wwscategoryidfk

Where pk would denote Primary Key and fk denotes foreign keys .. Then I would only use the wwsitcpk ... Are you saying this does not work in L2S? Or that it doesn't understand how to use it that way?

I was taught by old school dba's and until I started reading Microsoft docs did I ever see two FK's alone with out a Primary key..

Thanks,
Dennis

Steve
December 27, 2009

# re: LINQ to SQL and missing Many to Many EntityRefs

Dennis,

A (perhaps unfortunate) default action of the 'Set Primary Key' button in the SQL Server designer is that it automatically puts a Clustered Index on the field as well (as long as a CI does not already exist). And so we tend to merge the ideas of PK and CI as being one and the same, but they of course are not.

A field that is used to guarantee uniqueness (PK) does not necessarily require also being a CI. In fact, depending on the activity of the table, a CI may inhibit performance due to the physical sorting of pages, e.g., high volume/speed inserts can be inhibited by badly chosen CIs while waiting for page-splits at the wrong time.

If wwsitemidfk and wwscategoryidfk combinations are always (intended to be) unique and the desire is to physically force this constraint, then put the PK on these two fields, and very probably make it a CI as well.

There's DBA theory, business/application concerns, and now what helps make L2S work!

Fred
December 29, 2009

# re: LINQ to SQL and missing Many to Many EntityRefs

Hi

Rick, could you please tell me what's the name of your blogging engine?

Kevin Wright
December 29, 2009

# re: LINQ to SQL and missing Many to Many EntityRefs

FWIW, since you said you are also looking at other ORMs, SubSonic (certainly v2.x - haven't tried v3) also requires all tables to have PKs before it will generate its classes.

Mark Kadlec
December 30, 2009

# re: LINQ to SQL and missing Many to Many EntityRefs

I guess this is the final push to have a primary key <i>on every single table</i>. Probably a good rule, if any table doesn't have one it should raise suspicion, good to know in the future when using Linq to SQL.

Blake Niemyjski
March 23, 2011

# re: LINQ to SQL and missing Many to Many EntityRefs

I'd like to add that the CodeSmith Generator PLINQO (Extends Linq-to-SQL) templates support Many-to-Many relationships out of the box (http://www.codesmithtools.com/product/frameworks/plinqo/tour/manytomany). All that you need to do to get this support in existing projects is add a CodeSmith Generator project file to your solution. Then point it to the PLINQO templates and we will generate a new or update the existing DBML file; while preserving your custom DBML changes. The templates will also generate new entities that will have all of the relationships defined. This saves you countless hours from discovering and applying all of the Many to Many work arounds.

West Wind  © Rick Strahl, West Wind Technologies, 2005 - 2024