sql query vs linq to entity return different results

Question

This is my sql query

SELECT TOP(10)projects.stat, 
              wo.stat, 
              sevt.restype, 
              sevt.resid, 
              restype.user2, 
              projects.prj_id, 
              projects.user3, 
              projects.user9, 
              wo.wonum, 
              wo.jobdesc, 
              sevt.sesid, 
              sevt.restype, 
              sevt.type, 
              sevt.t_start, 
              sevt.t_end, 
              sevt. mealstart, 
              sevt.mealend, 
              sevt. melstart2, 
              sevt.melend2, 
              sevt.melstart3, 
              sevt.melend3, 
              sevt.user2, 
              sevt.subactid, 
              sevt.ot_exempt, 
              sevt_ex.user5, 
              rescat.user1 
FROM   schedwin.projects 
       INNER JOIN schedwin.wo 
               ON projects.prj_id = wo.prj_id 
       INNER JOIN schedwin.sevt 
               ON wo.seqnum = sevt.seqnum 
       INNER JOIN schedwin.rsrce 
               ON sevt.resid = rsrce.resid 
       LEFT OUTER JOIN schedwin.pers 
                    ON rsrce.recid = pers.recid 
       INNER JOIN schedwin.restype 
               ON sevt.rtype = restype.code 
       INNER JOIN schedwin.rescat 
               ON sevt.rcat = rescat.code 
       LEFT OUTER JOIN schedwin.sevt_ex 
                    ON sevt.sesid = sevt_ex.sesid 
WHERE  ( Ltrim(Rtrim(projects.stat)) IN ( '1', '2' ) ) 
       AND ( Ltrim(Rtrim(wo.stat)) = '6' ) 
       AND ( ( ( sevt.restype = 5 
                  OR sevt.restype = 0 ) 
               AND ( Substring(restype.user2, 2, 1) = 'F' 
                     AND Substring(restype.user2, 6, 1) = 'S' ) ) 
              OR ( sevt.restype = 4 ) ) 
       AND ( sevt.type = 0 ) 
       AND rescat.groupid = 0 
       AND restype.groupid = 0 
       AND Len(Ltrim(wo.invoice)) > 0 
       AND Ltrim(wo.invoice) <> 'PENDING' 
       AND wo.userflag1 <> 1 
       AND Ltrim(sevt.t_start) = '1351728000' 
ORDER  BY projects.prj_id, 
          wo.wonum

I was converting in to Linq to Entity like below

            var query =
            (from PROJECTS in db.PROJECTS
            join WOes in db.WOes on PROJECTS.PRJ_ID equals WOes.PRJ_ID
            join SEVTs in db.SEVTs on WOes.SEQNUM equals SEVTs.SEQNUM
            join RSRCEs in db.RSRCEs on SEVTs.RESID equals RSRCEs.RESID
            join PERS in db.PERS on RSRCEs.RECID equals PERS.RECID into PERS_join
            from PERS in PERS_join.DefaultIfEmpty()
            join RESTYPEs in db.RESTYPEs on new { RTYPE = SEVTs.RTYPE } equals new { RTYPE = RESTYPEs.CODE }
            join RESCATs in db.RESCATs on new { RCAT = SEVTs.RCAT } equals new { RCAT = RESCATs.CODE }
            join SEVT_EX in db.SEVT_EX on SEVTs.SESID equals SEVT_EX.SESID into SEVT_EX_join
            //join SEVT_EX in db.SEVT_EX on new { SESID = (String)SEVTs.SESID } equals new { SESID = SEVT_EX.SESID } into SEVT_EX_join
            from SEVT_EX in SEVT_EX_join.DefaultIfEmpty()
            where
              (new string[] { "1", "2" }).Contains((PROJECTS.STAT.TrimEnd()).TrimStart()) &&
              ((WOes.STAT.TrimEnd()).TrimStart() == "6") &&
              (((SEVTs.RESTYPE == 5 || SEVTs.RESTYPE == 0) &&
              (RESTYPEs.USER2.Substring(2 - 1, 1) == "F" && RESTYPEs.USER2.Substring(6 - 1, 1) == "S")) || (SEVTs.RESTYPE == 0)) &&
              (SEVTs.TYPE == 0) &&
              (RESCATs.GROUPID == 0) &&
              (RESTYPEs.GROUPID == 0 )&&
              (int?)(WOes.INVOICE.TrimStart()).Length > 0 &&
              WOes.INVOICE.TrimStart() != "PENDING" &&
              WOes.USERFLAG1 != 1 &&
             (SEVTs.T_START.TrimStart()) == (Booktime)
             //(SEVTs.T_START.TrimStart()) == (Booktime)
             // String.Compare(SEVTs.T_START.ToString().TrimStart(), "1351728000") >= 0
            //Convert.ToInt32(SEVTs.T_START.TrimStart()) >= Convert.ToInt32(Booktime)
            orderby
             PROJECTS.PRJ_ID,
             WOes.WONUM
            select new
            {
                PROJECTS.PRJ_ID,
                PROJECTS.USER3,
                PROJECTS.USER9,
                WOes.WONUM,
                WOes.JOBDESC,
                SEVTs.SESID,
                SEVTs.RESTYPE,
                SEVTs.TYPE,
                SEVTs.T_START,
                SEVTs.T_END,
                SEVTs.MEALEND,
                SEVTs.MELSTART3,
                SEVTs.MELSTART2,
                SEVTs.MELEND2,
                Column1 = SEVTs.MELSTART2,
                SEVTs.MELEND3,
                SEVTs.USER2,
                SEVTs.SUBACTID,
                SEVTs.OT_EXEMPT,
                USER5 = SEVT_EX.USER5,
                SEVTs.GMT_OFFSET,
                SEVTs.MEALSTART,
                SEVTs.STANDARD,
                RESCATs.USER1,
                SEVTs.RESID
            }).Take(10);

Definitely my sql query is return a correct records back but my Linq is not, any have any clue what am missing here please ?

Thanks

Jose Alonso · Accepted Answer · 2013-02-19 23:33:45Z

1

You have this line:

(((SEVT.restype = 5 or SEVT.restype = 0) and (substring(restype.User2,2,1) = 'F' and substring(restype.User2,6,1) = 'S')) or (SEVT.RESTYPE = 4))and

and in linq you have this

((SEVTs.RESTYPE == 5 || SEVTs.RESTYPE == 0) && (RESTYPEs.USER2.Substring(2 - 1, 1) == "F" && RESTYPEs.USER2.Substring(6 - 1, 1) == "S") && SEVTs.TYPE == 0)

First, Substring in 2-1 = 1 not 2. and the Logical Operations not equal

answered Feb 19, 2013 at 23:33

Jose Alonso

313 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Usher Over a year ago

Thanks,just correct the query but its not returning any value now. Still i have something missing look like like

Usher Over a year ago

I just messed up with close brackets that why the query act weird,thanks again

Collectives™ on Stack Overflow

sql query vs linq to entity return different results

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related