Sql to linq Converion of a query

Question

Hi I am new to linq query i am trying to convert the following query to linq

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

LEFT JOIN [BISDW1D].[dbo].Trading_Partner supp

ON supp.TPID = o.Recipient

LEFT JOIN [BISDW1D].[dbo].Trading_Partner ph

ON ph.TPID = o.Sender

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 aro.MessageTypeID = 5

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

ORDER BY o.DocumentID DESC

So far what i have done is  not giving result can anybody suggest me how to improve it?

 

var query = (from a in db.Documents

join b in db.Documents on new { q = a.DocumentReferenceNo, y = a.Sender } equals new { q= b.DocumentReferenceNo.Replace( "APVORD-", ""),(SqlFunctions.CharIndex( "-" && b.DocumentReferenceNo.Replace( "APVORD-", "")== "0" ? b.DocumentReferenceNo.Length+1 )),y=b.Sender}

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

join d in db.Trading_Partner on a.Sender equals d.TPID

where a.MessageTypeID == 2 && a.Environment == 1 && b.DocumentID == null && b.MessageTypeID==5

select new Bivir

{

DocumentID = a.DocumentID,

DocumentReceivedTime = a.DocumentReceivedTime,

DocumentReferenceNo = a.DocumentReferenceNo,

RelatedDocRefNo = b.DocumentReferenceNo,

});