How can I rearrange list indexing-CodePudding

I am working on google spreadsheets, user connect its spreadsheet, and the system pushed data to user connected spreadsheet at day end. here are spreadsheet columns The sequence of columns is "PatientName","DOB","StripeId","Card","ChargeCreated","Status","Amount"

here is the code is used

 var GoogleSpreadSheetData = db.GoogleSpreadSheetDatas
    .ToList()
    .Select(x => new List<string>{
        x.PatientName.Trim(),
        x.DOB.Trim(),
        x.StripeId.Trim(),
        x.Card.Trim(),
        x.ChargeCreated.Trim(),
        x.Status.Trim(),
        x.Amount.Trim(),
     })
    .ToList();

the sequence of google spread sheet columns and query return is same sequence like "PatientName","DOB","StripeId","Card","ChargeCreated","Status","Amount" and data will mapped on exact column in spread sheet Reference image.

if user changes sequence of columns like this image, replaced PatientName column to DOB. when next time data push to spreadsheet PatientName data is pushed in the DOB column and vice versa.

Before pushing data to the spreadsheet, firstly I call google API(using Developer Metadata) to get a sequence of that spreadsheet, I got the updated column sequence in response.

My question is

How can I change the sequence on query select? Can I rearrange the list on query select or after?

Please help to solve this problem. Thank you.

CodePudding user response：

Assuming your MetaData query provides you with a List<string> of column names:

List<string> spreadsheetColSeq;

Then you can query the database to retrieve the rows:

var dbData = db.GoogleSpreadSheetDatas
   .Select(x => new List<string> {
        x.PatientName.Trim(),
        x.DOB.Trim(),
        x.StripeId.Trim(),
        x.Card.Trim(),
        x.ChargeCreated.Trim(),
        x.Status.Trim(),
        x.Amount.Trim(),
    })
   .ToList();

And then you can create a mapping from the query order to the spreadsheet order and reorder the each row's List<string> to match the new order:

var origSeq = new[] { "PatientName", "DOB", "StripeId", "Card", "ChargeCreated", "Status", "Amount" };

var colOrder = spreadsheetColSeq
    .Select(colName => Array.IndexOf(origSeq, colName))
    .ToList();

var GoogleSpreadSheetData = dbData
    .Select(row => colOrder.Select(idx => row[idx]).ToList())
    .ToList();

Note: If columns may be deleted, this will crash since Array.IndexOf will return -1 for a missing value. You could filter colOrder by adding .Where(idx => idx > -1) before the ToList() to skip missing columns.