Except keyword issue in LINQ

Question

I want to capture the column names which needs to be added to the SQL table in order to insert the data.

Columnspresent-- List of all columns in the file ("Node", "Logtime", "Reason","ID","Comments")

existingtablecolumnsPresent -- List of all columns in the existing table in SQL("Node","Value","Reason","ID","Comments","logtime")

columnsNotPresent -- List of columns that needs to be added to the SQL table ( have to get "Value" in the output but not getting)..

 List<string> columnsPresent = 
          dt.Columns.Cast<DataColumn>()
         .Select(a => a.ColumnName.ToLower())
         .ToList();

 List<string> existingtablecolumnsPresent = 
          existingtable.Columns.Cast<DataColumn>()
         .Select(a => "[" + a.ColumnName.ToLower() + "]")
         .ToList();

 List<string> columnsNotPresent = 
         columnsPresent.OrderBy(t => t)
        .Except(existingtablecolumnsPresent.OrderBy(t => t))
        .ToList();

The above code is not giving the correct results if there is change in order of columns .Please advise.

Answer 1

you may try this (it needn't order by..)

List<string> existingtablecolumnsPresentNoSqrBr = new List<string>();
existingtablecolumnsPresent.ForEach(c => {
               c = c.Replace("[", string.Empty); 
               c = c.Replace("]",string.Empty);
               existingtablecolumnsPresentNoSqrBr.Add(c);
               });

List<string> columnsNotPresent = 
            columnsPresent.Except(existingtablecolumnsPresentNoSqrBr)
            .ToList();

really, if you avoid to .Select(a => "[" + a.ColumnName.ToLower() + "]") you can use the second query directly on existingtablecolumnsPresent..