How to implement NOT IN subquery in LinqToOracle?

How to implement NOT IN subquery in LinqToOracle?

Postby Mac » Wed 21 Jul 2010 09:46

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?
Mac
 
Posts: 12
Joined: Sun 23 Aug 2009 22:33

Postby AndreyR » Thu 22 Jul 2010 13:28

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;
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby Mac » Fri 06 Aug 2010 09:41

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();
Mac
 
Posts: 12
Joined: Sun 23 Aug 2009 22:33

Postby StanislavK » Tue 10 Aug 2010 15:50

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?
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48


Return to LinqConnect (LINQ to SQL support)