Sql to linq Converion of a query

Gunjan Manan

10y991LINQ

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,

});

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Manas Mohapatra10y ago
Replace your code:

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

with

Substring(0 -1 , (
SqlFunctions.CharIndex("-",aro.DocumentReferenceNo.Replace("APVORD-" , "")) == 0 ?
((int)aro.DocumentReferenceNo.Length + 1) :
(int)SqlFunctions.CharIndex("-",aro.DocumentReferenceNo.Replace("APVORD-" , ""))))
Gunjan Manan10y ago
Hi, I converted it to linq
from o in db.Documents
join aro in db.Documents
on new { o.DocumentReferenceNo, o.Sender, MessageTypeID = 5 }
equals new { DocumentReferenceNo = (aro.DocumentReferenceNo.Replace("APVORD-" , "")).Substring(0 -1 , (
SqlFunctions.CharIndex("-",aro.DocumentReferenceNo.Replace("APVORD-" , "")) == 0 ? ((int)aro.DocumentReferenceNo.Length + 1) : (System.Int64)SqlFunctions.CharIndex("-",aro.DocumentReferenceNo.Replace("APVORD-" , "")))), aro.Sender, MessageTypeID = (long)aro.MessageTypeID } into aro_join
from aro in aro_join.DefaultIfEmpty()
join supp in db.Trading_Partner on new { TPID = (int)o.Recipient } equals new { TPID = supp.TPID } into supp_join
from supp in supp_join.DefaultIfEmpty()
join ph in db.Trading_Partner on new { TPID = (int)o.Sender } equals new { TPID = ph.TPID } into ph_join
from ph in ph_join.DefaultIfEmpty()
where
o.MessageTypeID == 2 &&
o.Environment == 1 &&
o.DocumentReceivedTime < SqlFunctions.DateAdd("minute", - 3,SqlFunctions.GetUtcDate()) &&
aro.DocumentID == null &&
o.DocumentReceivedTime > SqlFunctions.DateAdd("hour", - 12,SqlFunctions.GetUtcDate())
orderby
o.DocumentID descending
select new {
o.DocumentID,
o.DocumentReceivedTime,
o.DocumentReferenceNo,
RelatedDocRefNo = aro.RelatedDocRefNo,
TimeSince = (double?)Math.Abs((double)SqlFunctions.DateDiff("minute",o.DocumentReceivedTime,SqlFunctions.GetUtcDate()))
}
the query looks like above query however i am getting now error
Error Compiling Expression: Error Compiling Expression: The best overloaded method match for 'string.Substring(int, int)' has some invalid arguments
Argument 2: cannot convert from 'long' to 'int'
The best overloaded method match for 'string.Substring(int, int)' has some invalid arguments
Argument 2: cannot convert from 'long' to 'int'
Manas Mohapatra10y ago
In Linq code, you are not using left outer join which is present is in your query.
Try to use outer join in Linq then you will get data.

http://www.dotnet-tricks.com/Tutorial/linq/UXPF181012-SQL-Joins-with-C

Secondly, your SQL query is bit complex. So you can use Linqer tool to convert SQL to linq

http://www.sqltolinq.com/downloads

Note: It would better if you use StoredProcedure and call it from Linq. As SQL is complex, .Net will take time to parse and execute query to get result.