AutoCrud: does [AutoId] use NEWSEQUENTIALID() on MSSQL?

ServiceStack.OrmLite SQL Server
1

I read that [AutoId] is using NEWID(), is it possible for NEWSEQUENTIALID() to be used, where available?

2

You would need to create a custom SQL Server Dialect Provider over the version you’re using and override the GetAutoIdDefaultValue() method, e.g:

public class CustomSqlServerDialectProvider : SqlServer2012OrmLiteDialectProvider
{
    public new static CustomSqlServerDialectProvider Instance = 
        new CustomSqlServerDialectProvider ();

    public override string GetAutoIdDefaultValue(FieldDefinition fieldDef) =>
        fieldDef.FieldType == typeof(Guid) 
            ? "NEWSEQUENTIALID()" 
            : null;
}

Then use that in your DB Factory, e.g:

var dbFactory = new OrmLiteConnectionFactory(
    connectionString,  
    CustomSqlServerDialectProvider.Instance);
3

Excellent, I have implemented the override, thank you.

However, both with the standard SqlServer2019OrmLiteDialectProvider (using NEWID()) and my custom version (using NEWSEQUENTIALID()), when I perform a POST without supplying a guid value for the Id (primary key), the empty guid value (0s) is being inserted.

I am using my local test database, which is MSSQL 2019 Developer Edition (64-bit), and the database is at 150 compatibility (MSSQL 2019).

My table (simplified) is system versioned, and defined as:

CREATE TABLE Simple.SimpleTest4 (
  Id  UNIQUEIDENTIFIER DEFAULT NEWSEQUENTIALID(),
  Version nvarchar(50)  NULL,
  SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
  SysEndTime  DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,
  PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime), 
  CONSTRAINT simple_simpletest4_pk PRIMARY KEY NONCLUSTERED (Id)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = Simple.SimpleTest4_History));

I am using AutoQuery plugin with GenerateCrudServices, and I have not added any service model or service interface code for the SimpleTest4 table.

Any suggestions?
Keith

4

You’d need to Annotate the Data model with the [AutoId] attribute, or the [AutoPopulate] attribute on the Request DTO to have AutoQuery populate it in C#, see docs at:

https://docs.servicestack.net/autoquery-crud#auto-guids

5

Okay, I did read that, but I was hopeful that I could avoid creating a model and rely purely on the auto features. Which, by the way, works really well when the JSON passed to the service POST endpoint contains all of the required column data.

Is there an other “override” available to provide default values (e.g. Guid.NewGuid()) when named fields, such as Id, are missing when calling a POST?

6

Auto populating fields is what the (also documented) [AutoDefault] and [AutoPopulate] attributes do, which can populate Guid fields with Eval="nguid".

7

Whilst I recommend taking over declaration of your declarative Data Models whenever you need to customize them, there’s also an opportunity to modify the code generation used to generate the models, e.g. something like:

Plugins.Add(new AutoQueryFeature 
{
    GenerateCrudServices = new GenerateCrudServices
    {
        TypeFilter = (type, req) => 
        {
            if (type.Name == "SimpleTest4") 
            {
                var id = type.Properties.First(x => x.Name == "Id");
                id.AddAttribute(new AutoIdAttribute());
            }
        },
    }
});
8

I like the TypeFilter approach very much; it worked great on multiple tables (by omitting the type.Name check).

The data model approach would offer additional benefits, because all my tables have audit columns.
However, on first attempt, I failed to get AutoId or AutoPopulate working; the empty guid value was still being inserted…

I appreciate your support, best I have had from any developer product in a very long time.

1 Like