in entity framework code first, why is the key not unique

Question

I have used the methods specified here to create composite keys. SQL Server calls it a PrimaryKey, but it is not unique (!). Is there a way to specify uniqueness in attributes or fluent API? I've found several hacks here but this should be possible to do upfront...

The non unique value combinations are a result of an SQLBulkCopy operation. Is it possible this is the reason?

[edit] my assumptions were wrong - read on to my answer.

score 2 · Accepted Answer · answered Feb 14 '11 at 10:13

My mistake! The keys are unique, including composite keys. My problem was in the column mapping of the SqlBulCopy class. I was doing

Public Sub DoBulKCopy(dt As DataTable, cns As String)
    Dim cn As New SqlConnection(cns)
    cn.Open()
    Dim copy As New SqlBulkCopy(cn)
    For i As Integer = 0 To dt.Columns.Count - 1
        copy.ColumnMappings.Add(i,i)
    Next

While I should have been doing

Public Sub DoBulKCopy(dt As DataTable, cns As String)
    Dim cn As New SqlConnection(cns)
    cn.Open()
    Dim copy As New SqlBulkCopy(cn)
    For i As Integer = 0 To dt.Columns.Count - 1
        copy.ColumnMappings.Add(dt.Columns(i).ColumnName, dt.Columns(i).ColumnName)
    Next

And not assuming the column order is the same.

HTH

score 1 · Answer 2 · answered Feb 14 '11 at 08:12

1

The uniqueness in a composite key is the combined values of all the keys. You can have repeated values in any single column, but all the key columns as a whole cannot be repeated as a sequence.

answered Feb 14 '11 at 08:12

I am aware of that. But after an SQLBulkLoad operation, I am able to get combined values which are not unique. Now that I think about it, this may be the result of the SQLBulkLoad itself... I'll edit. – GilShalit Feb 14 '11 at 08:27

in entity framework code first, why is the key not unique

2 Answers2