Linq queries for large systems

See more:

LINQ

dataset

I have a linq queries in my .net core web API endpoint that takes to long to execute and client are timing out before the execution finishes. Below is my setup:

Table A: Contains 100 million records
Table B: 100 million records
Table C: 100 million records
Table D: 100 million records

Endpoint Payload: Contains 100k of list of Table A entities.

Process: To insert the payload in Table A, for each records in the Payload, check each of the Table A, B,C and D to make sure that certain business logic constraints are kept before insertion.
If any constraint is violated, the whole operation fail. That means each records of the 100k must be checked against the 4 table.

After successful check, a record is inserted in Table A and the ID is use to insert a corresponding record in Table B. Then the loop continues.

Server Configuration:
Windows Server 2019, IIS Server, SQL Server 2016
8 core, 16 G Ram, SSD

Request Pattern:
Sometimes, there can be 20 concurrent request from different clients with the above Payload volume.

Please, help me in the best way to optimize this flow even if redesign is needed.
Thanks

What I have tried:

Have tried looping through the payload
Have tried first checking constraints, then loop to insert, and store id in list, then construct payload for table b and bulk insert