In a new WPF project that I am writing using c#, I want to attempt on using Entity Framework Core to interact with my SQL Server database.
Every time I try to add model to my context, I get the following error
Cannot insert explicit value for identity column in table 'Orders' when IDENTITY_INSERT is set to OFF.
I am using a Repository
and UnitOfWork
which wraps the Entity Framework Core methods to perform the work needed.
But at its simplest, I am executing the following code
var order = new Order();
order.Title = "some";
....
Context.Orders.Add(order);
Context.SaveChanges();
Here is my model
public class Order
{
[Key]
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public Int64 Id { get; set; }
public string Status { get; set; }
public int? CustomerId { get; set; }
public DateTime? Birthdate { get; set; }
public int UtcOffset { get; set; }\
public DateTime CreatedAt { get; set; }
public DateTime? UpdatedAt { get; set; }
public int? UpdatedBy { get; set; }
[ForeignKey(nameof(Creator))]
public int CreatedBy { get; set; }
public Order()
{
CreatedAt = DateTime.UtcNow;
}
public virtual User Creator { get; set; }
public virtual Customer Customer { get; set; }
}
What could be causing this problem?
Updated
Here is how my table is created
CREATE TABLE [dbo].[Orders](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[Status] [varchar](50) NOT NULL,
[CustomerId] [int] NULL,
[Birthdate] [datetime] NULL,
[CreatedBy] [int] NOT NULL,
[CreatedAt] [datetime] NOT NULL,
[UpdatedBy] [int] NULL,
[UpdatedAt] [datetime] NULL,
[UtcOffset] [int] NOT NULL,
CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Here is the method that creates the Order
model
public Order Create(int? customerId, DateTime? birthdate)
{
var order = new Order();
order.CustomerId = customerId;
order.Birthdate = birthdate;
order.Status = OrderStatus.Sold.ToString();
order.CreatedBy = Passport.Identity.Id;
var updatedOrder = Orders.Add(order);
Orders.Save();
return updatedOrder;
}
Here is my repository implementation
public class EntityRepository<TEntity, TKeyType> : IRepository<TEntity, TKeyType>
where TEntity : class
where TKeyType : struct
{
protected readonly DbContext Context;
protected readonly DbSet<TEntity> DbSet;
public EntityRepository(DbContext context)
{
Context = context;
DbSet = context.Set<TEntity>();
}
public TEntity Get(TKeyType id)
{
return DbSet.Find(id);
}
public IEnumerable<TEntity> GetAll()
{
return DbSet.ToList();
}
public bool Any(Expression<Func<TEntity, bool>> predicate)
{
return DbSet.Any(predicate);
}
public IQueryable<TEntity> Find(Expression<Func<TEntity, bool>> predicate)
{
return DbSet.Where(predicate);
}
public TEntity SingleOrDefault(Expression<Func<TEntity, bool>> predicate)
{
return DbSet.SingleOrDefault(predicate);
}
public virtual TEntity Add(TEntity entity)
{
DbSet.Add(entity);
return entity;
}
public virtual IEnumerable<TEntity> AddRange(IEnumerable<TEntity> entities)
{
DbSet.AddRange(entities);
return entities;
}
public void Remove(TEntity entity)
{
DbSet.Remove(entity);
}
public void RemoveRange(IEnumerable<TEntity> entities)
{
DbSet.RemoveRange(entities);
}
public void Update(TEntity entity)
{
DbSet.Attach(entity);
var record = Context.Entry(entity);
record.State = EntityState.Modified;
}
public IQueryable<TEntity> Query()
{
return DbSet;
}
public void Save()
{
Context.SaveChanges();
}
}
public class EntityRepository<TEntity> : EntityRepository<TEntity, int>
where TEntity : class
{
public EntityRepository(DbContext context)
: base(context)
{
}
}
Additionally, this question is not a duplicate on Entity Framework error: Cannot insert explicit value for identity column in table because I am decorating my Id property with the [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
. Moreover, I am using database first approach, so I created my database manually using MSSMS
This issue could be one of the following two scenarios
First, you could be unintentionally setting the Id
property using auto-mapping tool. If your using auto-mapping utility like AutoMapper
, ValueInjecter
or OoMapper
make sure you configure your mapping to ignore the the Id
property when your destination object is the Order
model. For Example, with AutoMapper, use the following syntax to configure auto-mapper to not may any value to the Id
property.
expression.CreateMap<OrderViewModel, Order>()
.ForMember(src => src.Id, opts => opts.Ignore());
Second scenario, what you are seeing may be related to an EntityFrameworkCore Bug. Instead on using the data-annotation (i.e [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
), try defining the id property as identity using fluent in the OnModelCreating
of you context class like so
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
// Here we identify the Id property to be set to Identity
// Also, we use change the PropertySaveBehavior on the same
// property to ignore the values
modelBuilder.Entity(modelType)
.Property(key.Name)
.UseSqlServerIdentityColumn()
.Metadata.BeforeSaveBehavior = PropertySaveBehavior.Ignore;
}
The above code should also solve the first scenario if that is indeed your problem. the line Metadata.BeforeSaveBehavior = PropertySaveBehavior.Ignore;
from above is telling EntityCore to simply not include the Id
column in the insert statement. So even if you're mapper maps a value to the Id
property that incorrectly mapped value would be excluded from the insert statement.
Furthermore, you could use reflection to set the "Id" property on all your dbsets to make your workflow little more robust or if you have many dbsets you won't have to add them one at a time. Here is an example of how to use reflection to configure your models to make the column that is called Id
identity.
public class DataContext : DbContext
{
public DbSet<Order> Orders { get; set; }
public DbSet<User> Users { get; set; }
// Here list any other DbSet...
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
// First we identify the model-types by examining the properties in the DbContext class
// Here, I am assuming that your DbContext class is called "DataContext"
var modelTypes = typeof(DataContext).GetProperties()
.Where(x => x.PropertyType.IsGenericType && x.PropertyType.GetGenericTypeDefinition() == typeof(DbSet<>))
.Select(x => x.PropertyType.GetGenericArguments().First())
.ToList();
// Feel free to add any other possible types you may have defined your "Id" property with
// Here I am assuming that only short, int, and bigint would be considered identity
var identityTypes = new List<Type> { typeof(Int16), typeof(Int32), typeof(Int64) };
foreach (Type modelType in modelTypes)
{
// Find the first property that is named "id" with the types defined in identityTypes collection
var key = modelType.GetProperties()
.FirstOrDefault(x => x.Name.Equals("Id", StringComparison.CurrentCultureIgnoreCase) && identityTypes.Contains(x.PropertyType));
// Once we know a matching property is found
// We set the propery as Identity using UseSqlServerIdentityColumn() method
if (key == null)
{
continue;
}
// Here we identify the Id property to be set to Identity
// Also, we use change the PropertySaveBehavior on the same
// property to ignore the values
modelBuilder.Entity(modelType)
.Property(key.Name)
.UseSqlServerIdentityColumn()
.Metadata.BeforeSaveBehavior = PropertySaveBehavior.Ignore;
}
}
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
if (optionsBuilder.IsConfigured)
{
return;
}
optionsBuilder.UseSqlServer(ConfigurationManager.ConnectionStrings["ConnectionName"]);
}
}