I have an entity with primary key "Id" which is Guid:
public class FileStore
{
public Guid Id { get; set; }
public string Name { get; set; }
public string Path { get; set; }
}
And some configuration:
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Entity<FileStore>().Property(x => x.Id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
base.OnModelCreating(modelBuilder);
}
When I try to insert a record I get a following error:
Cannot insert the value NULL into column 'Id', table 'FileStore'; column does not allow nulls. INSERT fails.\r\nThe statement has been terminated.
I don't want to generate Guid manually. I just want to insert a record and get Id generated by SQL Server. If I set .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity), Id column is not Identity column in SQL Server.
How can I configure Entity Framework to autogenerate Guid in SQL Server?
This question is related to
c#
entity-framework
sql-server-2008
entity-framework-6
According to this, DatabaseGeneratedOption.Identity is not detected by a specific migration if it's added after the table has been created, which is the case I run into. So I dropped the database and that specific migration and added a new migration, finally update the database, then everything works as expected. I am using EF 6.1, SQL2014 and VS2013.
If you do Code-First and already have a Database:
public override void Up()
{
AlterColumn("dbo.MyTable","Id", c => c.Guid(nullable: false, identity: true, defaultValueSql: "newsequentialid()"));
}
try this :
public class FileStore
{
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public Guid Id { get; set; }
public string Name { get; set; }
public string Path { get; set; }
}
You can check this SO post.
This works for me (no Azure), SQL 2008 R2 on dev server or localdb\mssqllocaldb on local workstation. Note: entity adds Create, CreateBy, Modified, ModifiedBy and Version columns.
public class Carrier : Entity
{
public Guid Id { get; set; }
public string Code { get; set; }
public string Name { get; set; }
}
then create a mapping configuration class
public class CarrierMap : EntityTypeConfiguration<Carrier>
{
public CarrierMap()
{
HasKey(p => p.Id);
Property(p => p.Id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
Property(p => p.Code)
.HasMaxLength(4)
.IsRequired()
.HasColumnAnnotation("Index", new IndexAnnotation(new IndexAttribute { IsClustered = true, IsUnique = true }));
Property(p => p.Name).HasMaxLength(255).IsRequired();
Property(p => p.Created).HasPrecision(7).IsRequired();
Property(p => p.Modified)
.HasColumnAnnotation("IX_Modified", new IndexAnnotation(new IndexAttribute()))
.HasPrecision(7)
.IsRequired();
Property(p => p.CreatedBy).HasMaxLength(50).IsRequired();
Property(p => p.ModifiedBy).HasMaxLength(50).IsRequired();
Property(p => p.Version).IsRowVersion();
}
}
This creates an Up method in the initial DbMigration when you execute add-migration like this
CreateTable(
"scoFreightRate.Carrier",
c => new
{
Id = c.Guid(nullable: false, identity: true),
Code = c.String(nullable: false, maxLength: 4),
Name = c.String(nullable: false, maxLength: 255),
Created = c.DateTimeOffset(nullable: false, precision: 7),
CreatedBy = c.String(nullable: false, maxLength: 50),
Modified = c.DateTimeOffset(nullable: false, precision: 7,
annotations: new Dictionary<string, AnnotationValues>
{
{
"IX_Modified",
new AnnotationValues(oldValue: null, newValue: "IndexAnnotation: { }")
},
}),
ModifiedBy = c.String(nullable: false, maxLength: 50),
Version = c.Binary(nullable: false, fixedLength: true, timestamp: true, storeType: "rowversion"),
})
.PrimaryKey(t => t.Id)
.Index(t => t.Code, unique: true, clustered: true);
Note: that the Id columns does not get a default value, don't worry
Now execute Update-Database, and you should end up with a table definition in your database like this:
CREATE TABLE [scoFreightRate].[Carrier] (
[Id] UNIQUEIDENTIFIER DEFAULT (newsequentialid()) NOT NULL,
[Code] NVARCHAR (4) NOT NULL,
[Name] NVARCHAR (255) NOT NULL,
[Created] DATETIMEOFFSET (7) NOT NULL,
[CreatedBy] NVARCHAR (50) NOT NULL,
[Modified] DATETIMEOFFSET (7) NOT NULL,
[ModifiedBy] NVARCHAR (50) NOT NULL,
[Version] ROWVERSION NOT NULL,
CONSTRAINT [PK_scoFreightRate.Carrier] PRIMARY KEY NONCLUSTERED ([Id] ASC)
);
GO
CREATE UNIQUE CLUSTERED INDEX [IX_Code]
ON [scoFreightRate].[Carrier]([Code] ASC);
Note: we have a overridden the SqlServerMigrationSqlGenerator to ensure it does NOT make the Primary Key a Clustered index as we encourage our developers to set a better clustered index on tables
public class OurMigrationSqlGenerator : SqlServerMigrationSqlGenerator
{
protected override void Generate(AddPrimaryKeyOperation addPrimaryKeyOperation)
{
if (addPrimaryKeyOperation == null) throw new ArgumentNullException("addPrimaryKeyOperation");
if (!addPrimaryKeyOperation.Table.Contains("__MigrationHistory"))
addPrimaryKeyOperation.IsClustered = false;
base.Generate(addPrimaryKeyOperation);
}
protected override void Generate(CreateTableOperation createTableOperation)
{
if (createTableOperation == null) throw new ArgumentNullException("createTableOperation");
if (!createTableOperation.Name.Contains("__MigrationHistory"))
createTableOperation.PrimaryKey.IsClustered = false;
base.Generate(createTableOperation);
}
protected override void Generate(MoveTableOperation moveTableOperation)
{
if (moveTableOperation == null) throw new ArgumentNullException("moveTableOperation");
if (!moveTableOperation.CreateTableOperation.Name.Contains("__MigrationHistory")) moveTableOperation.CreateTableOperation.PrimaryKey.IsClustered = false;
base.Generate(moveTableOperation);
}
}
You can not. You will / do break a lot of things. Like relationships. WHich rely on the number being pulled back which EF can not do in the way you set it up. THe price for breaking every pattern there is.
Generate the GUID in the C# layer, so that relationships can continue working.
It happened to me before.
When the table has been created and I added in .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity) later, the code migration somehow could not assign default value for the Guid column.
The fix:
All we need is to go to the database, select the Id column and add newsequentialid() manually into Default Value or Binding.
No need to update dbo.__MigrationHistory table.
Hope it helps.
The solution of adding New Guid() is generally not preferred, because in theory there is possibility that you might get a duplicate accidentally.
And you shouldn't worry about directly editing in the database. All Entity Framework do is automate part of our database work.
Translating
.HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity)
into
[Id] [uniqueidentifier] NOT NULL DEFAULT newsequentialid(),
If somehow our EF missed one thing and did not add in the default value for us, just go ahead and add it manually.
In addition to adding these attributes to your Id column:
[Key]
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public Guid Id { get; set; }
in your migration you should change your CreateTable to add the defaultValueSQL property to your column i.e.:
Id = c.Guid(nullable: false, identity: true, defaultValueSql: "newsequentialid()"),
This will prevent you from having to manually touch your database which, as you pointed out in the comments, is something you want to avoid with Code First.
And what something like this?
public class Carrier : Entity
{
public Carrier()
{
this.Id = Guid.NewGuid();
}
public Guid Id { get; set; }
public string Code { get; set; }
public string Name { get; set; }
}
Entity Framework – Use a Guid as the primary key
Using a Guid as your tables primary key, when using Entity Framework, requires a little more effort than when using a integer. The setup process is straightforward, after you’ve read/been shown how to do it.
The process is slightly different for the Code First and Database First approaches. This post discusses both techniques.
Code First
Using a Guid as the primary key when taking the code first approach is simple. When creating your entity, add the DatabaseGenerated attribute to your primary key property, as shown below;
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public Guid Id { get; set; }
Entity framework will create the column as you would expect, with a primary key and uniqueidentifier data type.
codefirst-defaultvalue
Also notice, very important, that the default value on the column has been set to (newsequentialid()). This generates a new sequential (continuous) Guid for each row. If you were so inclined, you could change this to newid()), which would result in a completely random Guid for each new row. This will be cleared each time your database gets dropped and re-created, so this works better when taking the Database First approach.
Database First
The database first approach follows a similar line to the code first approach, but you’ll have to manually edit your model to make it work.
Ensure that you edit the primary key column and add the (newsequentialid()) or (newid()) function as the default value before doing anything.
Next, open you EDMX diagram, select the appropriate property and open the properties window. Ensure that StoreGeneratedPattern is set to identity.
databasefirst-model
No need to give your entity an ID in your code, that will be populated for you automatically after the entity has been commited to the database;
using (ApplicationDbContext context = new ApplicationDbContext())
{
var person = new Person
{
FirstName = "Random",
LastName = "Person";
};
context.People.Add(person);
context.SaveChanges();
Console.WriteLine(person.Id);
}
Important Note: Your Guid field MUST be a primary key, or this does not work. Entity Framework will give you a rather cryptic error message!
Summary
Guid (Globally Unique Identifiers) can easily be used as primary keys in Entity Framework. A little extra effort is required to do this, depending on which approach you are taking. When using the code first approach, add the DatabaseGenerated attribute to your key field. When taking the Database First approach, explicitly set the StoredGeneratedPattern to Identity on your model.
[1]: https://i.stack.imgur.com/IxGdd.png
[2]: https://i.stack.imgur.com/Qssea.png
You can set the default value of your Id in your db to newsequentialid() or newid(). Then the identity configuration of EF should work.
Source: Stackoverflow.com