Clever Geek Handbook

DataTable Query

I am new to LINQ. I am trying to find rows that do not exist in the second data table.

report_list and both test types are: DataTable. Both of these data types are populated using the OleDbCommand, OleDbDataAdapter. I get the error "The specified cast is not valid." in foreach ... loop. I would appreciate your help.

            var result = from a in report_list.AsEnumerable()
                         where !(from b in benchmark.AsEnumerable()
                                 select b.Field<int>("bench_id")
                                )
                                .Contains(a.Field<int>("BenchmarkID"))
                         select a;



            foreach (var c  in result)
            {
                Console.WriteLine(c.Field<string>("Name"));
            }
+1


source to share


4 answers


I don't know if I understood your question. Are you trying to get items that exist in the first table but not the second?


var first = new string[] { "b", "c" };
var second = new string[] { "a", "c" };
//find the itens that exist in "first" but not in "second"
var q = from f in first
        where !second.Contains(f)
        select f;
foreach (var s in q) {
    Console.WriteLine(s);
}

//Prints:
//b


I suggest doing an inner query first if it doesn't depend on the outer record.

+1


source 


From a in report_list
Group Join b in benchmark On a.bench_id Equals b.bench_id Into g = Group
Where g.Count = 0
Select a


Note that this is VB syntax.

0


source


My suspicion is that one of the fields you are comparing is not an integer in the database. I believe that an invalid throwable exception is thrown by one of the calls Field<int>()

, as it is one of three different exceptions that this method can use. See the Docs here .

0


source


Perhaps use the .Except () extension to get the difference between two sets?

(from b in benchmark.AsEnumerable()  
    select new { id = b.Field<int>("bench_id")}).Except(
         from a in report_list.AsEnumerable() 
             select new {id = a.Field<int>("BenchmarkID")})

Not really sure about the exact syntax, but this should work with the IDs in the template and then remove all the equivalent IDs in the report list, leaving only IDs that don't match. (I hope this is your order after ...)

Note. This also assumes that the above problem mentioned by tvanfosson is not a problem either.

0


source






More articles:


All Articles