When saving a new Entity to the DB using EF5 saves the record with ID 0. The database has an identity column starting with 1 and counting 1.
When I recreate the specific table, all problems are gone. But I don't want to recreate tables in my production environment the whole time.
Where as I'm not an SQL expert. Is there some kind of method to solve this problem? We are working with MSSQL2005 (SQL2012 is ordered, but until it's here I can't make use of it and there wil be some time migrating old databases to the new one).
//Database creation script
/* [Queue].[DirectDebitOrder] */
CREATE TABLE [Queue].[BookingExportTask](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[DirectDebitRequestID] [bigint] NOT NULL,
[Type] [nvarchar](50) NOT NULL,
[Step] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_Queue_BookingExportTask] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY];
/* Foreign Keys */
ALTER TABLE [Queue].[BookingExportTask] WITH CHECK ADD CONSTRAINT [FK_Queue_BookingExportTask_Queue_BookingExportTaskStepEnum] FOREIGN KEY([Step]) REFERENCES [Queue].[BookingExportTaskStepEnum] ([Name]);
ALTER TABLE [Queue].[BookingExportTask] CHECK CONSTRAINT [FK_Queue_BookingExportTask_Queue_BookingExportTaskStepEnum];
ALTER TABLE [Queue].[BookingExportTask] WITH CHECK ADD CONSTRAINT [FK_Queue_BookingExportTask_Queue_BookingExportTaskTypeEnum] FOREIGN KEY([Type]) REFERENCES [Queue].[BookingExportTaskTypeEnum] ([Name]);
ALTER TABLE [Queue].[BookingExportTask] CHECK CONSTRAINT [FK_Queue_BookingExportTask_Queue_BookingExportTaskTypeEnum];
ALTER TABLE [Queue].[BookingExportTask] WITH CHECK ADD CONSTRAINT [FK_Queue_BookingExportTask_Finance_DirectDebitRequest] FOREIGN KEY([DirectDebitRequestID]) REFERENCES [Finance].[DirectDebitRequest] ([ID])
ALTER TABLE [Queue].[BookingExportTask] CHECK CONSTRAINT [FK_Queue_BookingExportTask_Finance_DirectDebitRequest];
//C# Code for saving Entities
/// <summary>
/// Saves the data in the <see cref="ILoadableBookingExportTask"/> to the <paramref name="entityContext"/>
/// </summary>
/// <param name="entityContext">The <see cref="DBContext"/> context to save the <see cref="ILoadableBookingExportTask"/> to.</param>
/// <param name="source">The <see cref="ILoadableBookingExportTask"/> to save.</param>
/// <returns>The <see cref="BookingExportTask"/> that holds the actually stored values.</returns>
public static BookingExportTask Save(this DBContext entityContext, ILoadableBookingExportTask source)
{
if (source == null) { return null; }
return source.SaveTo(entityContext.BookingExportTask.EnsureExists(source), entityContext);
}
/// <summary>
/// Helper. Finds existing instance or creates a new instance of <typeparamref name="TEntityType"/> which is immediately added to the <see cref="dbSet{TEntityType}"/> as well.
/// </summary>
/// <typeparam name="TEntityType">The type of the entity to check.</typeparam>
/// <param name="dbSet">The database collection for the entity.</param>
/// <param name="entity">The <see cref="IEntity"/> to find.</param>
/// <returns>The existing or newly created <typeparamref name="TEntityType"/>.</returns>
public static TEntityType EnsureExists<TEntityType>(this DbSet<TEntityType> dbSet, IEntity entity)
where TEntityType : class, IEntity, new()
{
if (entity == null) { return default(TEntityType); }
return dbSet.EnsureExists(entity.ID);
}
/// <summary>
/// Helper. Finds existing instance or creates a new instance of <typeparamref name="TEntityType"/> which is immediately added to the <see cref="dbSet{TEntityType}"/> as well.
/// </summary>
/// <typeparam name="TEntityType">The type of the entity to check.</typeparam>
/// <param name="dbSet">The collection to check for existance of the entity.</param>
/// <param name="id">The ID of the entity to find.</param>
/// <returns>The existing or newly created <typeparamref name="TEntityType"/>.</returns>
public static TEntityType EnsureExists<TEntityType>(this DbSet<TEntityType> dbSet, long id)
where TEntityType : class, IEntity, new()
{
return dbSet.GetByID(id) ?? dbSet.New();
}
/// <summary>
/// Saves the data in the <see cref="ILoadableBookingExportTask"/> to the <paramref name="entityContext"/>
/// </summary>
/// <param name="source">The <see cref="ILoadableBookingExportTask"/> to save.</param>
/// <param name="target">The <see cref="BookingExportTask"/> to save the data to.</param>
/// <param name="entityContext">The <see cref="DBContext"/> context to save the <see cref="ILoadableBookingExportTask"/> to.</param>
/// <returns>The <see cref="BookingExportTask"/> that holds the actually stored values.</returns>
private static BookingExportTask SaveTo(this ILoadableBookingExportTask source, BookingExportTask target, DBContext entityContext)
{
if (source == null) { return target; }
Debug.WriteLine(string.Format("Saving ILoadableBookingExportTask {0} to DBContext.", target.ID));
/* copy all fields from source to target. Use SyncLinkedObject for all linked entities to ensure the saving of possible changes to the foreign entities.
* target.Field = target.Field;
* target.RelatedEntity = entityContext.SyncLinkedObject(source.RelatedEntity, source.RelatedEntity_ForeignKey);
*
* Note: It's useless to overwrite the Identity property - like target.ID. Just skip it.
*/
return target;
}
C# Code in short:
- Check if entity already exists (it's a new entity so NO)
- Create new entity in the specific DBSet in the context
- Pass the values of the business entity to the dbset-entity (without the ID)
- Save the context
This is al working fine until. The DB is changed or restored. After that tables with Identity columns have to be recreated. If i don't recreate the tables the first NEW entity is saved to ID 0. With the ensureExists method al new entities from that moment on are overwriting the existing entity, which is logical because of the ID 0.
Problem is that the identity columns are not recognized from the EF.
If this problem arises I monitored the DB with the SQL Profiler. I pasted the incomming query found by the profiler in the SQL management studio where I get my ID. So it seems the DB is fine. Code isn't changed and worked before (before the restore of the DB). Only solution I found so far is recreating the tables with identity columns. After that the C# code is working again without adaption.
