c # – Existing multi-element payload with EF Core Performance Improvements

I have a .NET CORE EF application that was first set up as a database and is supposed to host a payload containing items to store in my database. Their payloads can be very large and contain several "securities", and each "security" has several "positions / transactions" to be stored in the database. In some cases, the duration of the expiration may increase exponentially, and some of the payloads may take up to 20 minutes. The ultimate goal is to reduce the time it takes to look through and persist in this code. The memory function is not the problem here (afaik). I think the problem is the nested passage of the payload and all their positions. Any help that speeds this up will be welcomed. Here is my calling function:

                private async task Persist (AccPackBody package)
{
bool hasModifications = false, hasErrors = false;
var secGuidsWithPositions = new list();

using (var dbContext = new NewportMasterContext (Configs.MasterDbConnString))
{
dbContext.Database.SetCommandTimeout (300);

foreach (var mappedSecurity in pack.Internal)
{
mappedSecurity.FirmId = pack.FirmId;

SEC_IDENTIFIER existsSecurity = null;
To attempt
{

existsSecurity = wait for TryFindSecId (mappedSecurity, pack.FirmId.Value, dbContext);

}
catch (exception ex)
{
await Logger.Send (LogType.Error, "An error occurred while trying to clone security");
}

if (existsSecurity == null)
{
waitit Logger.Send (pack, LogType.Error, $ "Missing security for: Identifier: {mappedSecurity.IdentifierName} Value: {mappedSecurity.IdentifierValue}, Firm {pack.FirmId}, Currency: {mappedSecurity.Currency}");
throw new LoggedException ();
}
otherwise
{
mappedSecurity.SecurityGuid = existsSecurity.Security_UID;

POSITION[] updatedPositions = null;
if (mappedSecurity.Postitions! = null && mappedSecurity.Postitions.Length> 0)
{



updatedPositions = HandlePositions (pack, dbContext, pack.State, pack.AccountUID.Value, mappedSecurity, pack.StatementDate.Value);

secGuidsWithPositions.Add (mappedSecurity.SecurityGuid.Value);

mappedSecurity.Postitions = updatedPositions! = null && updatedPositions.Count ()> 0
? aktualisiertePositionen
: new position[0];

if (updatedPositions! = null && updatedPositions.Count ()> 0)
hasModifications = true;
}

// EditTransaction
TRANSACTION[] updatedTransactions = null;
if (mappedSecurity.Transactions! = null && mappedSecurity.Transactions.Length> 0)
{

mappedSecurity.Transactions = updatedTransactions! = null && updatedTransactions.Count ()> 0
? aktualisierteTransactions
: new TRANSACTION[0];

if (updatedTransactions! = null && updatedTransactions.Count ()> 0)
hasModifications = true;
}
}
}

if (! hasErrors && dbContext.ChangeTracker.HasChanges ())
{
To attempt
{

await dbContext.SaveChangesAsync ();


}
catch (exception ex)
{
waitit Logger.Send (pack, LogType.Warning, "Error changing db, reset:" + ex.ToString ());
throw new LoggedException ();
}
}
}

Return pack;
} 

Here are my three functions calling the above method – here are many slowdowns:

                private async task TryFindSecId (AccMappedSecurity mappedSecurity, long firmId, newportMasterContext dbContext, Guid? PackUid = null)
{

var existsSecurities = dbContext.SEC_IDENTIFIER
.Where (x => x.IdentifierName == mappedSecurity.IdentifierName
&& mappedSecurity.IdentifierValue == x.Identifier
&& x.FirmID == firmId
&& (string.IsNullOrEmpty (mappedSecurity.Currency) || x.SECURITY.Currency == mappedSecurity.Currency) && x.SECURITY.ChangeType! = "D");

var existsSecurity = existsSecurities.FirstOrDefault ();

if (existsSecurity == null)
{
var cloneResult = ClonePostition.Clone expect (
new ClonePositionRequest
{
IdentifierName = mappedSecurity.IdentifierName,
IdentifierValue = mappedSecurity.IdentifierValue,
NewCurrency = mappedSecurity.Currency,
FirmId = FirmId
},
Logger.LocalLog
);



if (cloneResult.Code! = HttpStatusCode.InternalServerError)
{
existsSecurity = dbContext.SEC_IDENTIFIER
.FirstOrDefault (x => x.IdentifierName == mappedSecurity.IdentifierName)
&& mappedSecurity.IdentifierValue == x.Identifier
&& x.FirmID == firmId
&& (string.IsNullOrEmpty (mappedSecurity.Currency) || x.SECURITY.Currency == mappedSecurity.Currency));


}
otherwise
{
waitit Logger.Send (LogType.Error, "Internal server error on clone function - clone failed" + cloneResult.Code);
}


}
otherwise
{

}

return existing security;
}





private location[] HandlePositions (AccPackBody reqPayload, newportMasterContext dbContext, Status PackState, Guid accountGuid, AccMappedSecurity mappedSecurity, DateTime statementDate, Guid? PackUid = null)
{
if (useDiagnosticLogging) LocalLoggingUtilities.WriteDiagnosticLog (_logText, packUid.Value, "hit handle positions");

var handlePositions = new list();
int posCnt = mappedSecurity.Postitions.Length;
if (useDiagnosticLogging) LocalLoggingUtilities.WriteDiagnosticLog (_logText, packUid.Value, "Deserialization of assigned positions");
list deserializedPosition = mappedSecurity.Postitions
.Select (x => JsonConvert.DeserializeObject(x.ToString (), Configs.JsonCircularRefFixConfig))
.OrderBy (x => x.DateStart)
.List();

var updatedPosDetails = new list();
POSITION prevailing position = zero;

if (useDiagnosticLogging) LocalLoggingUtilities.WriteDiagnosticLog (_logText, packUid.Value, "Check for prevailing position.");

prevailingPosition = dbContext.POSITION
.Include (x => x.POSITION_DETAIL)
.Include (x => x.POSITION_DETAIL) .ThinInclude (x => x.POSITION_BOND)
.Include (x => x.POSITION_DETAIL) .ThinInclude (x => x.POSITION_FX)
.Include (x => x.POSITION_DETAIL) .ThinInclude (x => x.POSITION_OPTION)
.Include (x => x.POSITION_DETAIL) .ThinInclude (x => x.POSITION_PRICE)
.Where (x => x.Account_UID == accountGuid
&& x.Security_UID == mappedSecurity.SecurityGuid
&& x.SecurityFirmID == mappedSecurity.FirmId
&& (x.DateStart.Date <= statementDate) && (! x.DateEnd.HasValue || x.DateEnd.Value.Date < statementDate) 
                && x.ChangeType != "D" 
                && x.POSITION_DETAIL.Any(pd => pd.Genesis == "FEED" &&
pd.ChangeType! = "D"))
.OrderByDescending (x => x.DateStart)
.ToList (). FirstOrDefault ();

if (useDiagnosticLogging) LocalLoggingUtilities.WriteDiagnosticLog (_logText, packUid.Value, "Looping Inbound Positions.");
foreach (var incomingPosition in deserializedPositions)
{
if (useDiagnosticLogging) LocalLoggingUtilities.WriteDiagnosticLog (_logText, packUid.Value, string.Format ("Take new incoming position: {0}"), incomingPosition.Position_UID.ToString ());
incomingPosition.Account_UID = accountGuid;
incomingPosition.Security_UID = mappedSecurity.SecurityGuid.Value;
incomingPosition.SecurityFirmID = mappedSecurity.FirmId.Value;
incomingPosition.Position_UID = Guid.NewGuid ();
if (prevailingposition == null)
{
EntityEntry newPosition = null;
dbContext.POSITION.Add (incomingPosition);
newPosition = dbContext.Entry (incomingPosition);
newPosition.CurrentValues.SetValues ​​(new {ChangeType = "I"});
foreach (var posDetail in incomingPosition.POSITION_DETAIL.Where (x => x.ChangeType.ToUpper ()! = "D"))
{
EntityEntry newPositionDetail = null;
posDetail.Detail_UID = Guid.NewGuid ();
dbContext.POSITION_DETAIL.Add (posDetail);
newPositionDetail = dbContext.Entry (posDetail);
newPositionDetail.CurrentValues.SetValues ​​(new {ChangeType = "I"});
updatedPosDetails.Add (posDetail);
}

managedPositions.Add (incomingPosition);

if (useDiagnosticLogging) LocalLoggingUtilities.WriteDiagnosticLog (_logText, packUid.Value, "Inserted:") + incomingPosition.Position_UID.ToString ());
}
otherwise
{
if (prevailingPosition.DateStart.Date == incomingPosition.DateStart.Date)
{
foreach (var posDetail in prevailingposition.POSITION_DETAIL.Where (x => x.ChangeType.ToUpper ()! = "D"))
{
EntityEntry positionDetailToRemove = dbContext.Entry (posDetail);
positionDetailToRemove.CurrentValues.SetValues ​​(new)
{
ChangeDate = DateTime.Now,
ChangeType = "D"
});
}
EntityEntry positionToRemove = dbContext.Entry (prevailing position);
positionToRemove.CurrentValues.SetValues ​​(new)
{
ChangeDate = DateTime.Now,
ChangeType = "D"
});

EntityEntry newPosition = null;
dbContext.POSITION.Add (incomingPosition);
newPosition = dbContext.Entry (incomingPosition);
newPosition.CurrentValues.SetValues ​​(new {ChangeType = "I"});
managedPositions.Add (incomingPosition);

if (useDiagnosticLogging) LocalLoggingUtilities.WriteDiagnosticLog (_logText, packUid.Value, "insert:" + incomingPosition.Position_UID.ToString ());

foreach (var posDetail in incomingPosition.POSITION_DETAIL.Where (x => x.ChangeType.ToUpper ()! = "D"))
{
EntityEntry newPositionDetail = null;
posDetail.Detail_UID = Guid.NewGuid ();
dbContext.POSITION_DETAIL.Add (posDetail);
newPositionDetail = dbContext.Entry (posDetail);
newPositionDetail.CurrentValues.SetValues ​​(new {ChangeType = "I"});
updatedPosDetails.Add (posDetail);
}
}
else if (prevailingPosition.DateStart.Date <incomingPosition.DateStart.Date.)
&& PositionHasChange (prevailingposition, incomingPosition, packUid))
{
EntityEntry newPosition = null;
dbContext.POSITION.Add (incomingPosition);
newPosition = dbContext.Entry (incomingPosition);
newPosition.CurrentValues.SetValues ​​(new {ChangeType = "I"});
managedPositions.Add (incomingPosition);

if (useDiagnosticLogging) LocalLoggingUtilities.WriteDiagnosticLog (_logText, packUid.Value, "insert:" + incomingPosition.Position_UID.ToString ());

foreach (var posDetail in incomingPosition.POSITION_DETAIL.Where (x => x.ChangeType.ToUpper ()! = "D"))
{
EntityEntry newPositionDetail = null;
posDetail.Detail_UID = Guid.NewGuid ();
dbContext.POSITION_DETAIL.Add (posDetail);
newPositionDetail = dbContext.Entry (posDetail);
newPositionDetail.CurrentValues.SetValues ​​(new {ChangeType = "I"});
updatedPosDetails.Add (posDetail);
}
}
else if (prevailingPosition.DateStart.Date <incomingPosition.DateStart.Date.)
&&! PositionHasChange (prevailing position, incomingPosition, packUid))
{
}
}
}

return donePositions.ToArray ();
}





private transaction[] HandleTransctions (newportMasterContext dbContext, AccPackBody package, AccMappedSecurity mappedSecurity, Guid? PackUid = null)
{
object[] transactionToProcess = new object[0];
var managedTransactions = new list();

foreach (var trans in mappedSecurity.Transactions)
{
TRANSACTION managedTransaction = null,
mappedTransaction = null;

mappedTransaction = JsonConvert.DeserializeObject(trans.ToString (), Configs.JsonCircularRefFixConfig);
mappedTransaction.Security_UID = mappedSecurity.SecurityGuid.Value;

//CHOP
if (string.IsNullOrWhiteSpace (mappedTransaction.BaseCurrency))
{
mappedTransaction.BaseCurrency = "USD";
}

// missing transId - drop out
if (mappedTransaction.Reversal && string.IsNullOrWhiteSpace (mappedTransaction.TranID))
{
Logger.Send (pack, LogType.Warning, "Cancellation transaction missing a transaction ID");
throw new LoggedException ();
}
otherwise
{
//if(mappedTransaction.Reversal &&! string.IsNullOrWhiteSpace (mappedTransaction.TranID))
if (mappedTransaction.Reversal)
{
list transactionInDb = dbContext.TRANSACTION
.Where (x => x.Account_UID == pack.AccountUID
&& x.Security_UID == mappedSecurity.SecurityGuid
&& x.SecurityFirmID == mappedSecurity.FirmId
&& x.TradeDate.Date == mappedTransaction.TradeDate.Date
&& x.TranID == mappedTransaction.TranID
&&! x.Reversal
&& x.ChangeType! = "D")
.List();

if (transactionInDb.Count == 1)
{
mappedTransaction.Visible = false;
TRANSACTION transactionInDb = transactionInDb.FirstOrDefault ();
transactionInDb.Visible = false;
}
}

mappedTransaction.SecurityFirmID = mappedSecurity.FirmId.Value;
mappedTransaction.Account_UID = pack.AccountUID.Value;

if (mappedTransaction? .Comment? .Length> = 100)
{
mappedTransaction.Comment = mappedTransaction.Comment.Substring (0,96) + "...";
}

mappedTransaction.Tran_UID = Guid.NewGuid ();

var comparer = new TransactionComparer ();
EntityEntry transactionToInsert = null;
dbContext.TRANSACTION.Add (mappedTransaction);
transactionToInsert = dbContext.Entry (mappedTransaction);
transactionToInsert.CurrentValues.SetValues ​​(new {ChangeType = "I"});

if (useDiagnosticLogging) LocalLoggingUtilities.WriteDiagnosticLog (_logText, packUid.Value, "inserted:" + mappedTransaction.Tran_UID.ToString ());

if (transactionToInsert! = null)
{
transactionToInsert.CurrentValues.SetValues ​​(new {ChangeDate = DateTime.Now});
}

chargedTransaction = transactionToInsert? .Entity;

if (handlingTransaction! = null)
{
managedTransactions.Add (managedTransaction);
}
//}
}



}

return doneTransactions.ToArray ();
}

Example Payload:

{
"id": "74a7c410-5b00-4268-bb40-73cf099160ff",
"custodianUid": "00000000-0000-0000-0000-000000000000",
"datasourceUid": "e4b30a37-d0bf-49d4-bee9-a0cc19959e6d",
"token": "@ !!! @ ec131ebc7537c0f8414447261af2a8ef28414815274ebd4ea0ba33caac231bc8",
"statementDate": "2019-01-07T00: 00: 00Z",
"accountUid": "0b251123-b9e5-4f59-be4d-4dde0f0eb23e",
"firmId": 490
"Condition": 3,
"oversSizeLimit": false,
"external":[
{
"Identifier value": "85280",
"IdentifierName": "AssetID",
"Currency: USD",
"firmId": null,
"batchid": zero,
"securityGuid": null,
"previousPositionGuid": "57b63479-9cc1-49b3-9743-8932b1764d1e",
"previousPositionQty": 1000000
"previousPositionStartDate": "2019-01-01T00: 00: 00 + 00: 00",
"previousPositionEndDate": null,
"Transactions": [],
"Taxlots": [],
"Positions": [
        "1/7/2019 12:00:00 AM|TAA0315||@!!!@ec131ebc7537c0f8414447261af2a8ef28414815274ebd4ea0ba33caac231bc8|Security|Structured Product|85280|0|||||||||0.0000|0.0000||0|0|||0.00000|||1000000.00000|100.00|100.00|84.18000|84.18000|6/8/2021 12:00:00 AM|0.00||0.00|0.00|||841800.00|841800.00|USD|||||0.0000||||||||"
      ],
"Inch": []
    },
{
"Identifier value": "79428",
"IdentifierName": "AssetID",
"Currency: USD",
"firmId": null,
"batchid": zero,
"securityGuid": null,
"previousPositionGuid": "238c6c1e-0815-4b74-8ac8-d695ddb5ff2e",
"previous Position Qty": 1500000,
"previousPositionStartDate": "2019-01-01T00: 00: 00 + 00: 00",
"previousPositionEndDate": null,
"Transactions": [],
"Taxlots": [],
"Positions": [
        "1/7/2019 12:00:00 AM|TAA0315||@!!!@ec131ebc7537c0f8414447261af2a8ef28414815274ebd4ea0ba33caac231bc8|Security|Structured Product|79428|0|||||||||0.0000|0.0000||0|0|||0.00000|||1500000.00000|100.00|100.00|95.31000|95.31000|1/22/2021 12:00:00 AM|0.00||0.00|0.00|||1429650.00|1429650.00|USD|||||0.0000||||||||"
      ],
"Inch": []
    }
],
"Internal": [
    {
      "_name": null,
      "data": null,
      "identifiervalue": "85280",
      "identifierName": "AssetID",
      "currency": "USD",
      "previousPositionGuid": null,
      "previousPositionQty": null,
      "previousPositionStartDate": null,
      "previousPositionEndDate": null,
      "securityGuid": "c260432b-d169-474b-b142-83cd3e2b93e5",
      "firmId": 490,
      "positions": [
        {
          "Position_UID": "1e6b6a39-697a-468b-a266-bd5388475ec6",
          "Account_UID": "0b251123-b9e5-4f59-be4d-4dde0f0eb23e",
          "Security_UID": "c260432b-d169-474b-b142-83cd3e2b93e5",
          "SecurityFirmID": 490,
          "DateStart": "2019-01-07T00:00:00+00:00",
          "DateEnd": null,
          "ChangeDate": "2019-04-16T15:40:39.7454448Z",
          "ChangeType": "I",
          "BatchID": 2019416151243529,
          "Account_U": null,
          "Security": null,
          "POSITION_DETAIL": [
            {
              "Detail_UID": "39048278-f993-4229-b596-c681315c46b1",
              "Position_UID": "1e6b6a39-697a-468b-a266-bd5388475ec6",
              "Type": "BASE",
              "Quantity": 1000000,
              "Currency": "USD",
              "Value": 841800,
              "LastPrice": 84.18,
              "Cost": 100,
              "AccruedInterest": 0,
              "AccruedDividends": null,
              "Expenses": null,
              "UnrealizedGains": null,
              "RealizedGains": null,
              "Source_UID": "e4b30a37-d0bf-49d4-bee9-a0cc19959e6d",
              "Genesis": "Feed",
              "ChangeDate": "2019-04-16T15:40:39.8235573Z",
              "ChangeType": "I",
              "GenesisNavigation": null,
              "TypeNavigation": null,
              "POSITION_BOND": null,
              "POSITION_FX": null,
              "POSITION_OPTION": null,
              "POSITION_PRICE": null
            }
          ],
"POSITION_IDENTIFIER": [],
"POSITION_SUBSCRIPTION": []
        }
],
"transactions": zero,
"batchid": zero
},
{
"_name": null,
"data": zero,
"Identifier value": "79428",
"IdentifierName": "AssetID",
"Currency: USD",
"previousPositionGuid": null,
"previousPositionQty": zero,
"previousPositionStartDate": null,
"previousPositionEndDate": null,
"securityGuid": "266a3cc3-9f76-4ca1-a1bb-593c55d86e95",
"firmId": 490
"Positions": [
        {
          "Position_UID": "4d2e6c40-6878-4fab-96df-d62759e95917",
          "Account_UID": "0b251123-b9e5-4f59-be4d-4dde0f0eb23e",
          "Security_UID": "266a3cc3-9f76-4ca1-a1bb-593c55d86e95",
          "SecurityFirmID": 490,
          "DateStart": "2019-01-07T00:00:00+00:00",
          "DateEnd": null,
          "ChangeDate": "2019-04-16T15:40:39.870434Z",
          "ChangeType": "I",
          "BatchID": 2019416151243529,
          "Account_U": null,
          "Security": null,
          "POSITION_DETAIL": [
            {
              "Detail_UID": "c894765d-482a-4649-87be-afc6c8092275",
              "Position_UID": "4d2e6c40-6878-4fab-96df-d62759e95917",
              "Type": "BASE",
              "Quantity": 1500000,
              "Currency": "USD",
              "Value": 1429650,
              "LastPrice": 95.31,
              "Cost": 100,
              "AccruedInterest": 0,
              "AccruedDividends": null,
              "Expenses": null,
              "UnrealizedGains": null,
              "RealizedGains": null,
              "Source_UID": "e4b30a37-d0bf-49d4-bee9-a0cc19959e6d",
              "Genesis": "Feed",
              "ChangeDate": "2019-04-16T15:40:39.9798084Z",
              "ChangeType": "I",
              "GenesisNavigation": null,
              "TypeNavigation": null,
              "POSITION_BOND": null,
              "POSITION_FX": null,
              "POSITION_OPTION": null,
              "POSITION_PRICE": null
            }
          ],
"POSITION_IDENTIFIER": [],
"POSITION_SUBSCRIPTION": []
        }
],
"transactions": zero,
"batchid": zero
}
],
"batchid": 2019416151243529
}