Disclaimer: This question is regarding "modernization" of almost 17 years old system. ORM that was used 17 years ago required that PK's don't use Identity. I know how bad it is, and I can't change anything regarding this.
So I have (simplified) following table in database:
CREATE TABLE [dbo].[KlantDocument](
[OID] [bigint] NOT NULL,
[Datum] [datetime] NOT NULL,
[Naam] [varchar](150) NOT NULL,
[Uniek] [varchar](50) NOT NULL,
[DocumentSoort] [varchar](50) NULL,
CONSTRAINT [PK_KlantDocument] PRIMARY KEY CLUSTERED
(
[OID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[KlantDocument] CHECK CONSTRAINT [FK_KlantDocument_Klant]
GO
As you can see, table doesn't have Identity set on PK, so it has to be inserted manually.
Project is being rebuilt in Web Api .Net Core 5, that is going to do all of the CRUD operations. It is using EF Core as ORM, and it is agreed that Unit of Work pattern is going to be used here (please do keep reading, UoW is not issue here).
For those curious or for what it's worth, you can take a look at it here (https://pastebin.com/58bSDkUZ) ( this is by no means full UoW, just partially and without comments).
UPDATE: Caller Controller Action:
[HttpPost]
[Route("UploadClientDocuments")]
public async Task<IActionResult> UploadClientDocuments([FromForm] ClientDocumentViewModel model)
{
if (!ModelState.IsValid)
return BadRequest(ModelStateExtensions.GetErrorMessage(ModelState));
var dto = new ClientDocumentUploadDto
{
DocumentTypeId = model.DocumentTypeId,
ClientId = model.ClientId,
FileData = await model.File.GetBytes(),
FileName = model.File.FileName, // I need this property as well, since I have only byte array in my "services" project
};
var result = await _documentsService.AddClientDocument(dto);
return result ? Ok() : StatusCode(500);
}
When I am inserting record I am doing it like so:
// Called by POST method multiple times at once
public async Task<bool> AddClientDocument(ClientDocumentUploadDto dto)
{
try
{
var doc = new KlantDocument
{
/* All of the logic below to fetch next id will be executed before any of the saves happen, thus we get "Duplicate key" exception. */
// 1st try to fetch next id
// Oid = _uow.Query<KlantDocument>().OrderByDescending(s => s.Oid).First().Oid + 1,
// 2nd try to fetch next id
// Oid = _uow.Query<KlantDocument>().OrderBy(s => s.Oid).Last().Oid + 1,
// 3rd try to fetch next id
// Oid = await _uow.Query<KlantDocument>().OrderBy(s => s.Oid).AsNoTracking().Select(s => s.Oid).LastAsync() + 1,
// 4th try to fetch next id
// Oid = _uow.Query<KlantDocument>().OrderBy(s => s.Oid).AsNoTracking().Select(s => s.Oid).Last() + 1,
// 5th try to fetch next id
// Oid = (_uow.Query<KlantDocument>().OrderBy(s => s.Oid).AsNoTracking().Max(s => s.Oid) + 1),
Naam = dto.FileName,
DocumentSoort = dto.DocumentTypeId,
Datum = DateTime.Now,
Uniek = Guid.NewGuid() + "." + dto.FileName.GetExtension()
};
_uow.Context.Set<KlantDocument>().Add(doc); // Does not work
_uow.Commit();
}
catch (Exception e)
{
_logger.Error(e);
return false;
}
}
I get "Duplicate key" exception because 2 of the records are overlapping when inserting.
I have tried to wrap it into the transaction like so:
_uow.ExecuteInTransaction(() => {
var doc = new KlantDocument
{
/* All of the logic below to fetch next id will be executed before any of the saves happen, thus we get "Duplicate key" exception. */
// 1st try to fetch next id
// Oid = _uow.Query<KlantDocument>().OrderByDescending(s => s.Oid).First().Oid + 1,
// 2nd try to fetch next id
// Oid = _uow.Query<KlantDocument>().OrderBy(s => s.Oid).Last().Oid + 1,
// 3rd try to fetch next id
// Oid = await _uow.Query<KlantDocument>().OrderBy(s => s.Oid).AsNoTracking().Select(s => s.Oid).LastAsync() + 1,
// 4th try to fetch next id
// Oid = _uow.Query<KlantDocument>().OrderBy(s => s.Oid).AsNoTracking().Select(s => s.Oid).Last() + 1,
// 5th try to fetch next id
// Oid = (_uow.Query<KlantDocument>().OrderBy(s => s.Oid).AsNoTracking().Max(s => s.Oid) + 1),
Naam = dto.FileName,
DocumentSoort = dto.DocumentTypeId,
Datum = DateTime.Now,
Uniek = Guid.NewGuid() + "." + dto.FileName.GetExtension()
};
_uow.Context.Set<KlantDocument>().Add(doc); // Does not work
_uow.Commit();
});
and it does not work. I still get "Duplicate key" exception.
From what I know, shouldn't EF by default lock database until transaction is complete?
I tried to manually write insert SQL like so:
using (var context = _uow.Context)
{
using (var dbContextTransaction = context.Database.BeginTransaction())
{
try
{
// VALUES ((SELECT MAX(OID) + 1 FROM KlantDocument), -- tried this also, but was not yielding results
var commandText = @"INSERT INTO KlantDocument
(
OID
,Datum
,Naam
,Uniek
,DocumentSoort
)
VALUES (
(SELECT TOP(1) (OID + 1) FROM KlantDocument ORDER BY OID DESC),
@Datum,
@Naam,
@Uniek,
@DocumentSoort
)";
var datum = new SqlParameter("@Datum", DateTime.Now);
var naam = new SqlParameter("@Naam", dto.FileName);
var uniek = new SqlParameter("@Uniek", Guid.NewGuid() + "." + dto.FileName.GetExtension());
var documentSoort = new SqlParameter("@DocumentSoort", dto.DocumentTypeId ?? "OrderContents");
context.Database.ExecuteSqlRaw(commandText, datum, naam, uniek, documentSoort);
dbContextTransaction.Commit();
}
catch (Exception e)
{
dbContextTransaction.Rollback();
return false;
}
}
}
Same thing.
I did a lot research to try to tackle this issue:
- .NET Core - ExecuteSqlRaw - last inserted id? - solutions either don't work, or work only with identity columns
- I got idea to use SQL OUTPUT variable, but issue is that it's really hard or even impossible to achieve, feels hacky and overall if managed, there is no guarantee that it will work. As seen here: SQL Server Output Clause into a scalar variable and here How can I assign inserted output value to a variable in sql server?
As noted above, ORM at the time did some magic where it did sequentially insert records in database and I would like to keep it that way.
Is there any way that I can achieve sequential insert when dealing with given scenario?
