Sql to Linq Conversion

Question

Hi 

 

I converted the query from sql to Linq however in linq it is not giving me exact result as sql is giving. Please help

 

Sql Query

 

SELECT o.DocumentID, o.DocumentReceivedTime, o.DocumentReferenceNo, aro.RelatedDocRefNo, abs(datediff(minute, o.DocumentReceivedTime, GETUTCDATE())) AS TimeSince

FROM [BISDW1D].[dbo].Documents o

LEFT JOIN [BISDW1D].[dbo].Documents aro

ON o.DocumentReferenceNo = SUBSTRING(REPLACE(aro.DocumentReferenceNo, 'APVORD-', ''), 0, (

CASE

WHEN CHARINDEX('-', REPLACE(aro.DocumentReferenceNo, 'APVORD-', '')) = 0

THEN len(aro.DocumentReferenceNo) + 1

ELSE CHARINDEX('-', REPLACE(aro.DocumentReferenceNo, 'APVORD-', ''))

END

))

AND o.Sender = aro.Sender

AND aro.MessageTypeID = 5

WHERE o.MessageTypeID = 2

AND o.Environment = 1

AND o.DocumentReceivedTime < DATEADD(MINUTE, -3, GETUTCDATE())

AND aro.DocumentID IS NULL

AND o.DocumentReceivedTime > DATEADD(HOUR, -12, GETUTCDATE())

--AND o.DocumentID NOT IN (203309, 217893) --blocked duplicates

ORDER BY o.DocumentID DESC

linq query

 

byte? a1 = 5;

var query = (from a in db.Documents

join b in db.Documents on new { x=a.DocumentReferenceNo, y = a.Sender,z=a.MessageTypeID} equals new

{

x =(b.DocumentReferenceNo.Replace("APVORD-","").Substring(0,(SqlFunctions.CharIndex("-", b.DocumentReferenceNo.Replace("APVORD-"," ") ) == 0 ? (b.DocumentReferenceNo.Length +1):(int)SqlFunctions.CharIndex("-", b.DocumentReferenceNo.Replace("APVORD-",""))))),

y = b.Sender,

z=a1

} into j1

from b in j1.DefaultIfEmpty()

join c in db.Trading_Partner on a.Recipient equals c.TPID

join d in db.Trading_Partner on (int)a.Sender equals d.TPID

where a.MessageTypeID == 2

&& a.Environment == 1

&& a.DocumentReceivedTime < SqlFunctions.DateAdd("minute",-3,SqlFunctions.GetUtcDate())

&& b.DocumentID.Equals(null) &&

a.DocumentReceivedTime > SqlFunctions.DateAdd("hour",-12,SqlFunctions.GetUtcDate())

orderby a.DocumentID descending

select new Bivir

{

DocumentID = a.DocumentID,

DocumentReceivedTime = a.DocumentReceivedTime,

DocumentReferenceNo = a.DocumentReferenceNo,

RelatedDocRefNo = b.DocumentReferenceNo,

TimeSince = (double?)Math.Abs((double)SqlFunctions.DateDiff("minute",a.DocumentReceivedTime,SqlFunctions.GetUtcDate()))

}).ToList();