Remove Single Quotes From All Cells in a DataTable - Creating New Table - Using LINQ in Vb.Net

Question

I have a table with many values inside of it that might have single quotes around them. I need to create a duplicate table without those single quotes. I can't modify the original table as I need to use it, 'as-is' later.

I've done this with a table.Clone and using a foreach to loop through the rows and columns removing single quotes as I need and then putting that new row into the new table. It works and it's straight forward.....but

I'd like to do the same thing using LINQ. This was my attempt....

        Dim myResults = From myRow In dtTable _
                    From myItem In myRow.ItemArray _
                    Where TypeOf myItem Is String AndAlso _
                    myItem.ToString.StartsWith("'"c) AndAlso _
                    myItem.ToString.EndsWith("'"c)

As you can see - I didn't get very far. I had trouble finding examples that weren't looking at a specific column in the DataRow. It looks like my code does pull back all the matching results - but I'm at a lose for how I can create a duplicate table/modify the values?

EDIT - I've started a bounty for this in the hopes that someone can provide a solution. The only requirement is to not using a For Each; as I already know how to do that. I've gotten closer - but I still can't seem to create a new row or a new table.

My new approach hits a dead-end when I try to do this:

Dim MyNewRow As New Data.DataRow With {.ItemArray = myRemovedQuotes.ToArray}

The Error message I get says, "Error 1 'System.Data.DataRow.Protected Friend Sub New(builder As System.Data.DataRowBuilder)' is not accessible in this context because it is 'Protected Friend'."

Answer 1

If you really must use LINQ (as a learning exercise, perhaps), then maybe something like below (as C#; my VB is nowhere near up to translating it, I'm afraid):

        DataTable clone = original.Clone();
        string t;
        var qry = from DataRow row in original.Rows
                  let arr = row.ItemArray
                  select Array.ConvertAll(arr, s =>
                      (t = s as string) != null
                      && t.StartsWith("\'")
                      && t.EndsWith("\'") ? t.Trim('\'') : s);
        foreach (object[] arr in qry) {
            clone.Rows.Add(arr);
        }

The problem is that the ItemArray needs to be expanded while retaining the separate rows (so SelectMany isn't an option); Array.ConvertAll seems the easiest way.

Answer 2

Is your table generated locally (ie within the app) or coming from a database - if from the database I would be inclined to write a view that strips the single quotes before they reach your app.