CODEWITHSUNDEEP
c#vb.netlinqdatatable
Dinesh
Dinesh

Reputation: 219

Filter datatable if a column contains any one of the elements in an array

I have a DataTable like this

ColA  ColB
1     OneThree
2     FourTwo
3     EightNine
4     ThreeEightFive
5     SevenNine

and the substrings are in another array ("Two","Eight", "Three")

How do I filter the DataTable using ColB and get the rows which contains any one of the substrings in the array? Array might have n number of substrings.

Is there a possible way using Linq without looping thru each array element and checking the colB using CONTAINS keyword?

Upvotes: 0

Views: 2990

Answers (3)

G3nt_M3caj
G3nt_M3caj

Reputation: 2685

If I well understand you need something like this. Change MyDataTable with your filled DataTable and work on code following your necessities. 

Dim containsMyWords As Func(Of String, Boolean) = Function(entryS As String)
                                                      Dim myWords() As String = {"r1", "r2", "r3", "r4", "r5", "r6"}
                                                      If myWords.Contains(entryS) Then Return True
                                                      Return False
                                                  End Function

Dim listRows = From dtR As DataRow In MyDataTable.AsEnumerable
               Where containsMyWords(CType(dtR.Item("ColB"), String))

For Each mRow In listRows
    Console.WriteLine(Join(mRow.ItemArray, "~"))
Next

Upvotes: 0

divyang4481
divyang4481

Reputation: 1783

you can use LINQ as shown below to query datatable 

DataTable dt = new DataTable();
            dt.Columns.Add("ColA", typeof(int));
            dt.Columns.Add("ColB", typeof(string));
            dt.AcceptChanges();

            var r1 = dt.NewRow();
            r1["ColA"] = 1;
            r1["ColB"] = "OneThree";
            dt.Rows.Add(r1);

            var r2 = dt.NewRow();
            r2["ColA"] = 2;
            r2["ColB"] = "FourTwo";
            dt.Rows.Add(r2);

            var r3 = dt.NewRow();
            r3["ColA"] = 3;
            r3["ColB"] = "EightNine";
            dt.Rows.Add(r3);

            var r4 = dt.NewRow();
            r4["ColA"] = 4;
            r4["ColB"] = "ThreeEightFive";
            dt.Rows.Add(r4);

            var r5 = dt.NewRow();
            r5["ColA"] = 5;
            r5["ColB"] = "SevenNine";
            dt.Rows.Add(r5);

            dt.AcceptChanges();

            var subArray = new string[3] { "Two", "Eight", "Three" };

            var query = from r in dt.AsEnumerable()
                        where  subArray.Any(s=> r.Field<string>("ColB").IndexOf(s,StringComparison.InvariantCultureIgnoreCase)>-1)
                        select r.Field<string>("ColB");
            foreach (var item in query)
            {
                Console.WriteLine(item);
            }

Upvotes: 1

haldo
haldo

Reputation: 16701

This will return all ColB values that contain values from your list.

var dt = // your datatable
var list = new List<string>{ "Two", "Eight", "Three" }; 
var matches = dt.AsEnumerable()
                .Where(dr => list.Any(l => dr.Field<string>("ColB").Contains(l)))
                .Select(r => r.Field<string>("ColB"))
                .ToList();

Please note that Linq still performs loops behind the scenes. This may not be very efficient (due to the Where/Any), especially on large data sets.

If you want to return a DataTable use CopyToDataTable():

var matches = dt.AsEnumerable()
                .Where(dr => list.Any(l => dr.Field<string>("ColB").Contains(l)))
                .CopyToDataTable();

Upvotes: 1

Related Questions