ADO.net SQL Server 2012 - Query with composite key and MissingSchemaAction.AddWithKey incorrectly adds constraint

Question

This query, when run on ADO.net with MissingSchemaAction.AddWithKey throws exception:

Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.

Query:

SELECT map.GroupId, b.PersonId 
FROM [GroupPersonMap] as map
INNER JOIN [Person] AS b ON b.PersonId = map.PersonId
GROUP BY map.GroupId, b.PersonId 

Inspecting locals reveals that a unique constraint for PersonId has been added. Not only that, but running the same query in SQL Server Manager returns a result set without any warnings or errors. This exact code used to work on SQL Server 2005. Using SQL Server 2005, when running this query on ADO.net the query creates a composite constraint properly. Is this an upgrade issue?

As a side note, I know that setting EnforceConstraints = false provides a workaround. Ideally though, I'd like to solve this issue at its root.

Setup to reproduce:

CREATE TABLE [GroupPersonMap]
(
[GroupId] [int] NOT NULL,
[PersonId] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [GroupPersonMap] ADD CONSTRAINT [PK_GroupPersonMAP] PRIMARY KEY CLUSTERED  ([GroupId], [PersonId])

CREATE TABLE [Person]
(
[PersonId] [int] NOT NULL IDENTITY(1, 1),
[Val] INT
) ON [PRIMARY]
GO
ALTER TABLE [Person] ADD CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED  ([PersonId])

Then inserting values:

INSERT INTO [GroupPersonMap] 
SELECT 1, 1
UNION ALL
SELECT 2, 1

INSERT INTO [Person]
SELECT 1

Answer 1

You may find some helpful debugging advice in this post:

Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints

That said, the first thing that jumps out at me here is that all your joining and grouping are unnecessary. You have written

SELECT map.GroupId, b.PersonId 
FROM [GroupPersonMap] as map
INNER JOIN [Person] AS b ON b.PersonId = map.PersonId
GROUP BY map.GroupId, b.PersonId 

but you could get the identical results with

SELECT * FROM GroupPersonMap

Here's what I mean:

1) You are not selecting any field from table b (Person) except b.PersonId. But you know from your JOIN clause that the values of b.PersonId must equal map.PersonId, so there is no information coming from Person that isn't already in GroupPersonMap. So, we can strip out the JOIN:

SELECT map.GroupId, map.PersonId 
FROM [GroupPersonMap] as map
GROUP BY map.GroupId, map.PersonId 

2) But now, map.GroupId, map.PersonId is the exact primary key of that table, so we know that no actual aggregation will occur-- every combination of GroupId/PersonId is unique by definition. So, every row of input will "group to" one and only one row of output. This means we can strip out the GROUP BY clause as well:

SELECT map.GroupId, map.PersonId 
FROM [GroupPersonMap] as map

3) And now, what we have left in the SELECT clause is map.GroupId, map.PersonId-- that's all the fields in your table. So, you could simplify still further to just "SELECT * FROM GroupPersonMap", though in practice for production code, it's best to always enumerate the fields you want. (Specifying exactly what you want protects you against later schema changes.)

One more note about your JOIN as it is: when you join a table on its primary key, ideally you want to join on the entire key. (This is the "first order" of the famous "third normal form".) When a table has a composite key, that means including JOIN and/or WHERE criteria for every field in the key. Otherwise you can wind up with a semi-cartesian join, or, in cases like this, possible problems where the other half of the primary key is left unspecified. I would guess, if you wanted to leave your query as-is but still do away with your error, that you could do it simply by saying which group you're interested in (e.g. WHERE map.GroupId = xxxx). If you really do want a listing of all, then I'd say a simplified re-write without the unnecessary JOINing and GROUPing would be your best first line of attack.