LINQ to SQL Primary Key requirement for inserts fails

Question

I'm working on better understanding Linq-to-SQL before using on a real project, so I'm playing with it on a little side project. I've a table that I'm trying to update via DataContext.SubmitChanges() and it's failing due to primary key constraints.

I have no control over the table itself (I can't add an actual ID column), so instead I've modified the Linq entity to use 3 columns as a complex key. The inserts are still failing, even though the key data is unique across both the update set and the existing data in the table, and I'm curious to know why. What am I missing that forces Linq to treat these values as non unique?

Note that I am not trying to solve the update problem itself - I've determined that in my situation DataContext.ExecuteCommand() will work just as well. I'm trying to grok where my understanding of Linq (or of my data) is wrong.

The table structure looks like this: OPRID (char(30)) DATE (DateTime) PROJECT_ID (char(15)) HOURS (decimal)

The last 3 columns are the complex key.

And here's some sample data, comma delimited: cschlege, 2009-1-5, 10100, 0.8 cschlege, 2009-1-5, 10088, 1.1 cschlege, 2009-1-5, 10099, 0.8 cschlege, 2009-1-5, 10088, 1.2

The SubmitChanges() consistently fails on the last of those rows, but given a key of DATE, PROJECT_ID, HOURS those rows should be unique. It doesn't fail on the 3rd row, which has a duplication in the HOURS column, but only when it reaches the duplicate PROJECT_ID. If I remove PROJECT_ID from the complex key then the insert fails on the 3rd row, however.

Why is LINQ not treating those rows as uniquely keyed?

Answer 1

Ah. The answer is that I'm not paying close enough attention to the backing store's rules. There's a different index that's actually failing.

Answer 2

Given your explanation I would guess that the HOURS (decimal) is the problem ... It must be mapped somehow with a different precision.

If 1.2 rounds to 1 then you have a problem with:

cschlege, 2009-1-5, 10088, 1 (2)

cschlege, 2009-1-5, 10088, 1 (4)

As you said, if you remove the project ID you end up with failure in the 3rd row:

cschlege, 2009-1-5, 10100, 0 (1)

cschlege, 2009-1-5, 10099, 0 (3)

Can you insert just the last row in the db and then confirm that is with 1.2?