I'm having problems with the query below which is generated by Entity Framewrok 6.0. When I remove where clause query is being executed well. But I cant see the problem on where clause. Can you please help me?
{SELECT
"Extent1"."ID" AS "ID",
"Extent1"."STATECODE" AS "STATECODE",
"Extent1"."FIRMAID" AS "FIRMAID",
"Extent1"."ILID" AS "ILID",
"Extent1"."ILCEID" AS "ILCEID",
"Extent1"."PLANTURID" AS "PLANTURID",
"Extent1"."ETUDTURID" AS "ETUDTURID",
"Extent1"."ETUDAMACID" AS "ETUDAMACID",
"Extent1"."DILIMID" AS "DILIMID",
"Extent1"."ACIKLAMA" AS "ACIKLAMA",
"Extent1"."BASVURUTARIHI" AS "BASVURUTARIHI",
"Extent1"."ALAN" AS "ALAN",
"Extent1"."OLCEK" AS "OLCEK",
"Extent1"."CREATETIME" AS "CREATETIME",
"Extent1"."UPDATETIME" AS "UPDATETIME",
"Extent1"."CREATEUSERID" AS "CREATEUSERID",
"Extent1"."UPDATEUSERID" AS "UPDATEUSERID",
"Extent2"."ID" AS "ID1",
"Extent2"."KOD" AS "KOD",
"Extent2"."AD" AS "AD",
"Extent2"."CREATETIME" AS "CREATETIME1",
"Extent2"."UPDATETIME" AS "UPDATETIME1",
"Extent2"."CREATEUSERID" AS "CREATEUSERID1",
"Extent2"."UPDATEUSERID" AS "UPDATEUSERID1",
"Extent3"."ID" AS "ID2",
"Extent3"."KOD" AS "KOD1",
"Extent3"."AD" AS "AD1",
"Extent3"."PLANTURID" AS "PLANTURID1",
"Extent3"."CREATETIME" AS "CREATETIME2",
"Extent3"."UPDATETIME" AS "UPDATETIME2",
"Extent3"."CREATEUSERID" AS "CREATEUSERID2",
"Extent3"."UPDATEUSERID" AS "UPDATEUSERID2",
"Extent4"."ID" AS "ID3",
"Extent4"."VERGINUMARASI" AS "VERGINUMARASI",
"Extent4"."EMAIL" AS "EMAIL",
"Extent4"."FIRMAADI" AS "FIRMAADI",
"Extent4"."BUROTESCILNUMARASI" AS "BUROTESCILNUMARASI",
"Extent4"."TELEFON" AS "TELEFON",
"Extent4"."ADRES" AS "ADRES",
"Extent4"."ILID" AS "ILID1",
"Extent4"."ILCEID" AS "ILCEID1",
"Extent4"."MAHALLEID" AS "MAHALLEID",
"Extent4"."VERGIDAIRESIID" AS "VERGIDAIRESIID",
"Extent4"."USERID" AS "USERID",
"Extent4"."NODEID" AS "NODEID",
"Extent4"."CREATETIME" AS "CREATETIME3",
"Extent4"."UPDATETIME" AS "UPDATETIME3",
"Extent4"."CREATEUSERID" AS "CREATEUSERID3",
"Extent4"."UPDATEUSERID" AS "UPDATEUSERID3",
"Extent5"."FEATUREID" AS "FEATUREID",
"Extent5"."ADI_NUMARASI" AS "ADI_NUMARASI",
"Extent5"."UAVTKOD" AS "UAVTKOD",
"Extent6"."FEATUREID" AS "FEATUREID1",
"Extent6"."ADI_NUMARASI" AS "ADI_NUMARASI1",
"Extent6"."UAVTKOD" AS "UAVTKOD1",
"Extent6"."ILKOD" AS "ILKOD",
"Extent7"."ID" AS "ID4",
"Extent7"."ENLEM" AS "ENLEM",
"Extent7"."BOYLAM" AS "BOYLAM",
"Extent7"."DILIMI" AS "DILIMI",
"Extent7"."MERIDYEN" AS "MERIDYEN",
"Extent7"."ILADI" AS "ILADI",
"Extent7"."ILID" AS "ILID2",
"Extent8"."ID" AS "ID5",
"Extent8"."KOD" AS "KOD2",
"Extent8"."AD" AS "AD2",
"Extent8"."CREATETIME" AS "CREATETIME4",
"Extent8"."UPDATETIME" AS "UPDATETIME4",
"Extent8"."CREATEUSERID" AS "CREATEUSERID4",
"Extent8"."UPDATEUSERID" AS "UPDATEUSERID4"
FROM "JEOLOJI"."PROJE" "Extent1"
INNER JOIN "JEOLOJI"."ETUDAMAC" "Extent2" ON "Extent1"."ETUDAMACID" = "Extent2"."ID"
INNER JOIN "JEOLOJI"."ETUDTUR" "Extent3" ON "Extent1"."ETUDTURID" = "Extent3"."ID"
INNER JOIN "JEOLOJI"."FIRMA" "Extent4" ON "Extent1"."FIRMAID" = "Extent4"."ID"
INNER JOIN "JEOLOJI"."IL" "Extent5" ON "Extent1"."ILID" = "Extent5"."FEATUREID"
LEFT OUTER JOIN "JEOLOJI"."ILCE" "Extent6" ON "Extent1"."ILCEID" = "Extent6"."FEATUREID"
INNER JOIN "JEOLOJI"."DILIM" "Extent7" ON "Extent1"."DILIMID" = "Extent7"."ID"
INNER JOIN "JEOLOJI"."PLANTUR" "Extent8" ON "Extent1"."PLANTURID" = "Extent8"."ID"
WHERE ((1 = (CASE WHEN (( NVL(INSTR(LOWER(CASE WHEN ("Extent1"."ACIKLAMA" IS NULL) THEN '' ELSE "Extent1"."ACIKLAMA" END), LOWER('Ma')), 0) ) = 1) THEN 1 WHEN (( NVL(INSTR(LOWER(CASE WHEN ("Extent1"."ACIKLAMA" IS NULL) THEN '' ELSE "Extent1"."ACIKLAMA" END), LOWER('Ma')), 0) ) <> 1) THEN 0 END)) AND (22 <> "Extent1"."FIRMAID"))}
I solved the problem.
We are marking our string types in c# with StringLength attribute to prevent Entity Framework to create them as NCLOB columns on Oracle. Then Entity Framework generates columns with type nvarchar for c# propeties marked with StringLenth attribute.
In this query the where clause works fine with varchar2 because of the qoutes(as I learned from my Db profession friend.) So I changed the types of the columns and its solved.