linq filter columns rather than data-CodePudding

Firstly, I'm not sure if what I'm asking is possible or not so apologies if I'm asking a stupid question.

So I am able to filter a DataTable using linq to get the data I need, I'm wondering if it's possible to filter the columns using a simlar statement.

For example if I have the below datatable dtMyData

ID	Name	1	2	3	4
1	Conor	100	87	3	0
2	Frank	35	70	0	0
3	Jeff	35	13	0	57

I can filter it to the below using the following statement

dtMyData = dtMyData.AsEnumerable().Where(Function (f) f("Name").ToString().Equals("Frank")).CopyToDataTable

ID	Name	1	2	3	4
2	Frank	35	70	0	0

What I'm wanting to do (If it's possible) is filter the columns in a similar way so that I can select all of the columsn > 2 plus the first 2 columns. Giving me the following columns

ID	Name	3	4
1	Conor	3	0
2	Frank	0	0
3	Jeff	0	57

CodePudding user response：

Take a look at this method:

Private Function CopyTable(source As DataTable, columnsToKeep As IEnumerable(Of String)) As DataTable
    Dim copiedTable As DataTable = source.Clone()
    Dim columnsToRemove() As DataColumn = copiedTable.Columns.Cast(Of DataColumn).Where(Function(column) Not columnsToKeep.Contains(column.ColumnName)).ToArray()
    For i As Integer = 0 To columnsToRemove.Length - 1
        copiedTable.Columns.Remove(columnsToRemove(i))
    Next
    For Each row As DataRow In source.Rows
        Dim values As New List(Of Object)
        For Each column As DataColumn In copiedTable.Columns
            values.Add(row.Item(column.ColumnName))
        Next
        copiedTable.Rows.Add(values.ToArray())
    Next
    Return copiedTable
End Function

What this does is

Clone the DataTable
Loop over the copied DataTable and remove the columns that are not in the columnsToKeep
Loop over the original DataTable and add the rows to the copied DataTable without the cells that are not in the columnsToKeep

Fiddle: https://dotnetfiddle.net/2l6wk9

Edit

It would actually be easier to use DataTable.Copy over DataTable.Clone, my apologies:

Private Function CopyTable(source As DataTable, columnsToKeep As IEnumerable(Of String)) As DataTable
    Dim copiedTable As DataTable = source.Copy()
    Dim columnsToRemove() As DataColumn = copiedTable.Columns.Cast(Of DataColumn).Where(Function(column) Not columnsToKeep.Contains(column.ColumnName)).ToArray()
    For i As Integer = 0 To columnsToRemove.Length - 1
        copiedTable.Columns.Remove(columnsToRemove(i))
    Next
    Return copiedTable
End Function

What this updated code does is:

Copy the DataTable with its data
Loop over the copied DataTable and remove the columns that are not in the columnsToKeep

Fiddle: https://dotnetfiddle.net/NEIm2t