How to implement NOT IN subquery in LinqToOracle?

[Mac]

I'm having problems with converting query that has "not in" subquery to Linq object. The query is

Code: Select all

SELECT * FROM MyTable
WHERE MyID NOT IN (SELECT ID FROM MyOtherTable)

In LinqToSQL it should look like:

Code: Select all

var q = from t1 in MyTable
        let t2s = from t2 in MyOtherTable
                  select t2.ID
        where !t2s.Contains(t1.MyID) 
        select t1;

However it returns all records from MyTable.
I've tried to split it in 2 different queries:

Code: Select all

var mySet = from t2 in MyOtherTable select t2.ID;
var q = from t1 in MyTable
        where !mySet.Contains(t1.MyID) 
        select t1;

the first query returns correct result but the second is still ignoring the "where !contains" condition.

The same thing happen with both .Contains() and .Any()

Is there any way to get "not in" subquery in LinqToOracle?

[AndreyR]

Try to materialize the first query like in the following example:

Code: Select all

        List q1 = (from e in db.Emps
                 select e.Deptno).ToList();
        var q2 = from d in db.Depts
                 where !q1.Contains(d.Deptno)
                 select d;

[Mac]

Andrey

Thanks for reply. I've manage to work around this problem for some time but now I'm experiencing it again. The solution with materialising list does not work.

Here is the code I'm executing:

Code: Select all

            var productCodes = new List(products.Select(x => x.ProductCode)).ToList();

            var descriptions = (from cmlong in oneOfficeData.Cmlongs
                               where productCodes.Contains(cmlong.CmdesProduct)
                               select new { cmlong.CmdesProduct, cmlong.CmdesSeq, cmlong.CmdesDesc }).ToList();

[StanislavK]

Could you please specify the scripts needed to create and populate the database objects used? We couldn't reproduce the problem with simple tables like

Code: Select all

CREATE TABLE DEPT (
  DEPTNO NUMBER(4),
  DNAME VARCHAR2(14),
  LOC VARCHAR2(13),
  CONSTRAINT PK_DEPT PRIMARY KEY (DEPTNO)
)

Also, what versions of dotConnect for Oracle and Oracle server are you using?