Entity Framework many-to-many relationships and poorly defined keys

Here’s a new entry for the “stupid things on my part which weren’t obvious because of obscure error messages” book. Actually, the error message makes some sense in retrospect but then again, everything is always a lot clearer after the fact.

The scenario in this instance relates to the following three tables in ASafaWeb:

What these guys are describing is that when a log entry of a scan is created, it may have many entries of the X-Powered-By header (this comes through as a comma delimited collection). A typical way of normalising this relationship is to drop a mapping table in the middle, in this instance the “LogXPoweredByHeader”. The cardinality displayed above is just what we’d expect in this scenario.

When this model is persisted through into Entity Framework (version 4.1 in this case), the mapping table is automatically dropped as EF is able to model the header entries as a collection against the XPoweredByHeaders attribute. Here’s what we get (some entities have been excluded in the image for the sake of brevity):

So far, so good. For simplicity’s sake, here’s how a new log entry is being created:

var log = new Log();
// Set a bunch of other attributes on the log
var xPoweredByHeaders = db.XPoweredByHeaders.First();
log.XPoweredByHeaders.Add(xPoweredByHeaders);
db.Logs.AddObject(log);
db.SaveChanges();

As per the comment, a whole series of other attributes are being saved but I want to just focus on the XPoweredByHeaders attribute for now. I’m also just going to take the first one I can find in the XPoweredByHeader table to keep this really simple.

Here’s where it all goes wrong:

For the sake of SEO, here’s the important part of that errors again:

A value shared across entities or associations is generated in more than one location. Check that mapping does not split an EntityKey to multiple store-generated columns.

Huh?! What value am I sharing across entities or associations? And why is it being generated more than once? And I’m not aware of any splitting of entity keys. Very bizarre.

After a series of unsuccessful Googling and asking / complaining on Twitter, I decided to dig deeper into the table definition. Here’s the create script:

CREATE TABLE [dbo].[LogXPoweredByHeader](
    [LogId] [int] IDENTITY(1,1) NOT NULL,
    [XPoweredByHeaderId] [int] NOT NULL,
CONSTRAINT [PK_LogXPoweredByHeader] PRIMARY KEY CLUSTERED 
(
    [LogId] ASC,
    [XPoweredByHeaderId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY =
  OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

See the problem? It immediately jumped out at me and it’s on the second line of the statement; the “LogId” column has been specified as an identity column. Ah, there’s the light-bulb moment; how is this column going to hold a foreign key if it’s an identity?!

Clearly this wasn’t a conscious design decision and it’s more a case of the visual design surface in SSMS taking some liberties on my behalf. Of course it’s an easy fix though and as soon as I rectified the DB and updated the .edmx I did a quick diff against the last revision in source control (the deleted text is highlighted):

<Property Name="LogId" Type="int" Nullable="false"
StoreGeneratedPattern="Identity" />

So the StoreGeneratedPattern attribute was dropped and immediately everything started behaving just as I’d wanted it to in the first place. In hindsight, yes, it was a value that was being generated in more than one place but you’d think EF might have been a bit better at reporting it. I mean think about it: if a mapping table (EF knows it’s a mapping table), has two columns with each one being an FK to one of the tables in the map yet somehow one of them is also an identity (which EF also knows), wouldn’t it make sense to explain this in the error message? Hopefully the next person who Googles this will get a more direct answer by way of this post.