1、这段linq,执行期间报ora-12704:character set mismatch错误。
1 var query = from m in ctx.MENU 2 where (m.SUPER_MENU_ID ?? "") == (parentMenuId ?? "") 3 orderby m.SORT_ID descending 4 select new { m.SORT_ID };
生成出来的sql如下:
1 SELECT "Project1"."SORT_ID" AS "SORT_ID" 2 FROM ( 3 4 SELECT "Extent1"."SORT_ID" AS "SORT_ID" 5 FROM "BA"."MENU" "Extent1" 6 WHERE ((CASE WHEN("Extent1"."SUPER_MENU_ID" IS NULL) THEN ‘‘ 7 ELSE "Extent1"."SUPER_MENU_ID" END) = 8 (CASE WHEN(&p__linq__0 IS NULL) THEN ‘‘ 9 ELSE &p__linq__0 END)) 10 11 ) "Project1" 12 ORDER BY "Project1"."SORT_ID" DESC
但是这条sql单独放到plsql里跑是OK的。
2、改成这样,让生成的sql去掉了里面的case when就OK了。
1 parentMenuId = parentMenuId ?? ""; 2 var query = from m in ctx.MENU 3 where m.SUPER_MENU_ID == parentMenuId 4 orderby m.SORT_ID descending 5 select new { m.SORT_ID };
生成的sql如下:
1 SELECT "Project1"."SORT_ID" AS "SORT_ID" FROM ( 2 3 SELECT "Extent1"."SORT_ID" AS "SORT_ID" 4 FROM "BA"."MENU" "Extent1" 5 WHERE ("Extent1"."SUPER_MENU_ID" = :p__linq__0) 6 7 ) "Project1" 8 ORDER BY "Project1"."SORT_ID" DESC
3、目前的猜测是,ef生成出来的case when有问题,调整linq不生成case when即可。但奇怪的是,同样的sql在plsql里跑居然也是ok的,手工修改客户端的字符集也无法在plsql里重现这个问题,如下:
修改注册表里,HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraClient11g_home2\NLS_LANG,在plsql里客户端使用不同于服务端的字符集,但无法生成同样的错误。
修改前:
NLS_LANG = SIMPLIFIED CHINESE_CHINA.ZHS16GBK
Character Sets
Character
size: 2 byte(s)
CharSetID: 852
NCharSetID: 2000
Unicode Support:
True
NLS_LANG: SIMPLIFIED CHINESE_CHINA.ZHS16GBK
NLS_CHARACTERSET:
ZHS16GBK
NLS_NCHAR_CHARACTERSET: AL16UTF16
修改后:
NLS_LANG = SIMPLIFIED CHINESE_CHINA.AL32UTF8
Character Sets
Character
size: 2 byte(s)
CharSetID: 873
NCharSetID: 2000
Unicode Support:
True
NLS_LANG: SIMPLIFIED CHINESE_CHINA.AL32UTF8
NLS_CHARACTERSET:
ZHS16GBK
NLS_NCHAR_CHARACTERSET: AL16UTF16
4、这个问题只是暂时解决,仍然存疑中,待完善。主要参考这篇:
《ef4 part 8: database agnostic linq to entities》
EntityFramework查询oracle数据库时报ora-12704: character set mismatch,布布扣,bubuko.com
EntityFramework查询oracle数据库时报ora-12704: character set mismatch