DataTable Query

Question

Hi, I am new to LINQ. I am trying to find the rows that does not exists in the second data table.

report_list and benchmark both type are : DataTable. Both these datatables are being populated using OleDbCommand,OleDbDataAdapter. I am getting an error "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"));
            }

Answer 1

I don't know if I understood your question. Are you trying to get the items that exists in the first table but not in 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 you to make the inner query first, once it does not depend on the outer record.

Answer 2

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.

Answer 3

My suspicion is that one of the fields you are comparing is not an integer in the database. I believe that the invalid cast exception is being thrown by one of the Field<int>() calls since that is one of the three different exceptions that this method can throw. See docs here.

Answer 4

Perhaps use the .Except() extension to get the set difference of the 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 actually sure of the precise syntax, but that should work by taking the ids in benchmark, and then removing all equivalent ids in report_list, leaving only the ids that don't match. (I hope this is the order you were after...)

Note: This is also assuming that the above issue mentioned by tvanfosson isn't also a problem