Entity Framework Many-to-Many Clustered vs. Nonclustered Index

Question

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!

Answer 1

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.