Not Getting expected results in LINQ compare to SQL

Help
, , ,
1

Hi,
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.

2

@indrajit.shah
besides on our discussions via messages I would like to adress your question on a independed base.

The case can be interpretated that the data comes from a Database and the data can be fetched from there with a SQL Statement.

  • It is recommended to check if the strategy to collect the data from the source also in an already processed / consolidated format is maybe the prefered approach. The benefit is that the needed data is transported and only this data without other overhead is forwarded to the further processing

The question can also be interpretated as it is to find the corresponding LINQ statement doing the same as the SQL Statement is defining:

(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()

On the first look the statement looks fine and should do it. In such a case following analysis can be driven:

  • debugging / datatable content checking - maybe it gives some hints on value format variations
  • statement checking within watch / immediate panel with a reduced data set (Take/Skip usages)

as an alternate following can also be cross checked:

  • output of the join datatable activity
  • LINQ on a where any approach:

grafik
grafik1071Ă—73 7.15 KB

(From a In Stmnt_NewDT.AsEnumerable
Where DTBankTrans.AsEnumerable.Any(Function (b) a(“Reference No”).ToString.Trim.Equals(b(“Payment reference”).ToString.Trim) And a(“Amount”).ToString.Trim.Equals(b(“Amount”).ToString.Trim))
Select a).CopyToDataTable()

Kindly note: empty result and copytodatatable can be handled more defensive by following (toList, check count approach)
grafik

Let us know the results from your further analysis steps. Thanks

1 Like
3

Thank you for your reply and guidance.

It’s the is 2nd scenario, My data comes in an excel file only, to check/verify the desired output I tried writing a few SQL queries.

I didn’t get to try the suggestion you shared.

  • I am trying to get the desired output maintaining the format for Stmnt_NewDT

  • I am using below LING to get the rest of the records where these strings don’t contain, instead, I am getting records where the string contains, can you please give a look

      Stmt_RawDT.AsEnumerable().Where(Function(row) Not(row(" TEXT").ToString.Trim.Contains("CHQ RET")) And 
  (row(" TEXT").ToString.Trim.Contains("CHQ DEP RET"))).CopyToDataTable

Also when writing the data in excel the format of a few columns changed, I have sent you a personal message with screenshots.