C#•13mo ago

✅ Handling changing Keys after an insert

Hello folks, I have a function that inserts new data into a sql database. the data is workflows and conaints a hierarchy with ParentKeys and a sequence. Example data that is being pushed to the database:

  [
    {
      key: 4,
      parentKey: 0,
      sequence: 1,
      parameters: [
        {
          ...
        },
        {
          ...
        },
        ...
      ]
    },
    {
      key: 5,
      parentKey: 0,
      sequence: 2,
      parameters: [
        ...
      ]
    },
    {
      key: 6,
      parentKey: 5,
      sequence: 1,
      parameters: [
        ...
      ]
    },
    {
      key: 7,
      parentKey: 5,
      sequence: 2,
      parameters: [
        ...
      ]
    },
    {
      key: 8,
      parentKey: 0,
      sequence: 3,
      parameters: [
        ...
      ]
    }
  ]

  [
    {
      key: 4,
      parentKey: 0,
      sequence: 1,
      parameters: [
        {
          ...
        },
        {
          ...
        },
        ...
      ]
    },
    {
      key: 5,
      parentKey: 0,
      sequence: 2,
      parameters: [
        ...
      ]
    },
    {
      key: 6,
      parentKey: 5,
      sequence: 1,
      parameters: [
        ...
      ]
    },
    {
      key: 7,
      parentKey: 5,
      sequence: 2,
      parameters: [
        ...
      ]
    },
    {
      key: 8,
      parentKey: 0,
      sequence: 3,
      parameters: [
        ...
      ]
    }
  ]

26 Replies

Zil•13mo ago

The problem that i have is that when I push this data to the database i want it to generate a new Key wich all works fine, but because the objects receive a new Key the parentKey obviously doesnt match the right Key anymore. My endpoint:

c++
            foreach (var step in steps)
            {
                var testQuery = $@"
                        INSERT INTO [{databaseName}].[dbo].[stepTable] ([Type], [ParentKey], [SubWorkflowCode], [Sequence], [WorkflowKey]) 
                        OUTPUT INSERTED.[Key] 
                        VALUES (@Type, CASE WHEN @ParentKey = 0 THEN NULL ELSE @ParentKey END, @SubWorkflowCode, @Sequence, @WorkflowKey);";


                var newKey = await connection.QueryFirstOrDefaultAsync<int>(testQuery, step);

                foreach (var parameter in step.Parameters)
                {
                    var parameterQuery = $@"
                                INSERT INTO [{databaseName}].[dbo].[parameterTable] ([Name], [Source], [Value], [WorkflowStepKey], [GroupCode], [EditedByCustomer]) 
                                VALUES ('{parameter.Name}', '{parameter.Source}', '{parameter.Value}', {newKey}, NULL, 0);";

                    Console.WriteLine("Parameter Query: " + parameterQuery);

                    await connection.ExecuteAsync(parameterQuery);
                }
            }

c++
            foreach (var step in steps)
            {
                var testQuery = $@"
                        INSERT INTO [{databaseName}].[dbo].[stepTable] ([Type], [ParentKey], [SubWorkflowCode], [Sequence], [WorkflowKey]) 
                        OUTPUT INSERTED.[Key] 
                        VALUES (@Type, CASE WHEN @ParentKey = 0 THEN NULL ELSE @ParentKey END, @SubWorkflowCode, @Sequence, @WorkflowKey);";


                var newKey = await connection.QueryFirstOrDefaultAsync<int>(testQuery, step);

                foreach (var parameter in step.Parameters)
                {
                    var parameterQuery = $@"
                                INSERT INTO [{databaseName}].[dbo].[parameterTable] ([Name], [Source], [Value], [WorkflowStepKey], [GroupCode], [EditedByCustomer]) 
                                VALUES ('{parameter.Name}', '{parameter.Source}', '{parameter.Value}', {newKey}, NULL, 0);";

                    Console.WriteLine("Parameter Query: " + parameterQuery);

                    await connection.ExecuteAsync(parameterQuery);
                }
            }

I already catch the new key of each Step that got created in the newKey variable. Any idea how i could handle this? Thanks in advance!

HimmDawg•13mo ago

You could make a Dictionary that keeps track of all pairs of (previousKey, newKey) and in a second iteration over your objects, you change all parentkeys to whatever the value is in the dictionary

Zil•13mo ago

hmm, yea that could work. the thing is that i cant insert anything with a "wrong" parentKey because it will give me FK conflicts. How could i approach that? because if I understand correctly you are saying just insert it into the database(While doing this save the old and new Key) and then after update all the parentKey's with the Dictionary. But I cant insert data with the wrong parentKey

HimmDawg•13mo ago

I assume that key is the PK with auto_increment then?

Zil•13mo ago

Yes

HimmDawg•13mo ago

Well if you want to insert data that provides its own keys, you cannot use auto_increment then

Zil•13mo ago

I dont fully understand what you mean. But maybe it helps if i give a little more context: Basicly what im doing is pulling the workflows into a application and doing all kinds of mutations on them where I change there parent-child relations and sequences. When i click save it first deletes all the workflows(for specific scope) and then inserts them back into the database. At that point they receive the next available Key. so if they first had the keys 1, 2 ,3 After mutation and insert they will get 4, 5, 6

HimmDawg•13mo ago

Yes, I understand that. But you've just said yourself: You nuke a certain number of workflows. If one of the objects has a parentKey for an object that was just deleted, the fk constraint will always fail. So I'd just recommend to remove auto_increment from that table. Is it necessary to delete certain worksflows? Otherwise i'd just update them

Zil•13mo ago

no i cant change anything to the database unfortunately. And it is necessary to delete the workflows, i cant just update them. Right now its just about editing existing workflows. but in the near future it also must be possible to add completely new objects I think the Dictionary solution is really close to something that could work. I just need a work around for this What I thought of was just setting the parentKey to 0 in the first iteration and then update them after. but thats stupid because you lose the reference for the second iteration

HimmDawg•13mo ago

Hummm, then maybe combine the dictionary approach + insert those objects without parent first The fk constraint shouldn't fail then

Zil•13mo ago

hmm, idk if that is a good approach because a child of a parent can also be a parent to another child. if that makes sense I also thought of grouping the workflows where each array starts with the parent and the objects after it are his children but then i will face this issue aswell

HimmDawg•13mo ago

Yes, but there must be objects without parent, no?

Zil•13mo ago

Yes

HimmDawg•13mo ago

There should be no trouble when inserting those first

Zil•13mo ago

But what do we gain from inserting the objects that have a parentKey of 0 first ?

HimmDawg•13mo ago

You can create the dictionary and change other objects' parentKey.

Zil•13mo ago

yes i see what you mean. this works for the "highest" workflows(that have a parentKey of 0). then you can indeed update the children that have those highest workflows as a parent. but those children can be a parent themselfs.

HimmDawg•13mo ago

Yes, but now you're free to insert that object to the db since the fk constraint wont fail anymore. Then add the old and new key to the dictionary again, so that the child objects know about the new key of their parent

Zil•13mo ago

So you are saying: 1. Insert the highest workflows that have a parentKey of 0, save old and new Keys for these objects. 2. Insert the workflows that have one of the old Keys as parentKey. save old a new Keys of these objects. Basiscly repeat this proces?

HimmDawg•13mo ago

Yes. In 2. you need to change the parentkeys of the objects accordingly of course

Zil•13mo ago

Yes Insert them with the new keys from the from the dictionary into the parentKey Totally different: this feels like a lot of work for a endpoint or no ?

HimmDawg•13mo ago

Given the circumstances and constraints, i don't see much other possibilties fluffyFoxThink

maybe somebody else has a better solution

Zil•13mo ago

Yea I wouldn't know any other possibility either... And i dont think people will still join a post with 31 comments already in it harold

HimmDawg•13mo ago

You never know what happens if you drop a link to this thread in #help-0 and ask for more opinions foxNosy

Zil•13mo ago

Okay did that, thanks for your help. Ill just start on the solution we talked about and maybe somebody will still chime in! Thanks!!

Accord•13mo ago

Was this issue resolved? If so, run /close - otherwise I will mark this as stale and this post will be archived until there is new activity.

Gaming

Programming

✅ Handling changing Keys after an insert