How can i change this LINQ query to use my List instead of the hard coded number?

Question

I finally got my LINQ to query the Datatable and with hard coded values, i get a new datatable with those records. But i need help to update this code to use my List returnClass() as my variables in the where clause.

var query = from r in d.AsEnumerable()
            where r.Field<string>("Column7") == "672"
            select r;

DataTable output = query.CopyToDataTable<DataRow>();
foreach (DataRow row in output.Rows)
{
    output.ImportRow(row);
}

Id like to replace the == "672" with the list i have that contains 94 values, that i want to exclude from the datatable. This is my original post that got me to where i am now, but now i just need help with the LINQ query to use my List and write in a fashion that excludes any records that have the values in my list. Im trying to filter my 34k records down to 29k using that list.

Another example of something i tried that doesnt work

            var query = from r in d.AsEnumerable()
                    where !r.Field<string>("Column7").Contains(Convert.ToString(returnClass()))
                    select r;

        DataTable output = query.CopyToDataTable<DataRow>();
        foreach (DataRow row in output.Rows)
        {
            output.ImportRow(row);
        }

List looks like this(truncated for clarity)

    private List<int> returnClass()
    {
        List<int> cl = new List<int>();
        cl.Add(75);
        cl.Add(76);
        cl.Add(77);
        cl.Add(78);
        cl.Add(79);
        cl.Add(80);
        cl.Add(81);
        cl.Add(82);
        return cl;
    }

Can you give an example of what your list could contain, and what the resulting query would look like? — Sweeper, Jun 24 '19 at 19:32

score 2 · Accepted Answer · answered Jun 24 '19 at 19:36

2

Assuming that myList is a List<string>, you can try something like this:

var query = from r in d.AsEnumerable()
            where myList.Any(r.Field<string>("Column7").Contains)
            select r;

Or, if myList is a List<int>, you might want to modify your Database column to also have type int, and you can do the following:

var query = from r in d.AsEnumerable()
            where myList.Contains(r.Field<int>("Column7"))
            select r;

answered Jun 24 '19 at 19:36

Sean Sailer

343
1
12

it complains about casting, my datatable seems to contain the values are strings and my list is of type Int, so do i need to convert this line ? r.Field("Column7") – CubanGT Jun 24 '19 at 19:43
i get this error Message "Specified cast is not valid." string on this line where !returnClass().Contains(r.Field("Column7")) – CubanGT Jun 24 '19 at 19:45
1

I went ahead and just changed my List to string values and updated my query to this and it seems to work.. var query = from r in d.AsEnumerable() where !returnClass().Any(r.Field("Column7").Contains) select r; DataTable output = query.CopyToDataTable(); foreach (DataRow row in output.Rows) { output.ImportRow(row); } – CubanGT Jun 24 '19 at 19:54
The above works until it gets to the line after my DataTable output where i start a foreach loop over the new datatable i get this error Collection was modified; enumeration operation might not execute. – CubanGT Jun 25 '19 at 16:23
It throw the error on this line: foreach (DataRow row in output.Rows) – CubanGT Jun 25 '19 at 16:25
@CubanGT The error "collection was modified" is because of this line `DataTable output = query.CopyToDataTable();` See this post: [https://stackoverflow.com/questions/15457571/error-in-datarow-collection-was-modified-enumeration-operation-might-not-execut] – Sean Sailer Jun 25 '19 at 16:32
I changed my code to match the example, but my Row.Count seems to be growing, how or why is that possible? DataTable output = query.CopyToDataTable(); for (int i = 0; i < output.Rows.Count; i++) { DataRow rows = output.Rows[i]; output.ImportRow(rows); } – CubanGT Jun 25 '19 at 18:17

How can i change this LINQ query to use my List instead of the hard coded number?

1 Answers1