I've got (what I consider to be) a relatively complex SQL query to select some data for a new feature for an app I maintain at work. Here is the query.
SELECT pchtq.[sourceappid],
pchtq.[transactioncode],
pchtq.[accountid],
pchtq.[year],
pchtq.[filingdatetime],
pchtq.[amount],
pchtq.[note],
pchtq.Status,
pchtq.SourceRecordID,
utaq.fullname,
utaq.username
FROM [database].[dbo].[transactions1] pchtq
INNER JOIN [database].[dbo].[useraccounts] utaq
ON pchtq.[accountid] = utaq.[accountid]
WHERE
pchtq.status = 'failed'
AND pchtq.[recordcreatedatetime] >= '01/01/2015'
AND pchtq.[recordcreatedatetime] <= '05/11/2016'
and Not exists
(
select TransactionID
from
Database.dbo.Transactions2 eft
where
CAST(eft.TransactionID as nvarchar(50)) = pchtq.SourceRecordID
and eft.status IN( 'paid', 'audit' )
)
In entityspaces, I have it written like this:
pchtq
.Select(
pchtq.SourceAppID,
pchtq.TransactionCode,
pchtq.AccountID,
pchtq.Year,
pchtq.FilingDateTime,
pchtq.Amount,
pchtq.Note
).InnerJoin(utaq).On(pchtq.AccountID == utaq.AccountID)
.Where(pchtq.RecordCreateDateTime >= request.StartDate
&& pchtq.RecordCreateDateTime <= request.EndDate
&& pchtq.Status == "FAILED")
.NotExists(
eftq.Select(
eftq.EFileTransactionID
).Where(eftq.Status.In("Paid", "Audit")
&& Convert.ToString(eftq.TransactionID) == pchtq.SourceRecordID));
However, when I run it in the ES app (using pchtc.Load(pchtq)), I get about 7500 rows, whereas when I run the SQL query, I get about 1500.
What's going wrong here?
