Linq Help to Convert SQL

Help Activities
, , ,
1

HI,

Can we can convert below sql query into Linq

Select Distinct internal.[Match Group],Quality_Break.[Trade Date],Quality_Break.Qty_Int,Quality_Break.Qty_Ext from Internal Inner Join Quantity_Break on (Internal.[Match Group]=Quantity_Break.[Match Group]) And (format (Internal.[Trade Date],“mm/dd/yyyy”)=Quantity_Break.[Trade Date]) where (((internal.Reason)=‘Not Allowed’));

needed output in datatable.
Any help will be appreciated

2

Hi @TUSHAR_DIWASE

result = (From internalRow In internalTable.AsEnumerable()
 Join quantityRow In quantityBreakTable.AsEnumerable()
 On internalRow.Field(Of String)("Match Group") Equals quantityRow.Field(Of String)("Match Group")
 And internalRow.Field(Of DateTime)("Trade Date").ToString("MM/dd/yyyy") Equals quantityRow.Field(Of DateTime)("Trade Date").ToString("MM/dd/yyyy")
 Where internalRow.Field(Of String)("Reason") = "Not Allowed"
 Select resultTable.LoadDataRow(New Object() {
     internalRow.Field(Of String)("Match Group"),
     quantityRow.Field(Of DateTime)("Trade Date"),
     quantityRow.Field(Of Double)("Qty_Int"),
     quantityRow.Field(Of Double)("Qty_Ext")
 }, False)).CopyToDataTable()
3

Hi @TUSHAR_DIWASE

Try this LINQ:

Dtresult = (From internalRow In InputDt.AsEnumerable() 
Join qualityBreakRow In qualityBreakTable.AsEnumerable() 
On internalRow.Field(Of String)("Match Group") Equals qualityBreakRow.Field(Of String)("Match Group") _
And internalRow.Field(Of DateTime)("Trade Date").ToString("MM/dd/yyyy") Equals qualityBreakRow.Field(Of String)("Trade Date") 
Where internalRow.Field(Of String)("Reason") = "Not Allowed" 
Select OutputDt.Clone().LoadDataRow(New Object() _
	{ internalRow.Field(Of String)("Match Group"), qualityBreakRow.Field(Of String)("Trade Date"), qualityBreakRow.Field(Of Integer)("Qty_Int"), qualityBreakRow.Field(Of Integer)("Qty_Ext") }, False)).Distinct().CopyToDataTable()

Hope it helps!!

4

The image shows a compilation error message window listing multiple code syntax and declaration errors, such as BC30035, BC30800, and BC30451, indicating issues that need resolution for successful code execution. (Captioned by AI)
The image shows a compilation error message window listing multiple code syntax and declaration errors, such as BC30035, BC30800, and BC30451, indicating issues that need resolution for successful code execution. (Captioned by AI)812×539 179 KB

getting error

5

@TUSHAR_DIWASE

Use Assign activity not invoke code

Dtresult = (From internalRow In InputDt.AsEnumerable() 
Join qualityBreakRow In qualityBreakTable.AsEnumerable() 
On internalRow.Field(Of String)("Match Group") Equals qualityBreakRow.Field(Of String)("Match Group") _
And internalRow.Field(Of DateTime)("Trade Date").ToString("MM/dd/yyyy") Equals qualityBreakRow.Field(Of String)("Trade Date") 
Where internalRow.Field(Of String)("Reason") = "Not Allowed" 
Select OutputDt.Clone().LoadDataRow(New Object() _
	{ internalRow.Field(Of String)("Match Group"), qualityBreakRow.Field(Of String)("Trade Date"), qualityBreakRow.Field(Of Integer)("Qty_Int"), qualityBreakRow.Field(Of Integer)("Qty_Ext") }, False)).Distinct().CopyToDataTable()
6

Hi @TUSHAR_DIWASE

Please use that syntax in Assign activity.

Regards