Linq .Any() wrong SQL statement for IBM DB2

[ziflex]

Hello there! I try this:

// 'db' is created instance of DbManager
bool exist = db.GetTable<Document>().Any(i => i.Id == id)

and i'v got that query:

SELECT
    CASE WHEN EXISTS(
        SELECT
            *
        FROM
            documents as i
        WHERE
            i.Id = 306722
    ) THEN 1 ELSE 0 END as c1
FROM c1 FETCH FIRST 1 ROW ONLY

wich is wrong for IBM DB2. It doesn't work.

[igor-tkachev]

For the following test:

[Test]
public void Any61([DataContexts] string context)
{
    using (var db = GetDataContext(context))
        Assert.AreEqual(
                        Child.   Any(c => c.ParentID > 3),
            db.GetTable<Child>().Any(c => c.ParentID > 3));
}

bltoolkit generates:

Provider : DB2
DbManager: -- DB2
SELECT
    CASE WHEN EXISTS(
        SELECT
            *
        FROM
            "Child" "c"
        WHERE
            "c"."ParentID" > 3
    ) THEN 1 ELSE 0 END as "c1"
FROM SYSIBM.SYSDUMMY1 FETCH FIRST 1 ROW ONLY

[ziflex]

Sorry, my code has a mistake, of course there is must be "FROM SYSIBM.SYSDUMMY1 FETCH FIRST 1 ROW ONLY" (it has been edited). But, the problem is "CASE WHEN EXISTS" statement. On my IBM iSeries DB2 it doesn't work. I'v got an error 'ERROR [42000] [IBM][iSeries Access ODBC Driver][DB2 UDBJSQL0104 - Token EXISTS was not valid. Valid tokens: <IDENTIFIER> <INTEGER> <CHARSTRING> <GRAPHSTRING>'. After searching in google, I found out that EXISTS is not supported on CASE. So, there is must be another query.

[igor-tkachev]

It works for DB2 version that I have. Anyway, what do you think should be generated?

[ziflex]

Oh, you don't have a such problem... I suppose, it's because of old version of DB2 we use - v5r4. I'm not a big expert in SQL, so I can't say what the best solution is.

Maybe something like this:

SELECT
     CASE WHEN (
          SELECT COUNT(*) FROM c WHERE c.ParentId > 3
     ) > 0 THEN 1 ELSE 0 END as c1
FROM SYSIBM.SYSDUMMY1 FETCH FIRST 1 ROW ONLY