SQL query to linq

Help Studio
, , , , ,
1

I have the following SQL Query:
SELECT ABC As Division , MPN, FORMAT(Period,‘M/d/yyyy’) As Period, SUM(Quantity) As Quantity FROM Upload$ Group By MPN, Period.

where Upload is the worksheet name.

Let us assume that the data present in the worksheet named “Upload” is present in a data table named “UploadDT”. What can be the equivalent linq statement.

2

Hi @svinjamuri ,

You can get reference from below video.

3

Hi @svinjamuri

In order to convert the above SQL query into LINQ we will first prepare the structure of the ouput Data Table for we will use the Build Data Table Activity in which the column data type and Column Names will be mentioned as shown below. This the output Data Table. As you can see the column names are same as written after the “As” in SQL. The Data Table variable used to store the output outDT

image

Since I am not reading the input from the excel I am using the another build data Table to construct the uploadDT

image

Now, use the assign activity.

image

The LINQ used is

(From r In uploadDT.AsEnumerable()
Group r By obj= New With{Key.new_mpn=r.Item("MPN"), Key.new_date=r.item("Date")}
Into grp=Group
Select outDT.Rows.Add({String.Join(", ", grp.Select(Function(x) x.Item("ABC").ToString)), obj.new_mpn, obj.new_date, grp.Sum(Function (x) CInt(x.Item("Quantity")))})).AsEnumerable.OrderBy(Function(x) x(0).ToString).CopyToDataTable

Finally you can use the write range or Output Data Table activity to view the output.

Attached xaml file

GroupByMultipleColumnx.xaml (12.2 KB)

Note

If you can provide a sample input and output it can be tailored according to you requirements

4

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.