datatable linq remove rows where not in array

Question

_{Thursday, June 27, 2013 11:02 PM}

i have datatable with data from sql, with columns : col1,col2,col3.

and i got string[].

i want to remove all rows from the datatable where col1 is not contained in string[].

how do i do this?

Thanks all.

All replies (6)

_{Friday, June 28, 2013 1:58 AM ✅Answered | 1 vote}

Hi want to learn1;

The following code snippet should do what you need. Please replace dataTableInstance with the actual instance name of the datatable, DataTypeOfCol1 with the actual data type that the column is holding, and col1 with the actual column name in the datatable.

string[] words = new string[] { "word1", "word2" };

// Get the list of rows to be removed
var results = (from row in dataTableInstance.AsEnumerable()
               where words.Contains(row.Field<DataTypeOfCol1>("col1"))
               select row).ToList();

// Remove the rows from the dataTable              
results.ForEach( r => dt.Rows.Remove(r));

Fernando (MCSD)

If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

NOTE: If I ask for code, please provide something that I can drop directly into a project and run (including XAML), or an actual application project. I'm trying to help a lot of people, so I don't have time to figure out weird snippets with undefined objects and unknown namespaces.

_{Friday, June 28, 2013 3:16 PM ✅Answered | 1 vote}

Hope this helps..modify the below based on the datatypes of datatable column

      DataTable table = new DataTable();
           table.Columns.Add("col1", typeof(string)); table.Columns.Add("col2", typeof(string)); table.Columns.Add("col3", typeof(string));
           table.Rows.Add("good", "abc", "xyz");
           table.Rows.Add("bad", "abc", "xyz");
           table.Rows.Add("ugly", "abc", "xyz");

           string[] wordlist = { "good" };
           DataRow [] rows = table.AsEnumerable().Where(x => !wordlist.Contains(x.Field<string>("col1"))).ToArray();
           foreach (DataRow row in rows) table.Rows.Remove(row);
           table.AcceptChanges();

           foreach (DataRow row in table.Rows)
           {
               Console.WriteLine("{0} {1} {2} ", row[0], row[1], row[2]);
           }

_{Friday, June 28, 2013 1:28 AM}

Hi,

If you connect to database using linq to sql, please refer to the following code:

static void Main(string[] args)        {            string[] arr = { "hello", "jimmy" };            TestDBDataContext db = new TestDBDataContext();            var col1s = db.t_tests.Where(t => !arr.Contains(t.col1));            db.t_tests.DeleteAllOnSubmit(col1s);            db.SubmitChanges();            Console.WriteLine("OK");            Console.ReadKey();        }

If you connect to database using ado.net, just concatenate the sql command with "delete from t where col1 not in ('string1','string2'...)". This is the simplest way to solve your problem.

If you still have any question, please feel free to give me a reply.

Have a good day!

Caillen

_{Friday, June 28, 2013 8:59 PM}

Thanks all :)

_{Saturday, June 29, 2013 12:29 PM}

I think it would be much better to not retrieve the rows in the first place, isntead of filtering them after you already dragged them across the Network. Never retrieve something from a DB you do not actually need.

If you could give us some examples for the values of col1 and the stuff in teh string[], we could build a SQL Querry for that. But even without that it sounds like a prime example for use of SQL IN:

http://msdn.microsoft.com/en-us/library/ms177682.aspx

Let's talk about MVVM: http://social.msdn.microsoft.com/Forums/en-US/wpf/thread/b1a8bf14-4acd-4d77-9df8-bdb95b02dbe2

_{Saturday, June 29, 2013 12:33 PM}

Hi want to learn1;

If one or more of the above solutions have answered your question please mark them as answer thank you.

Fernando (MCSD)

If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

NOTE: If I ask for code, please provide something that I can drop directly into a project and run (including XAML), or an actual application project. I'm trying to help a lot of people, so I don't have time to figure out weird snippets with undefined objects and unknown namespaces.