Linq查询和普通SQL查询结果不同。

Question

我试图使用实体框架运行以下DB2 SQL

SELECT CM98.TUCUS01.CUST_NO,   
     CM98.TUMHC08.APPL_DT,   
     CM98.TUUNI30.MDL_ID,   
     CM98.TUMHC08.APPL_ID,   
     CM98.TUMHC08.DLR_NO,   
     CM98.TUMHC07.STAT_CD_DESC_TX,   
     CM98.TUCUS01.LAST_NAME,   
     CM98.TUCUS01.FST_NAME,   
     CM98.TUMHC08.UN_ID,   
     CM98.TUMHC08.PL_ID  
FROM CM98.TUSRV97,   
     CM98.TUMHC06,   
     CM98.TUMHC07,   
     CM98.TUCUS01,   
     CM98.TUUNI30,   
     CM98.TUMHC08  
WHERE ( CM98.TUMHC08.APPL_ID = CM98.TUMHC06.APPL_ID ) and  
     ( CM98.TUMHC07.APPL_STAT_CD = CM98.TUMHC06.APPL_STAT_CD ) and  
     ( CM98.TUUNI30.UN_ID = CM98.TUMHC08.UN_ID ) and  
     ( CM98.TUSRV97.XPROD_DIV_CD = CM98.TUUNI30.XPROD_DIV_CD ) and  
     ( CM98.TUSRV97.XPROD_CD = CM98.TUUNI30.XPROD_CD ) and  
     ( CM98.TUSRV97.MDL_ID = CM98.TUUNI30.MDL_ID ) and  
     ( CM98.TUMHC08.CUST_NO = CM98.TUCUS01.CUST_NO ) and  
( ( "CM98"."TUMHC06"."APPL_STAT_TS" = (SELECT MAX(APPL_STAT_TS) FROM CM98.TUMHC06 WHERE APPL_ID = CM98.TUMHC08.APPL_ID) ) )

下面是我创建的Linq查询，

from SRV97 in db.TUSRV97
from MHC06 in db.TUMHC06
from MHC07 in db.TUMHC07
from CUS01 in db.TUCUS01
from UNI30 in db.TUUNI30
from MHC08 in db.TUMHC08
where
 MHC08.APPL_ID == MHC06.APPL_ID &&
 MHC07.APPL_STAT_CD == MHC06.APPL_STAT_CD &&
 UNI30.UN_ID == MHC08.UN_ID &&
 SRV97.XPROD_DIV_CD == UNI30.XPROD_DIV_CD &&
 SRV97.XPROD_CD == UNI30.XPROD_CD &&
 SRV97.MDL_ID == UNI30.MDL_ID &&
 MHC08.CUST_NO == CUS01.CUST_NO &&
 MHC06.APPL_STAT_TS == 
  (from tempTUMHC060 in db.TUMHC06
  where
   tempTUMHC060.APPL_ID == MHC08.APPL_ID
  select new {
   tempTUMHC060.APPL_STAT_TS
  }).Max(p => p.APPL_STAT_TS)
select new {
 CUS01.CUST_NO,
 MHC08.APPL_DT,
 UNI30.MDL_ID,
 MHC08.APPL_ID,
 MHC08.DLR_NO,
 MHC07.STAT_CD_DESC_TX,
 CUS01.LAST_NAME,
 CUS01.FST_NAME,
 MHC08.UN_ID,
 MHC08.PL_ID
}

在执行普通SQL查询时，我得到一行作为结果，但在执行Linq查询时没有得到任何结果。

是我编写的相当于SQL?的LINQ。

我不知道wrong..Any的想法是什么？

更新:下面的是LINQ生成的内部查询

    {SELECT project1.appl_id 
AS 
  appl_id, project1.cust_no 
AS 
  cust_no, project1.appl_dt 
AS 
  appl_dt, project1.mdl_id1 
AS 
  mdl_id, project1.appl_id1 
AS 
  appl_id1, project1.dlr_no 
AS 
  dlr_no, project1.stat_cd_desc_tx 
AS 
  stat_cd_desc_tx, project1.last_name 
AS 
  last_name, project1.fst_name 
AS 
  fst_name, project1.un_id1 
AS 
  un_id, project1.pl_id 
AS 
  pl_idfrom 
  ( 
             SELECT     extent1.xprod_div_cd    AS xprod_div_cd, 
                        extent1.xprod_cd        AS xprod_cd, 
                        extent1.mdl_id          AS mdl_id, 
                        extent2.appl_id         AS appl_id, 
                        extent2.appl_stat_cd    AS appl_stat_cd, 
                        extent2.appl_stat_ts    AS appl_stat_ts, 
                        extent3.appl_stat_cd    AS appl_stat_cd1, 
                        extent3.stat_cd_desc_tx AS stat_cd_desc_tx, 
                        extent4.last_name       AS last_name, 
                        extent4.fst_name        AS fst_name, 
                        extent4.cust_no         AS cust_no, 
                        extent5.un_id           AS un_id, 
                        extent5.xprod_div_cd    AS xprod_div_cd1, 
                        extent5.xprod_cd        AS xprod_cd1, 
                        extent5.mdl_id          AS mdl_id1, 
                        extent6.appl_id         AS appl_id1, 
                        extent6.un_id           AS un_id1, 
                        extent6.cust_no         AS cust_no1, 
                        extent6.pl_id           AS pl_id, 
                        extent6.dlr_no          AS dlr_no, 
                        extent6.appl_dt         AS appl_dt, 
                        ( 
                               SELECT max(extent7.appl_stat_ts) AS a1 
                               FROM   ( 
                                             SELECT tumhc06.appl_id      AS appl_id, 
                                                    tumhc06.appl_stat_cd AS appl_stat_cd, 
                                                    tumhc06.appl_stat_ts AS appl_stat_ts, 
                                                    tumhc06.cret_by_id   AS cret_by_id 
                                             FROM   honda.tumhc06        AS tumhc06) AS extent7
                               WHERE  extent7.appl_id = extent6.appl_id) AS c1 
             FROM       honda.tusrv97                                    AS extent1 
             CROSS JOIN 
                        ( 
                               SELECT tumhc06.appl_id      AS appl_id, 
                                      tumhc06.appl_stat_cd AS appl_stat_cd, 
                                      tumhc06.appl_stat_ts AS appl_stat_ts, 
                                      tumhc06.cret_by_id   AS cret_by_id 
                               FROM   honda.tumhc06        AS tumhc06) AS extent2 
             CROSS JOIN honda.tumhc07 AS extent3 
             CROSS JOIN 
                        ( 
                               SELECT tucus01.last_name         AS last_name, 
                                      tucus01.fst_name          AS fst_name, 
                                      tucus01.mid_init          AS mid_init, 
                                      tucus01.cust_email_addr   AS cust_email_addr, 
                                      tucus01.cust_state        AS cust_state, 
                                      tucus01.cust_addr         AS cust_addr, 
                                      tucus01.cust_addr2        AS cust_addr2, 
                                      tucus01.cust_city         AS cust_city, 
                                      tucus01.zip_cd5           AS zip_cd5, 
                                      tucus01.zip_cd4           AS zip_cd4, 
                                      tucus01.fips_ctry_cd      AS fips_ctry_cd, 
                                      tucus01.last_upd_date     AS last_upd_date, 
                                      tucus01.cust_no           AS cust_no, 
                                      tucus01.cust_name_sfx_txt AS cust_name_sfx_txt, 
                                      tucus01.zip_cd3           AS zip_cd3, 
                                      tucus01.war_cust_sex_cd   AS war_cust_sex_cd, 
                                      tucus01.cust_typ_cd       AS cust_typ_cd, 
                                      tucus01.cust_ttl_cd       AS cust_ttl_cd, 
                                      tucus01.cust_ttl          AS cust_ttl, 
                                      tucus01.ssec_no           AS ssec_no, 
                                      tucus01.brth_date         AS brth_date, 
                                      tucus01.data_srce_cd      AS data_srce_cd 
                               FROM   cm98.tucus01              AS tucus01) AS extent4 
             CROSS JOIN honda.tuuni30 AS extent5 
             CROSS JOIN honda.tumhc08 AS extent6) 
AS 
  project1where (((((((project1.appl_id1 = project1.appl_id) AND 
  ( 
    project1.appl_stat_cd1 = project1.appl_stat_cd 
  ) 
  ) AND 
  ( 
    project1.un_id = project1.un_id1 
  ) 
  ) AND 
  ( 
    project1.xprod_div_cd = project1.xprod_div_cd1 
  ) 
  ) AND 
  ( 
    project1.xprod_cd = project1.xprod_cd1 
  ) 
  ) AND 
  ( 
    project1.mdl_id = project1.mdl_id1 
  ) 
  ) AND 
  ( 
    project1.cust_no1 = project1.cust_no 
  ) 
  ) AND 
  ( 
    project1.appl_stat_ts = project1.c1 
  ) 

}

谢谢

Answer 1

我找到了解决这个问题的方法。刚刚删除了edmx文件中的所有表，并按预期的方式重新添加。