I'm just trying to add a table ProcessingFlags to my database (mapped to a corresponding JobRunning class), and for some reason, when one record is about to be inserted in the table, the exception shown further down is thrown.
DbContext:
modelBuilder.Entity("UserManager.Data.Model.JobRunning", b =>
{
b.Property<int>("Id")
.HasColumnType("int");
b.Property<bool>("IsProcessing")
.HasColumnType("bit")
.HasComment("Job is processing");
b.HasKey("Id");
b.ToTable("ProcessingFlags", null, t =>
{
t.HasComment("Semaphore to know if some process are executed and avoid the execution of others");
});
});
ProcessingFlagsConfiguration:
public override void Configure(EntityTypeBuilder<JobRunning> builder)
{
builder.ToTable(
"ProcessingFlags",
t => t.HasComment("Semaphore to know if some process are executed and avoid the execution of others")
);
builder.HasKey(k => k.Id);
builder.Property(k => k.Id).ValueGeneratedOnAdd();
builder.Property(p => p.IsProcessing).HasComment("Job is processing");
}
Migration:
protected override void Up(MigrationBuilder migrationBuilder)
{
migrationBuilder.CreateTable(
name: "ProcessingFlags",
columns: table => new
{
Id = table.Column<int>(type: "int", nullable: false)
.Annotation("SqlServer:Identity", "1, 1"),
IsProcessing = table.Column<bool>(type: "bit", nullable: false, comment: "Job is processing")
},
constraints: table =>
{
table.PrimaryKey("PK_ProcessingFlags", x => x.Id);
},
comment: "Semaphore to know if some process are executed and avoid the execution of others");
}
Migration designer:
protected override void BuildTargetModel(ModelBuilder modelBuilder)
{
modelBuilder.Entity("Usage.Model.ProcessingFlags", b =>
{
b.Property<int>("Id")
.HasColumnType("int")
.HasAnnotation("SqlServer:ValueGenerationStrategy", SqlServerValueGenerationStrategy.IdentityColumn);
b.Property<int>("IsProcessing")
.HasColumnType("bit")
.HasComment("Job is processing");
b.HasKey("Id");
b.ToTable("ProcessingFlags");
b.HasComment("Semaphore to know if some process are executed and avoid the execution of others");
});
}
JobRunning class:
public sealed class JobRunning : Entity<JobRunningValues>, IJobRunning<JobRunningValues>
{
public bool IsProcessing { get; set; }
}
JobRunningValues:
public enum JobRunningValues
{
UserCleanup = 1
}
Running the migrations with Update-Database, the table is created correctly, but when trying to insert data, it throws the following exception:
Microsoft.Data.SqlClient.SqlException (0x80131904): Cannot insert explicit value for identity column in table 'ProcessingFlags' when IDENTITY_INSERT is set to OFF.
I cannot touch the code where the record is to be inserted, because it belongs to an external nuget library we import in many projects and it's working, but I'll paste the code as a reference.
Code where the record is to be inserted:
public async ValueTask<bool> HandleAsync(UserCleanUpCommand request, CancellationToken ct = default)
{
await using var _ = await JobProcess<JobRunning, JobRunningValues>.BeginAsync(
async ct => await InternalHandleAsync(request, ct),
_gen,
_unitOfWork,
_processingFlagsRepository,
_sysLock,
_eventBus,
JobRunningValues.UserCleanup,
request.JobId,
_logger,
request.Props.CorrelationId,
ct
);
return true;
}
BeginAsync:
public static Task<IAsyncDisposable> BeginAsync(Func<CancellationToken, Task<(long Records, string? Note)>> action, IVdcIdGenerator gen, IVdcUnitOfWork unitOfWork, IVdcRepository<TJobRunning> jobRunningRepository, ISysLock sysLock, IEventBus eventBus, TJobCategory category, int? jobId, ILogger logger, string? correlationId, CancellationToken ct = default(CancellationToken))
{
return new JobProcess<TJobRunning, TJobCategory>(category.ToString(), gen, sysLock, unitOfWork, jobRunningRepository, eventBus, category, jobId, correlationId, logger).StartAsync(action, ct);
}
StartAsync:
private async Task<IAsyncDisposable> StartAsync(Func<CancellationToken, Task<(long Records, string? Note)>> action, CancellationToken ct)
{
_ = 5;
try
{
int? jobId = _jobId;
if (jobId.HasValue)
{
JobWorkerStartV0Event.B body = new JobWorkerStartV0Event.B(_jobId.Value, _gen.WorkerId);
await _eventBus.PublishJobManagerEventAsync<JobWorkerStartV0Event, JobWorkerStartV0Event.B>(new JobWorkerStartV0Event(_gen.GenerateId(), new Guid(_jobId.Value, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), _gen.WorkerId, _correlationId, body), ct);
}
await using (await _sysLock.AcquireAsync(_lockName, null, ct))
{
var anon = await (from s in _jobRunningRepository.Find((TJobRunning x) => x.Id.Equals(_category))
select new { s.IsProcessing }).FirstOrDefaultAsync(ct);
await FlagAsProcessingAsync(anon != null, anon?.IsProcessing ?? false, ct);
}
(long, string) tuple = await action(ct);
long? records = tuple.Item1;
_records = records;
_note = tuple.Item2;
}
catch (Exception err)
{
LoggerExtensions.LogError(exception: _err = err, logger: _logger, message: "Error in processing", args: Array.Empty<object>());
}
return this;
}
FlagAsProcessingAsync:
private async Task FlagAsProcessingAsync(bool exist, bool isProcessing, CancellationToken ct)
{
if (exist)
{
if (isProcessing)
{
throw new InvalidOperationException("There is other job in process.");
}
await _jobRunningRepository.Find((TJobRunning x) => x.Id.Equals(_category)).ExecuteUpdateAsync((SetPropertyCalls<TJobRunning> x) => x.SetProperty((TJobRunning p) => p.IsProcessing, valueExpression: true), ct);
}
else
{
TJobRunning entity = new TJobRunning
{
Id = _category,
IsProcessing = true
};
await _jobRunningRepository.AddAsync(entity, ct);
await _unitOfWork.SaveChangesAsync(ct);
}
}
Again, I've paste the code starting from HandleAsync just as reference, because from the BeginAsync the code belongs to a library, so the problem must surely be in the migration / table creation, but cannot figure out what.
In the ProcessingFlagsConfiguration, I've already tried with ValueGeneratedNever (which I already know it wouldn't work), with ValueGeneratedOnAdd (which it should work) and just removing it, all to no avail, because as soon as it enters the BeginAsync method the error is thrown and the InternalHandlerAsync in never hit.
The .NET version is 8.
Edit 1: Adding SQL Create Table script for ProcessingFlags.
CREATE TABLE [dbo].[ProcessingFlags](
[Id] [int] IDENTITY(1,1) NOT NULL,
[IsProcessing] [bit] NOT NULL,
CONSTRAINT [PK_ProcessingFlags] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
Edit 2:
Now I know (from one of my companions) that it is me the responsible of writing the Id field to table, so I'm even more confused now, because the Id field is set to Identity (1,1) in that table.
I'm just trying to add a table ProcessingFlags to my database (mapped to a corresponding JobRunning class), and for some reason, when one record is about to be inserted in the table, the exception shown further down is thrown.
DbContext:
modelBuilder.Entity("UserManager.Data.Model.JobRunning", b =>
{
b.Property<int>("Id")
.HasColumnType("int");
b.Property<bool>("IsProcessing")
.HasColumnType("bit")
.HasComment("Job is processing");
b.HasKey("Id");
b.ToTable("ProcessingFlags", null, t =>
{
t.HasComment("Semaphore to know if some process are executed and avoid the execution of others");
});
});
ProcessingFlagsConfiguration:
public override void Configure(EntityTypeBuilder<JobRunning> builder)
{
builder.ToTable(
"ProcessingFlags",
t => t.HasComment("Semaphore to know if some process are executed and avoid the execution of others")
);
builder.HasKey(k => k.Id);
builder.Property(k => k.Id).ValueGeneratedOnAdd();
builder.Property(p => p.IsProcessing).HasComment("Job is processing");
}
Migration:
protected override void Up(MigrationBuilder migrationBuilder)
{
migrationBuilder.CreateTable(
name: "ProcessingFlags",
columns: table => new
{
Id = table.Column<int>(type: "int", nullable: false)
.Annotation("SqlServer:Identity", "1, 1"),
IsProcessing = table.Column<bool>(type: "bit", nullable: false, comment: "Job is processing")
},
constraints: table =>
{
table.PrimaryKey("PK_ProcessingFlags", x => x.Id);
},
comment: "Semaphore to know if some process are executed and avoid the execution of others");
}
Migration designer:
protected override void BuildTargetModel(ModelBuilder modelBuilder)
{
modelBuilder.Entity("Usage.Model.ProcessingFlags", b =>
{
b.Property<int>("Id")
.HasColumnType("int")
.HasAnnotation("SqlServer:ValueGenerationStrategy", SqlServerValueGenerationStrategy.IdentityColumn);
b.Property<int>("IsProcessing")
.HasColumnType("bit")
.HasComment("Job is processing");
b.HasKey("Id");
b.ToTable("ProcessingFlags");
b.HasComment("Semaphore to know if some process are executed and avoid the execution of others");
});
}
JobRunning class:
public sealed class JobRunning : Entity<JobRunningValues>, IJobRunning<JobRunningValues>
{
public bool IsProcessing { get; set; }
}
JobRunningValues:
public enum JobRunningValues
{
UserCleanup = 1
}
Running the migrations with Update-Database, the table is created correctly, but when trying to insert data, it throws the following exception:
Microsoft.Data.SqlClient.SqlException (0x80131904): Cannot insert explicit value for identity column in table 'ProcessingFlags' when IDENTITY_INSERT is set to OFF.
I cannot touch the code where the record is to be inserted, because it belongs to an external nuget library we import in many projects and it's working, but I'll paste the code as a reference.
Code where the record is to be inserted:
public async ValueTask<bool> HandleAsync(UserCleanUpCommand request, CancellationToken ct = default)
{
await using var _ = await JobProcess<JobRunning, JobRunningValues>.BeginAsync(
async ct => await InternalHandleAsync(request, ct),
_gen,
_unitOfWork,
_processingFlagsRepository,
_sysLock,
_eventBus,
JobRunningValues.UserCleanup,
request.JobId,
_logger,
request.Props.CorrelationId,
ct
);
return true;
}
BeginAsync:
public static Task<IAsyncDisposable> BeginAsync(Func<CancellationToken, Task<(long Records, string? Note)>> action, IVdcIdGenerator gen, IVdcUnitOfWork unitOfWork, IVdcRepository<TJobRunning> jobRunningRepository, ISysLock sysLock, IEventBus eventBus, TJobCategory category, int? jobId, ILogger logger, string? correlationId, CancellationToken ct = default(CancellationToken))
{
return new JobProcess<TJobRunning, TJobCategory>(category.ToString(), gen, sysLock, unitOfWork, jobRunningRepository, eventBus, category, jobId, correlationId, logger).StartAsync(action, ct);
}
StartAsync:
private async Task<IAsyncDisposable> StartAsync(Func<CancellationToken, Task<(long Records, string? Note)>> action, CancellationToken ct)
{
_ = 5;
try
{
int? jobId = _jobId;
if (jobId.HasValue)
{
JobWorkerStartV0Event.B body = new JobWorkerStartV0Event.B(_jobId.Value, _gen.WorkerId);
await _eventBus.PublishJobManagerEventAsync<JobWorkerStartV0Event, JobWorkerStartV0Event.B>(new JobWorkerStartV0Event(_gen.GenerateId(), new Guid(_jobId.Value, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), _gen.WorkerId, _correlationId, body), ct);
}
await using (await _sysLock.AcquireAsync(_lockName, null, ct))
{
var anon = await (from s in _jobRunningRepository.Find((TJobRunning x) => x.Id.Equals(_category))
select new { s.IsProcessing }).FirstOrDefaultAsync(ct);
await FlagAsProcessingAsync(anon != null, anon?.IsProcessing ?? false, ct);
}
(long, string) tuple = await action(ct);
long? records = tuple.Item1;
_records = records;
_note = tuple.Item2;
}
catch (Exception err)
{
LoggerExtensions.LogError(exception: _err = err, logger: _logger, message: "Error in processing", args: Array.Empty<object>());
}
return this;
}
FlagAsProcessingAsync:
private async Task FlagAsProcessingAsync(bool exist, bool isProcessing, CancellationToken ct)
{
if (exist)
{
if (isProcessing)
{
throw new InvalidOperationException("There is other job in process.");
}
await _jobRunningRepository.Find((TJobRunning x) => x.Id.Equals(_category)).ExecuteUpdateAsync((SetPropertyCalls<TJobRunning> x) => x.SetProperty((TJobRunning p) => p.IsProcessing, valueExpression: true), ct);
}
else
{
TJobRunning entity = new TJobRunning
{
Id = _category,
IsProcessing = true
};
await _jobRunningRepository.AddAsync(entity, ct);
await _unitOfWork.SaveChangesAsync(ct);
}
}
Again, I've paste the code starting from HandleAsync just as reference, because from the BeginAsync the code belongs to a library, so the problem must surely be in the migration / table creation, but cannot figure out what.
In the ProcessingFlagsConfiguration, I've already tried with ValueGeneratedNever (which I already know it wouldn't work), with ValueGeneratedOnAdd (which it should work) and just removing it, all to no avail, because as soon as it enters the BeginAsync method the error is thrown and the InternalHandlerAsync in never hit.
The .NET version is 8.
Edit 1: Adding SQL Create Table script for ProcessingFlags.
CREATE TABLE [dbo].[ProcessingFlags](
[Id] [int] IDENTITY(1,1) NOT NULL,
[IsProcessing] [bit] NOT NULL,
CONSTRAINT [PK_ProcessingFlags] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
Edit 2:
Now I know (from one of my companions) that it is me the responsible of writing the Id field to table, so I'm even more confused now, because the Id field is set to Identity (1,1) in that table.
Based on the layout of your table, the IDENTITY flag is enabled. So SQL Server won't allow you to explicitly set a value for the corresponding column. If you can't remove the constraint from your SQL model, you have to disable temporarily the constraint.
So you need to enable the IDENTITY_INSERT per sql connection with the following sql statement :
SET IDENTITY_INSERT ProcessingFlags OFF;
A way to achieve that is to execute a raw SQL Query before the SaveChanges.
You can read official documentation about your issue here.
As you explained, a large part of the code shared is in a library you can't edit, but it looks like you can edit the HandleAsync method. If the unit of work pattern is commonly implemented, your _unitOfWork instance should hold a reference to your DbContext. So you need to figure a way to get the DbContext instance and execute something like that before BeginAsync:
dbContext.Database.ExecuteSqlRawAsync("SET IDENTITY_INSERT ProcessingFlags OFF;");
Id = _category. IfJobRunning(i.e. its table) has an identity column, why do you (or they) assign anIdvalue to a new instance? – Gert Arnold Commented Mar 5 at 19:39Add, notAddAsync. You almost certainly don't need it. – Gert Arnold Commented Mar 5 at 19:52