I am trying to replicate the below SQL query in LINQ but I am not getting the expected results, whereas the record counts conflict, I am trying to get matched records.
SQL Query - record count is 245(with both the condition)
SELECT [BANK] ,[Transaction Date] ,[TEXT] ,A.[Amount] ,[Reference No] ,[Value Date] ,[Branch Name] FROM [dbo].[BankStmt_Dec2019] A INNER JOIN [dbo].[BankTrn_Dec2019] B ON CONVERT(nvarchar, A.[Reference No]) = B.[Payment reference] AND A.Amount = B.Amount
LINQ query record count is 538 with both the conditions, when utilizing both conditions in Join Data Table activity the count comes down to zero.
Both the DT DTBankTrans and Stmnt_NewDT have records
(From a In Stmnt_NewDT.AsEnumerable Join b In DTBankTrans.AsEnumerable On a("Reference No").ToString() Equals b("Payment reference").ToString() And a("Amount").ToString.Trim Equals b("Amount").ToString.Trim Select a).CopyToDataTable()
Is this anything to do with the datatype of both the columns? as in raw data(in excel) Amount column cell format is Number and it’s up to two decimals.
P.S - In SQL and in my workflow, record counts of both the DT matched with SQL.