I need to create Crud services for a third-party SQL Server database.Unfortunately, the database uses VARCHAR (and even worse CHAR) type for its primary keys.
Is this supported in OrmLite?
Sure, VARCHAR primary keys are fairly common, CHAR’s are rare but they should be able to be populated as normal.
I have made progress and can query and create rows in the table. But, I am unable to perform an update, because of an OptimisticConcurrencyException, which I do not understand.
The code (.Net core 5 and C#9) snippets are as follows:
ServiceModel.Type:
[Schema("dbo")]
[Alias("delivery_term")]
[DataContract]
public record IncoTerm
{
[Alias("del_term_code")]
[DataMember, PrimaryKey, Required]
[CustomField("CHAR(8)")]
public string Code { get; init; } = null!;
[Alias("del_term_desc")]
[DataMember, Required]
public string Description { get; init; } = null!;
[Alias("trans_id")]
[DataMember, Required]
public int TransactionId { get; init; }
}
ServiceModel:
[Route("/reference/incoterm/{Code}", "PUT")]
public class UpdateIncoTerm : IUpdateDb<IncoTerm>, IReturn<IncoTerm>, IPut
{
public string Code { get; init; } = null!;
public string Description { get; init; } = null!;
public int TransactionId { get; init; }
}
Generated SQL, which works if executed directly in SQL Server Management Studio:
UPDATE “dbo”.“delivery_term”
SET “del_term_desc”=@del_term_desc,
“trans_id”=@trans_id
WHERE (del_term_code = @0)
PARAMS: @0=ABC1, @del_term_desc=ABC-1-a, @trans_id=4138339
Service response
{
"result": null,
"responseStatus": {
"errorCode": "OptimisticConcurrencyException",
"message": "2 rows were updated by 'UpdateIncoTerm'",
"stackTrace": null,
"errors": [],
"meta": null
}
}
Any suggestions how to investigate further, because no other errors are reported under the debugger or in the .csv log file?
My only thought, is perhaps the database’s concept of a “RowVersion”. Before perfoming the update, a new value for the trans_id must be obtained from a database sequence… At this time, I am triggering the sequence separately and manually copying it to the swagger-ui form.
The fact that there are 2 rows updated for a WHERE using a primary key sounds odd. Can you confirm the schema is enforcing a unique constraint? It sounds like something has happened with the insert to create 2 records with the same del_term_code. If del_term_code is missing a constraint at the database level, this could happen as the use of PrimaryKey attribute can’t prevent the creation of rows with duplicate values.
Are you able to share the schema of that table?
As requested, the table definition (from the third-party) is as follows:
CREATE TABLE dbo.delivery_term (
del_term_code char(8) NOT NULL,
del_term_desc varchar(50) NULL,
trans_id int NOT NULL,
CONSTRAINT delivery_term_pk PRIMARY KEY CLUSTERED (del_term_code) WITH (FILLFACTOR = 80)
)
ON [PRIMARY]
GO
And the original insert did only create a single record:
del_term_code, del_term_desc, trans_id
ABC1 , ABC-1, 4138338The error was because the Update, modified 2 rows given updates by Primary Key shouldn’t update more than 1 row.
Please verify that the del_term_code is a unique Primary Key and only 1 result is returned?
SELECT * FROM dbo.delivery_term WHERE del_term_code = 'ABC1'
The creation had only inserted one row, when I encountered the problem with updating. I will, however, repeat the tests and provide feedback as soon as I can.
I have retested, and I am still receiving OptimisticConcurrencyException.
However, exactly one row was updated on the database, so I do not understand
how ServiceStack thinks more than one row was updated.
I have double-checked the database table and only one row exists with the primary
key being used in the generated SQL statement. I also executed the update statement
on the database and it worked as expected; one row updated.
I tried tracing the problem, but I could not follow what ServiceStack was doing on the line 553, highlighted below in AutoQueryFeature.autoCrud.cs:
public partial class AutoQueryFeature
{
private object UpdateInternal<Table>(IRequest req, object dto, string operation, IDbConnection db = null)
{
// Should only update a Single Row
var rowsUpdated = GetAutoFilterExpressions(ctx, dtoValues, out var expr, out var exprParams)
? ctx.Db.UpdateOnly<Table>(dtoValues, expr, exprParams.ToArray())
: ctx.Db.UpdateOnly<Table>(dtoValues); // PROBLEM IS IN HERE
if (rowsUpdated != 1) // rowsUpdated EQUALS 2 HERE
throw new OptimisticConcurrencyException($"{rowsUpdated} rows were updated by '{dto.GetType().Name}'");
}
}
I am using ServiceStack 5.13.2, C#9, .NET5 and MSSQL 2019 standard.
Any thoughts?
Have you tried capturing and replaying the UPDATE SQL in your RDBMS to see how many records are being updated?
You can use the BeforeExecFilter to inspect the DB Command before its executed, or to print the executed SQL to the console you can use:
OrmLiteUtils.PrintSql();
I did try the output from PrintSql and only one record is updated when using a db tool And even with the exception being thrown, a single record is still updated in the db.
I will try the BeforeExecFilter tomorrow as you suggest.
It is very strange, and I have never had any issues before with my own databases. But this is a third-party db and it is complicated to use because of transaction Id sequences.
Update: the multiple updates were being caused by database triggers on the third-party database that are used for data tracking (and not documented).
Not sure, why this is being classified as an OptimisticConcurrencyException, but maybe this is an internal issue for the database provider.
From my side, ServiceStack working correctly (as always).
1 Like
Just to add: a similar trigger is used when inserting data, causing 2 rows to be affected. But, OptimisticConcurrencyException is not thrown. When updating, the trigger affects 3 rows and the OptimisticConcurrencyException is thrown. I suspect there is a trigger problem.