实体框架多对多集群与非聚簇索引 [英] Entity Framework Many-to-Many Clustered vs. Nonclustered Index

问题描述

我设计了一个具有两个实体的实体数据模型，它们之间存在着很多关系。当我自动生成SQL代码以生成此模型的数据库时，它已经生成了一个表（两列）来跟踪这个多对多关联。但是，这个表在这两列上都有一个PRIMARY KEY NONCLUSTERED。

由于我希望这可以在不喜欢只有非聚簇索引的表的SQL Azure上工作，所以我是想知道是否有一个很好的方式来告诉代码生成来生成集群索引？谢谢！

解决方案

我有另一个名为Model.indexes.sql的文件，其中包含脚本以创建超出基本EF的其他索引，例如用于性能优化的那些。

尽管这不太理想，但我添加了一个索引删除，并为每个EF关联创建了将非聚类索引转换为索引：

ALTER TABLE [dbo]。[MyAssociation]
DROP CONSTRAINT [PK_MyAssociation]
GO
ALTER TABLE [dbo] 。[MyAssociation]
ADD CONSTRAINT [PK_MyAssociation]
PRIMARY KEY CLUSTERED（[Table1_Id]，[Table2_Id] ASC）;
GO

这是在从模型生成数据库...之后执行的。我会喜欢一个更优雅的解决方案。

I designed an entity data model with two entities between which there exists a many to many relationship. When I auto-generate SQL code to generate the database for this model, it has generated a table (two columns) to keep track of this many-to-many association. However, this table has a PRIMARY KEY NONCLUSTERED on both columns.

Since I want this to work on SQL Azure which doesn't like tables with only nonclustered indices, I was wondering whether there is a good way of telling the code generation to generate clustered indices? Thanks!

解决方案

I have another file called Model.indexes.sql that contains scripts to create additional indexes beyond the basic ones EF generates, such as those for performance optimizations.

Although this is not ideal, I added into this an index drop and create for each EF association to convert the Non-Clustered indexes into indexed ones:

ALTER TABLE [dbo].[MyAssociation] DROP CONSTRAINT [PK_MyAssociation] GO ALTER TABLE [dbo].[MyAssociation] ADD CONSTRAINT [PK_MyAssociation] PRIMARY KEY CLUSTERED ([Table1_Id], [Table2_Id] ASC); GO

This is executed after every "Generate Database from Model...". I would love a more elegant solution.

这篇关于实体框架多对多集群与非聚簇索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！