SQL Server : ADO.Net GetUpdateCommand ok with PK, fails after adding another clustered unique index
<p>I have such a table with a non-clustered primary key:</p>

<pre><code>CREATE TABLE [dbo].[StudentGrade](
    [EnrollmentID] [int] IDENTITY(1,1) NOT NULL,
    [CourseID] [nvarchar](10) NOT NULL,
    [StudentID] [int] NOT NULL,
    [Grade] [decimal](3, 2) NOT NULL,
CONSTRAINT [PK_StudentGrade] PRIMARY KEY NONCLUSTERED ([EnrollmentID] ASC))
</code></pre>

<p>The select statement is:</p>

<pre><code>select EnrollmentID,Grade from StudentGrade
</code></pre>

<p>GetUpdateCommand works fine, the non-clustered primary key is recognized.</p>

<p>When adding another clustered unique index (on the both columns not even part of the select clause):</p>

<pre><code>CREATE UNIQUE CLUSTERED INDEX [Badguy] ON [dbo].[StudentGrade] ([CourseID] ASC, [StudentID] ASC)
</code></pre>

<p>GetUpdateCommand fails with this exception:</p>

<pre><code>System.InvalidOperationException: Dynamic SQL generation for the
UpdateCommand is not supported against a SelectCommand that does not return
any key column information.
</code></pre>

<p>If the index is not unique OR not clustered, there is no error.</p>

<p>It looks like the clustered unique index is preferred against the primary key for describing metadata. And if there are several key candidates (pk / unique index), it does not try to use the one having all columns in the query.</p>

<p>Is this an expected behaviour ? Any simple way to fix apart from selecting indexed columns or expliciting the update command ?</p>

<p>I need to have this clustered index for performance purposes, the primary key is not often used in queries.</p>