C# LINQ DataTable "WHERE NOT IN"

Question

I have two DataTables. I can join these two tables using LINQ but I am trying to write the following SQL query to LINQ but failed.

What is did for joining two DataTables:

dtMerged = (from a in dtOraDataTable.AsEnumerable()
                join b in dtSqlSmsTable.AsEnumerable()
                    on a["EntrySerialNo"].ToString() equals b["EntrySerialNo"].ToString()
                    into g
                where g.Any()
                select a).CopyToDataTable();

Now I want to do:

SELECT * FROM OraSmsData a WHERE EntrySerialNo NOT IN (SELECT EntrySerialNo FROM SqlSmsData WHERE IsProcessed=1);

How can I implement this in LINQ? I will appreciate if anyone guide me here.

Athanasios Kataras · Answer 1 · 2020-09-08T15:01:54.480

3

Does this work for you?

var query =    
    from a in dtOraDataTable.AsEnumerable() 
    where !(from b in dtSqlSmsTable.AsEnumerable()   
            select b["EntrySerialNo"].ToString())    
           .Contains(a["EntrySerialNo"].ToString()    
    select a;

edited Sep 08 '20 at 15:01

answered Sep 08 '20 at 14:41

Athanasios Kataras

25,191
4
32
61

Operator "!" cannot be applied to operand of type 'EnumerableRowCollection' showing this error. – Sajid Wasim Sep 08 '20 at 14:52
1

I think only one bracket is wrong. The `Contains` check should be outside of `where !(...)` ... like shown [here](https://stackoverflow.com/questions/3944803/use-linq-to-get-items-in-one-list-that-are-not-in-another-list) – nilsK Sep 08 '20 at 15:00
Excellent spot, I fixed it now. – Athanasios Kataras Sep 08 '20 at 15:02
Code with explanation is typically better received that just a dump of code with "try this" or similar... – Heretic Monkey Sep 08 '20 at 15:57
"EntrySerialNo" is sql server numeric datatype. I am getting conversion error. – Sajid Wasim Sep 08 '20 at 16:10

Sowmyadhar Gourishetty · Accepted Answer · 2020-09-08T16:16:02.547

0

You can do something as below:

var query = 
  dtOraDataTable.AsEnumerable()
    .Where(x => !dtSqlSmsTable.AsEnumerable()
    .Select(y => y.Field<decimal>("EntrySerialNo")).Contains(x.Field<decimal>("EntrySerialNo")));

edited Sep 08 '20 at 16:16

answered Sep 08 '20 at 15:47

Sowmyadhar Gourishetty

1,843
1
8
15

"EntrySerialNo" is sql server numeric datatype. I am getting conversion error. – Sajid Wasim Sep 08 '20 at 16:11
Does `int` solved your problem? You can either use `decimal` or `double` if you still get that error. – Sowmyadhar Gourishetty Sep 08 '20 at 16:18
This works for me. Thanks @Sowmyadhar. var dtMerged = dtOraDataTable.AsEnumerable() .Where(x => !dtSqlSmsTable.AsEnumerable() .Select(y => y.Field("EntrySerialNo")).Contains(x.Field("EntrySerialNo"))).CopyToDataTable(); – Sajid Wasim Sep 08 '20 at 16:19

C# LINQ DataTable "WHERE NOT IN"

2 Answers2