Negative identities and object graph saving - lobodava/artisan-orm GitHub Wiki

Background reading. This page is a quick reference for the negative-identities + MERGE-with-translation-tables technique. The full long-form story behind it — why the problem is hard, how the pattern was found, and which trade-offs it makes — is in the re-published article: How to Save an Object Graph in Master-Detail Relationship with One Stored Procedure.

4.1.0 preparation. This page keeps a reduced teaching schema and mapper-driven preparation example. It is not a deployment script for the current sample: the current Record TVP has nine columns, and the repository returns a saved graph. Use the linked current SQL types and mappers together. Version 4.1.0 is being prepared; this does not announce a published package.

Let's suppose that a web client sends to our web server a graph consisting of three objects:

• GrandRecord
• Record
• ChildRecord

Let it be a collection of GrandRecords where:

• each GrandRecord has a collection of Records,
• and each Record has a collection of ChildRecords,
• Id value is integer and autogenerated by Database,
• and while an object is not saved in Database, Id has value = 0.

Here is the sample of object graph array (or object graphs) :

                      Id, Name
GrandRecord           1,  (A)
    Record            |-- 2, (A)A
        ChildRecord       |-- 3, (A)Aa
        ChildRecord       |-- 0, (A)Ab
    Record            |-- 0, (A)B
        ChildRecord       |-- 0, (A)Ba
        ChildRecord       |-- 0, (A)Bb
GrandRecord           0,  (B)
    Record            |-- 0, (B)A

Or the same as a JavaScript object fragment; the linked article contains the complete JSON payload:

grandRecords: [
    {
        id: 1,
        name: "(A)",
        records: [
            {
                id: 2,
                name: "(A)A",
                childRecords: [
                    {
                        id: 3,
                        name: "(A)Aa",
                    },
                    {
                        id: 0,
                        name: "(A)Ab",
                    },
                ]
            },
            {
                id: 0,
                name: "(A)B",
                childRecords: [
                    {
                        id: 0,
                        name: "(A)Ba",
                    },
                    {
                        id: 0,
                        name: "(A)Bb",
                    },
                ]
            }        
        ]        
    },
    {
        id: 0,
        name: "(B)",
        records: [
            {
                id: 0,
                name: "(B)A",
                childRecords: []
            }
        ]
    }
]

On the web server, the incoming JSON payload is deserialized into object graphs of three classes:

public class GrandRecord
{
    public  Int32          Id       { get; set; }
    public  String         Name     { get; set; } = "";
    public  IList<Record>  Records  { get; set; } = new List<Record>();
}

public class Record
{
    public  Int32               Id             { get; set; }
    public  Int32               GrandRecordId  { get; set; }        
    public  String              Name           { get; set; } = "";
    public  IList<ChildRecord>  ChildRecords   { get; set; } = new List<ChildRecord>();
}

public class ChildRecord
{
    public  Int32   Id        { get; set; }
    public  Int32   RecordId  { get; set; }
    public  String  Name      { get; set; } = "";
}

Note that Record.GrandRecordId and ChildRecord.RecordId after deserialization are 0.

The object graphs has to be saved with one stored procedure into three database tables:

create table dbo.GrandRecords
(
    Id    int          not null  identity  primary key clustered,
    Name  varchar(30)  not null
);

create table dbo.Records
(
    Id             int          not null  identity  primary key clustered,
    GrandRecordId  int          not null  ,
    Name           varchar(30)  not null  ,
    
    foreign key (GrandRecordId) references dbo.GrandRecords (Id) on delete cascade
);

create table dbo.ChildRecords
(
    Id        int          not null  identity  primary key clustered,
    RecordId  int          not null  ,
    Name      varchar(30)  not null  ,
    
    foreign key (RecordId) references dbo.Records (Id) on delete cascade
);

Artisan.Orm uses the User Defined Table Types to save objects to database. In our case, UDTTs look like this:

create type dbo.GrandRecordTableType as table
(
    Id    int          not null   primary key clustered,
    Name  varchar(30)  not null    
);

create type dbo.RecordTableType as table
(
    Id             int          not null   primary key clustered,
    GrandRecordId  int          not null   ,
    Name           varchar(30)  not null

);

create type dbo.ChildRecordTableType as table
(
    Id        int          not null   primary key clustered,
    RecordId  int          not null   ,
    Name      varchar(30)  not null    
);

The procedure signature is shown here; its complete body appears below:

create procedure dbo.SaveGrandRecords
    @GrandRecords  dbo.GrandRecordTableType  readonly,
    @Records       dbo.RecordTableType       readonly,
    @ChildRecords  dbo.ChildRecordTableType  readonly
as

The reduced example's write-only repository method is shown below. The current sample instead returns IList<GrandRecord> with the generated database IDs; see the current-source notes at the end of this page.

public void SaveGrandRecords(IList<GrandRecord> grandRecords)
{
    var records = grandRecords.SelectMany(gr => gr.Records);
    var childRecords = records.SelectMany(r => r.ChildRecords);

    ExecuteCommand(cmd =>
    {
        cmd.UseProcedure("dbo.SaveGrandRecords");

        cmd.AddTableParam( "@GrandRecords", grandRecords );
        cmd.AddTableParam( "@Records"     , records      );
        cmd.AddTableParam( "@ChildRecords", childRecords );
    });
}

The order of the three AddTableParam calls is significant in this pattern. Each call materializes its TVP immediately and invokes the mapper's CreateDataRow. The parent mapper assigns temporary IDs and propagates those IDs into its children before the child collection is materialized. Keep the collections non-null, and normalize or reject null child collections at the input boundary.

Because our tables in Database are linked by foreign keys GrandRecordId and RecordId, we have somehow to persist that link while converting the object graphs into the separate table parameters.

What is more, the identities of new objects have to be unique! Otherwise, we cannot distinguish the Records of GrandRecord (A) from the Records of GrandRecord (B).

Here Negative Identities come up on stage. Artisan.Orm provides three static classes with only Next property:

• Int64NegativeIdentity
• Int32NegativeIdentity
• Int16NegativeIdentity

The Next property supplies increasing temporary IDs from the integer type's minimum value through -1. In 4.1.0, all three generators permanently throw InvalidOperationException after that finite range is exhausted; they never wrap to zero or positive values and never reset. Int16 has only 32,768 negative values per generator lifetime, so choose Int32 or Int64 for larger workloads. These values are correlation keys, not persistent ordering values.

Use these temporary IDs only for schemas where new integer-keyed objects use Id == 0; keep existing nonzero IDs. This is an application persistence strategy, not a requirement for every mapper. The example values below assume a fresh generator sequence; do not depend on particular numeric values in application code.

Artisan.Orm cmd.AddTableParam extensions use Mappers and those Mappers do the magic with the Negative Identities in CreateDataRow methods:

// GrandRecordMapper

public static Object[] CreateDataRow(GrandRecord obj)
{
    if (obj.Id == 0) 
        obj.Id = Int32NegativeIdentity.Next;

    foreach (var record in obj.Records)
        record.GrandRecordId = obj.Id;

    return new object[]
    {
        obj.Id    ,
        obj.Name
    };
}

// RecordMapper

public static Object[] CreateDataRow(Record obj)
{
    if (obj.Id == 0) 
        obj.Id = Int32NegativeIdentity.Next;

    foreach (var childRecord in obj.ChildRecords)
        childRecord.RecordId = obj.Id;

    return new object[]
    {
        obj.Id             ,
        obj.GrandRecordId  ,
        obj.Name
    };
}

// ChildRecordMapper

public static Object[] CreateDataRow(ChildRecord obj)
{
    if (obj.Id == 0) 
        obj.Id = Int32NegativeIdentity.Next;

    return new object[]
    {
        obj.Id        ,
        obj.RecordId  ,
        obj.Name
    };
}

So after the Mappers do their work, the stored procedure receives the following table valued parameters:

These illustrative numbers follow the mapper-based order used here: materialize all grand records, then all records, then all children. The article's explicit depth-first preparation loop assigns different numbers. Both work because the relationships use consistent keys, not the numerical sequence itself.

@GrandRecords

@Records

@ChildRecords

In order to update existing, insert new and delete old data, SQL Server uses the MERGE statement.

The MERGE statement has the OUTPUT clause. The OUTPUT in MERGE statement can collect just inserted Ids as well as Ids from the source (parameter) table.

If a record exists in a table inserted.id contains the existing Id and MERGE does UPDATE.

If a record does not exist in a table inserted.id contains the new Id and MERGE does INSERT.

So the technique "to save all three tables with correct foreign keys" is to collect InsertedId-ParamId pairs from the first table and translate those values for the second. And do the same for the second and third tables.

Both child sources below translate foreign keys through INNER JOIN to an ID map. The TVP parameters remain READONLY: never try to UPDATE an input TVP to replace temporary IDs. Each supplied child must belong to a supplied parent; otherwise an inner join can omit it.

Here is the SQL Server 2012+ stored procedure for the reduced teaching schema. It wraps all three merges in a transaction and commits or rolls back only when it started that transaction. An existing caller-owned transaction remains the caller's responsibility; an error must reach that owner so it can roll back.

create procedure dbo.SaveGrandRecords
    @GrandRecords  dbo.GrandRecordTableType  readonly,
    @Records       dbo.RecordTableType       readonly,
    @ChildRecords  dbo.ChildRecordTableType  readonly
as
begin
    set nocount on;

    declare @StartTranCount int = @@trancount;
    begin try
        if @StartTranCount = 0 begin transaction;

    declare @GrandRecordIds table (  -- translation table
        InsertedId  int  primary key, 
        ParamId     int  unique
    );
    
    declare @RecordIds table (       -- translation table
        InsertedId  int     primary key, 
        ParamId     int     unique, 
        [Action]    nvarchar(10)
    );

    -- save GrandRecords 

    merge into dbo.GrandRecords as target
        using 
        (
            select Id, Name from @GrandRecords
        ) 
        as source on source.Id = target.Id

    when matched then
        update set                
            Name = source.Name        

    when not matched by target then                                                         
        insert ( Name )
        values ( source.Name )

    output            -- collecting translation ids
        inserted.Id,
        source.Id
    into @GrandRecordIds ( 
        InsertedId , 
        ParamId    );


    -- save Records 

    merge into dbo.Records as target
    using 
    (
        select
            Id             , 
            GrandRecordId  =  ids.InsertedId,   -- Id translation target
            Name    
        from
            @Records r
            inner join @GrandRecordIds ids 
                on ids.ParamId = r.GrandRecordId -- Id translation source
    ) 
    as source on source.Id = target.Id

    when matched then
        update set
            GrandRecordId  =  source.GrandRecordId, 
            Name           =  source.Name    

    when not matched by target then                                                         
        insert (    
            GrandRecordId , 
            Name          )
        values (
            source.GrandRecordId , 
            source.Name          )

    when not matched by source 
        and target.GrandRecordId in (select InsertedId from @GrandRecordIds) then
           delete

    output                 -- collecting translation Ids
        isnull(inserted.Id, deleted.Id),
        isnull(source.Id, deleted.Id), 
        $action
    into @RecordIds (
        InsertedId  , 
        ParamId     , 
        [Action]    );

            
    delete from @RecordIds where [Action] = 'DELETE';


    -- save ChildRecords

    merge into dbo.ChildRecords as target
        using 
        (
            select
                Id        ,
                RecordId  =  ids.InsertedId,    -- Id translation target
                Name        
            from
                @ChildRecords cr
                inner join @RecordIds ids 
                    on ids.ParamId = cr.RecordId -- Id translation source
        ) 
        as source on source.Id = target.Id

    when matched then
        update set
            RecordId = source.RecordId , 
            Name     = source.Name

    when not matched by target then
        insert (    
            RecordId , 
            Name     )
        values (
            source.RecordId , 
            source.Name     )
        
    when not matched by source and target.RecordId in (select InsertedId from @RecordIds) then
        delete;

        if @StartTranCount = 0 commit transaction;
    end try
    begin catch
        if @StartTranCount = 0 and xact_state() <> 0 rollback transaction;
        throw;
    end catch;
end;

Keep source keys unique and index the relevant join columns. The sample UDTTs use PRIMARY KEY CLUSTERED on Id, matching the source.Id = target.Id predicates. These indexes support efficient matching; they do not guarantee that a MERGE is deadlock-free or processes its rows in source-ID order. The original article retains its historical assertion and explains the correction. See also MERGE index guidance.

The graph algorithm uses OUTPUT inserted.Id, source.Id to capture a pair for the same affected row. Its next stage joins ids.ParamId = r.GrandRecordId, so children receive the correct parent ID even if the parents were processed in another order. Do not zip input and output arrays by position or calculate a new ID from the preceding one. SQL Server does not promise matching order between table changes and rows written by OUTPUT; see the OUTPUT remarks.

Use the mechanism that matches the required kind of order:

For application ordering, assign a position within each parent's collection, include it in the TVP and persistent table, and insert/update it in the procedure. Read with an order such as ORDER BY GrandRecordId, SortOrder, Id, using the key as a deterministic tie-breaker. A manual mapper can derive the position from the collection; the C# model need not mirror every TVP column. SortOrder is a proposed application schema extension, not a column already present in the current sample.

The narrower identity-allocation guarantee belongs to the documented INSERT ... SELECT ... ORDER BY form. It does not extend to MERGE, physical insertion order, or global commit chronology. See INSERT limitations and the article's standalone ordered-insert example.

Deadlocks need a separate concurrency policy: use consistent resource access order, short transactions, appropriate indexes and isolation, and inspect the actual deadlock graph. Artisan.Orm supplies no automatic retry policy. A bounded retry must repeat the whole transaction after rollback, coordinate caller-owned transactions, and account for external side effects. See transaction retry guidance, including the distinction between the THROW; example above and the current sample's error-number-changing RAISERROR wrappers.

This procedure deletes omitted records only within the supplied grand-record IDs, and omitted child records only within the supplied surviving record IDs. It does not replace the entire database graph. The sample foreign keys use ON DELETE CASCADE, so deleting a record also removes its child records.

Send complete descendant collections for the branches being saved; this is a snapshot operation, not a partial patch. A grand record not supplied in the top-level TVP is left unchanged.

The current RecordTableType contains nine columns: Id, GrandRecordId, Name, RecordTypeId, Number, Date, Amount, IsActive, and Comment. The three-column TVP and mapper on this page belong to the reduced teaching schema. Use the current type, mapper and procedure as a set when targeting the sample database.

The current SaveGrandRecords procedure returns three ordered result sets. Its repository materializes a new saved graph and joins those results. Use that returned graph for the generated positive IDs: sending TVPs prepares temporary IDs in the input objects, but does not rewrite those input objects with their final database IDs.

See also full examples of:

• saving stored procedure
• mappers
• negative identities
• repository
• repository tests
• Background reading (long-form): How to Save an Object Graph in Master-Detail Relationship with One Stored Procedure