SQL Server: ADO.Net GetUpdateCommand ok with PK，失败后添加另一个集群唯一
本文关键字：添加 失败 另一个 唯一 PK ADO Server Net GetUpdateCommand with ok | 更新日期: 2023-09-27 18:07:26
我有一个非集群主键的表:
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))
select语句为:
select EnrollmentID,Grade from StudentGrade
GetUpdateCommand工作正常，非集群主键被识别。
当添加另一个集群唯一索引时(在两个列上，甚至不是select子句的一部分):
CREATE UNIQUE CLUSTERED INDEX [Badguy] ON [dbo].[StudentGrade] ([CourseID] ASC, [StudentID] ASC)
GetUpdateCommand失败，出现以下异常:
System.InvalidOperationException: Dynamic SQL generation for the
UpdateCommand is not supported against a SelectCommand that does not return
any key column information.
如果索引不是唯一的或没有聚集，则没有错误。
在描述元数据时，似乎首选集群唯一索引，而不是主键。如果有几个关键候选者(pk/unique index)，它不会尝试使用包含查询中所有列的那个。
这是预期的行为吗?除了选择索引列或显式更新命令之外，还有什么简单的修复方法吗?
出于性能考虑，我需要这个聚集索引，主键不常用于查询。
看起来聚集的唯一索引是首选的描述元数据的主键。
是的，这就是问题的关键。你可以用sp_describe_first_result_set看到这一点。在只有非集群主键的情况下，enroll列是标识为"is_part_of_unique_key"的列:
EXEC sp_describe_first_result_set
@tsql = N'SELECT EnrollmentID,Grade FROM StudentGrade'
, @params = NULL
, @browse_information_mode = 1;
+-----------+----------------+--------------+-------------+----------------+------------------+------------+-----------+-------+----------------+--------------+--------------------+------------------+----------------+------------------------------+-------------------+-------------------------+-----------------------+---------------------+-----------------+-------------------+--------------------------+---------------+-----------------+---------------+--------------+---------------+--------------------+-----------------------+---------------+--------------------+----------------------+--------------------------+------------------------+----------------------+-------------+------------+------------------+-----------------------+
| is_hidden | column_ordinal | name | is_nullable | system_type_id | system_type_name | max_length | precision | scale | collation_name | user_type_id | user_type_database | user_type_schema | user_type_name | assembly_qualified_type_name | xml_collection_id | xml_collection_database | xml_collection_schema | xml_collection_name | is_xml_document | is_case_sensitive | is_fixed_length_clr_type | source_server | source_database | source_schema | source_table | source_column | is_identity_column | is_part_of_unique_key | is_updateable | is_computed_column | is_sparse_column_set | ordinal_in_order_by_list | order_by_is_descending | order_by_list_length | tds_type_id | tds_length | tds_collation_id | tds_collation_sort_id |
+-----------+----------------+--------------+-------------+----------------+------------------+------------+-----------+-------+----------------+--------------+--------------------+------------------+----------------+------------------------------+-------------------+-------------------------+-----------------------+---------------------+-----------------+-------------------+--------------------------+---------------+-----------------+---------------+--------------+---------------+--------------------+-----------------------+---------------+--------------------+----------------------+--------------------------+------------------------+----------------------+-------------+------------+------------------+-----------------------+
| 0 | 1 | EnrollmentID | 0 | 56 | int | 4 | 10 | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 0 | 0 | 0 | NULL | tempdb | dbo | StudentGrade | EnrollmentID | 1 | 1 | 0 | 0 | 0 | NULL | NULL | NULL | 56 | 4 | NULL | NULL |
| 0 | 2 | Grade | 0 | 106 | decimal(3,2) | 5 | 3 | 2 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 0 | 0 | 0 | NULL | tempdb | dbo | StudentGrade | Grade | 0 | 0 | 1 | 0 | 0 | NULL | NULL | NULL | 106 | 17 | NULL | NULL |
+-----------+----------------+--------------+-------------+----------------+------------------+------------+-----------+-------+----------------+--------------+--------------------+------------------+----------------+------------------------------+-------------------+-------------------------+-----------------------+---------------------+-----------------+-------------------+--------------------------+---------------+-----------------+---------------+--------------+---------------+--------------------+-----------------------+---------------+--------------------+----------------------+--------------------------+------------------------+----------------------+-------------+------------+------------------+-----------------------+
在组合自然键列上添加唯一聚类索引后，CourseID和StudentID成为客户端api首选的唯一键。这些被标记为"is_part_of_unique_key"的隐藏元数据列返回:
+-----------+----------------+--------------+-------------+----------------+------------------+------------+-----------+-------+------------------------------+--------------+--------------------+------------------+----------------+------------------------------+-------------------+-------------------------+-----------------------+---------------------+-----------------+-------------------+--------------------------+---------------+-----------------+---------------+--------------+---------------+--------------------+-----------------------+---------------+--------------------+----------------------+--------------------------+------------------------+----------------------+-------------+------------+------------------+-----------------------+
| is_hidden | column_ordinal | name | is_nullable | system_type_id | system_type_name | max_length | precision | scale | collation_name | user_type_id | user_type_database | user_type_schema | user_type_name | assembly_qualified_type_name | xml_collection_id | xml_collection_database | xml_collection_schema | xml_collection_name | is_xml_document | is_case_sensitive | is_fixed_length_clr_type | source_server | source_database | source_schema | source_table | source_column | is_identity_column | is_part_of_unique_key | is_updateable | is_computed_column | is_sparse_column_set | ordinal_in_order_by_list | order_by_is_descending | order_by_list_length | tds_type_id | tds_length | tds_collation_id | tds_collation_sort_id |
+-----------+----------------+--------------+-------------+----------------+------------------+------------+-----------+-------+------------------------------+--------------+--------------------+------------------+----------------+------------------------------+-------------------+-------------------------+-----------------------+---------------------+-----------------+-------------------+--------------------------+---------------+-----------------+---------------+--------------+---------------+--------------------+-----------------------+---------------+--------------------+----------------------+--------------------------+------------------------+----------------------+-------------+------------+------------------+-----------------------+
| 0 | 1 | EnrollmentID | 0 | 56 | int | 4 | 10 | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 0 | 0 | 0 | NULL | tempdb | dbo | StudentGrade | EnrollmentID | 1 | 0 | 0 | 0 | 0 | NULL | NULL | NULL | 56 | 4 | NULL | NULL |
| 0 | 2 | Grade | 0 | 106 | decimal(3,2) | 5 | 3 | 2 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 0 | 0 | 0 | NULL | tempdb | dbo | StudentGrade | Grade | 0 | 0 | 1 | 0 | 0 | NULL | NULL | NULL | 106 | 17 | NULL | NULL |
| 1 | 3 | CourseID | 0 | 231 | nvarchar(10) | 20 | 0 | 0 | SQL_Latin1_General_CP1_CI_AS | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 0 | 0 | 0 | NULL | tempdb | dbo | StudentGrade | CourseID | 0 | 1 | 1 | 0 | 0 | NULL | NULL | NULL | 231 | 20 | 13632521 | 52 |
| 1 | 4 | StudentID | 0 | 56 | int | 4 | 10 | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 0 | 0 | 0 | NULL | tempdb | dbo | StudentGrade | StudentID | 0 | 1 | 1 | 0 | 0 | NULL | NULL | NULL | 56 | 4 | NULL | NULL |
+-----------+----------------+--------------+-------------+----------------+------------------+------------+-----------+-------+------------------------------+--------------+--------------------+------------------+----------------+------------------------------+-------------------+-------------------------+-----------------------+---------------------+-----------------+-------------------+--------------------------+---------------+-----------------+---------------+--------------+---------------+--------------------+-----------------------+---------------+--------------------+----------------------+--------------------------+------------------------+----------------------+-------------+------------+------------------+-----------------------+
首选唯一的聚集索引，因为这是用于单例查询的最有效的键。这意味着客户端需要SELECT查询返回的键值才能使自动生成的CRUD语句发挥作用。
有几个选项可以避免在选择查询中添加自然键列。一种方法是使用VIEW_METADATA选项创建视图，该选项封装了查询，并在代码中使用它，而不是直接使用表:
CREATE VIEW vw_StudentGrade
WITH VIEW_METADATA
AS
SELECT EnrollmentID, Grade from StudentGrade;
然后将元数据限制为视图返回的列，因此即使有唯一的聚集索引(或唯一约束)，也可以将EnrollmentID识别为唯一的键列:
EXEC sp_describe_first_result_set
@tsql = N'SELECT EnrollmentID,Grade FROM vw_StudentGrade'
, @params = NULL
, @browse_information_mode = 1;
+-----------+----------------+--------------+-------------+----------------+------------------+------------+-----------+-------+----------------+--------------+--------------------+------------------+----------------+------------------------------+-------------------+-------------------------+-----------------------+---------------------+-----------------+-------------------+--------------------------+---------------+-----------------+---------------+-----------------+---------------+--------------------+-----------------------+---------------+--------------------+----------------------+--------------------------+------------------------+----------------------+-------------+------------+------------------+-----------------------+
| is_hidden | column_ordinal | name | is_nullable | system_type_id | system_type_name | max_length | precision | scale | collation_name | user_type_id | user_type_database | user_type_schema | user_type_name | assembly_qualified_type_name | xml_collection_id | xml_collection_database | xml_collection_schema | xml_collection_name | is_xml_document | is_case_sensitive | is_fixed_length_clr_type | source_server | source_database | source_schema | source_table | source_column | is_identity_column | is_part_of_unique_key | is_updateable | is_computed_column | is_sparse_column_set | ordinal_in_order_by_list | order_by_is_descending | order_by_list_length | tds_type_id | tds_length | tds_collation_id | tds_collation_sort_id |
+-----------+----------------+--------------+-------------+----------------+------------------+------------+-----------+-------+----------------+--------------+--------------------+------------------+----------------+------------------------------+-------------------+-------------------------+-----------------------+---------------------+-----------------+-------------------+--------------------------+---------------+-----------------+---------------+-----------------+---------------+--------------------+-----------------------+---------------+--------------------+----------------------+--------------------------+------------------------+----------------------+-------------+------------+------------------+-----------------------+
| 0 | 1 | EnrollmentID | 0 | 56 | int | 4 | 10 | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 0 | 0 | 0 | NULL | tempdb | dbo | vw_StudentGrade | EnrollmentID | 1 | 1 | 0 | 0 | 0 | NULL | NULL | NULL | 56 | 4 | NULL | NULL |
| 0 | 2 | Grade | 0 | 106 | decimal(3,2) | 5 | 3 | 2 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 0 | 0 | 0 | NULL | tempdb | dbo | vw_StudentGrade | Grade | 0 | 0 | 1 | 0 | 0 | NULL | NULL | NULL | 106 | 17 | NULL | NULL |
+-----------+----------------+--------------+-------------+----------------+------------------+------------+-----------+-------+----------------+--------------+--------------------+------------------+----------------+------------------------------+-------------------+-------------------------+-----------------------+---------------------+-----------------+-------------------+--------------------------+---------------+-----------------+---------------+-----------------+---------------+--------------------+-----------------------+---------------+--------------------+----------------------+--------------------------+------------------------+----------------------+-------------+------------+------------------+-----------------------+
另一个选择是手动创建UpdateCommand而不是使用CommandBuilder。这将给予您完全的控制权，因此您可以在WHERE子句中使用EnrollmentID，而不管返回的元数据如何。