Notes

if (CorrespondenceIdsToProcess.Count > 0)
{
var correspondenceList = DbContext.CorresTaxes.Where(ct => ct.StatusCD == CorrespondanceConstants.Post && CorrespondenceIdsToProcess.Contains(ct.CorrespondenceID))
.Join(DbContext.TaxTmplInstProps, ct => ct.TaxTmplInstPropID, ttpd => ttpd.TaxTmplInstPropID,
(ct, ttpd) => new { CorrespondenceId = ct.CorrespondenceID, PdfName = ct.UploadFilename, PdfSize = ct.TotalPdfPage, Payload = ttpd.Payload }).ToList();

string currentDateFolder = Path.Combine(AppConfig["SharedPath"], "FileUpload", "IndexFiles", DateTime.Now.ToString("yyyy_MM_dd"));
Directory.CreateDirectory(currentDateFolder);

string filePath = Path.Combine(currentDateFolder, $"{DateTime.Now.ToString("yyyy_MM_dd")}.csv");

// Build file content in memory
var csvContent = new StringBuilder();
csvContent.AppendLine("CorrespondenceID,EnvelopeID,PDFName,PDFSize,Address,City,State,Zipcode,Country,PDFMergeLocation");
var recordsToUpdate = new List<CorresTax>();

foreach (var correspondence in correspondenceList)
{
var correspondencetoEdit = DbContext.CorresTaxes.FirstOrDefault(c => c.CorrespondenceID == Convert.ToInt64(correspondence.CorrespondenceId));

if (correspondence.PdfName != null && correspondence.PdfSize != null)
{
var jsonValue = Newtonsoft.Json.JsonConvert.DeserializeObject<JObject>(correspondence.Payload);
string CorrespondenceID = correspondence.CorrespondenceId.ToString();
string EnvelopeID = "";
string PDFName = correspondence.PdfName.ToString().Trim();
string PDFSize = correspondence.PdfSize.ToString().Trim();
string Address = jsonValue["HeaderfootercorresVM"]["Address"].ToString().Trim();
string City = jsonValue["HeaderfootercorresVM"]["City"].ToString().Trim();
string State = jsonValue["HeaderfootercorresVM"]["State"].ToString().Trim();
string Zipcode = jsonValue["HeaderfootercorresVM"]["Zipcode"].ToString().Trim();
string Country = jsonValue["HeaderfootercorresVM"]["Country"].ToString().Trim();

// Append data to CSV content
csvContent.AppendLine($"{CorrespondenceID},{EnvelopeID},{PDFName},{PDFSize},{Address},{City},{State},{Zipcode},{Country}");


// Prepare record for batch update
//var correspondencetoEdit = DbContext.CorresTaxes.FirstOrDefault(c => c.CorrespondenceID == Convert.ToInt64(CorrespondenceID));
if (correspondencetoEdit is not null)
{
correspondencetoEdit.IndexFilePath = filePath.Trim();
correspondencetoEdit.MdfctnDT = DateTime.Now;
correspondencetoEdit.StatusCD = CorrespondanceConstants.Indexed;
recordsToUpdate.Add(correspondencetoEdit);
}
}
else
{
// Update record for error
if (correspondencetoEdit is not null)
{
correspondencetoEdit.StatusCD = CorrespondanceConstants.Error;
correspondencetoEdit.MdfctnDT = DateTime.Now;
recordsToUpdate.Add(correspondencetoEdit);
}
}
}

// Write CSV content to the file
File.WriteAllText(filePath, csvContent.ToString(), Encoding.UTF8);

// Update database records in bulk
if (recordsToUpdate.Any())
{
DbContext.CorresTaxes.UpdateRange(recordsToUpdate);
DbContext.SaveChanges();

IndexFile indexFile = new();
indexFile.FileName = $"{DateTime.Now.ToString("yyyy_MM_dd")}.csv".ToString();
indexFile.FilePath = filePath.ToString().Trim();
indexFile.StatusCD = CorrespondanceConstants.Indexed.Trim();
DbContext.IndexFiles.Attach(indexFile);
DbContext.SaveChanges();
}

Logger.LogInformation("CorrespondenceIndexFileGenerationHelper:ProcessBundling completed.");

}