C#•2y 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

ZilOP•2y 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•2y 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

ZilOP•2y 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•2y ago

I assume that key is the PK with auto_increment then?

ZilOP•2y ago

Yes

HimmDawg•2y ago

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

ZilOP•2y 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•2y 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

ZilOP•2y 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•2y ago

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

ZilOP•2y 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•2y ago

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

ZilOP•2y ago

Yes

HimmDawg•2y ago

There should be no trouble when inserting those first

ZilOP•2y ago

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

HimmDawg•2y ago

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

ZilOP•2y 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•2y 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

ZilOP•2y 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•2y ago

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

ZilOP•2y 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•2y ago

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

maybe somebody else has a better solution

ZilOP•2y 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•2y ago

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

ZilOP•2y 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•17mo 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