Using Entity Framework Core, I want to have a Guid PK, without suffering page fragmentation in the database.
I have seen this post and this. Although it was possible in EF6, the way it's done seems to have changed.
Is it possible to create a non-clustered primary key in Entity Framework Core and have an additional index?
Q&A Answer below.
Right-click the table on which you want to create a nonclustered index and select Design. Right-click on the column you want to create the nonclustered index on and select Indexes/Keys. In the Indexes/Keys dialog box, click Add. Select the new index in the Selected Primary/Unique Key or Index text box.
EF Core only supports one index per distinct set of properties. If you configure an index on a set of properties that already has an index defined, either by convention or previous configuration, then you will be changing the definition of that index.
You can have a table with a non-clustered primary key, or a clustered table without primary key. Both is possible.
Entity Framework Core supports composite keys - primary key values generated from two or more fields in the database. Composite keys are not covered by conventions or data annotation attributes. The only way to configure composite keys is to use the HasKey method.
It is possible using EntityFrameworkCore v1.0.1 or greater.
The following code gets the desired result:
using System;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata;
namespace TestApplication.Models
{
/// <summary>
/// The context class. Make your migrations from this point.
/// </summary>
public partial class TestApplicationContext : DbContext
{
public virtual DbSet<Company> Companies { get; set; }
public TestApplicationContext(DbContextOptions<TestApplicationContext> options) : base(options)
{
}
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
// standard stuff here...
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Company>(entity =>
{
entity.Property<Guid>("CompanyId")
.ValueGeneratedOnAdd();
entity.Property<int>("CompanyIndex")
.UseSqlServerIdentityColumn()
.ValueGeneratedOnAdd();
entity.Property(e => e.CompanyName)
.IsRequired()
.HasColumnType("varchar(100)");
// ... Add props here.
entity.HasKey(e => e.CompanyId)
.ForSqlServerIsClustered(false)
.HasName("PK_Company");
entity.HasIndex(e => e.CompanyIndex)
.ForSqlServerIsClustered(true)
.HasName("IX_Company");
});
}
}
/// <summary>
/// The model - put here for brevity.
/// </summary>
public partial class Company
{
public Company()
{
}
public Guid CompanyId { get; set; }
public int CompanyIndex { get; set; }
public string CompanyName { get; set; }
// more props here.
}
}
Project.json
{
"version": "1.0.0-*",
"dependencies": {
"Microsoft.EntityFrameworkCore.Design": "1.0.1",
"Microsoft.EntityFrameworkCore.SqlServer": "1.0.1",
"Microsoft.EntityFrameworkCore.SqlServer.Design": "1.0.1",
"Microsoft.EntityFrameworkCore.Tools": "1.0.0-preview3-final",
"NETStandard.Library": "1.6.0"
},
"tools": {
"Microsoft.EntityFrameworkCore.Tools": "1.0.0-preview3-final",
"Microsoft.AspNetCore.Server.IISIntegration.Tools": "1.0.0-preview2-final"
},
"frameworks": {
"netstandard1.6": {
"imports": "dnxcore50"
}
}
}
For EF Core 5 or greater
// ...
modelBuilder.Entity<MyEntity>(entity =>
{
// ...
entity.HasIndex(e => e.MyIndexColumn)
.IsClustered(false);
}
// ...
=> The obsolete method ForSqlServerIsClustered
was removed in this version
For EF Core 3.x
// ...
modelBuilder.Entity<MyEntity>(entity =>
{
// ...
entity.HasIndex(e => e.MyIndexColumn)
.IsClustered(true); // new method
// OR
entity.HasIndex(e => e.MyIndexColumn)
.ForSqlServerIsClustered(false); // obsolete method
}
// ...
=> Both methods IsClustered
and ForSqlServerIsClustered
can be used, but later one is already marked as obsolete in favor of the first one.
For EF Core 1.x- EF Core 2.x
// ...
modelBuilder.Entity<MyEntity>(entity =>
{
// ...
entity.HasIndex(e => e.MyIndexColumn)
.ForSqlServerIsClustered(false);
}
// ...
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With