3. RPA Challenge - LINQ to extract values from datatable

datatable
linq

#1

Hi All,

Can someone help me with how to extract values from datatable using LINQ. I have a csv which I am reading and creating a datatable. CSV has two columns A and B. Column A defines ID’s and column B defines role.

Example
Column A Column B
1 Admin
2 Approver
2 Deletion

and so on… ID 2 can have multiple values defined as role.

I can do that with other method but want to know with use of LINQ.

Regards,
Ashish Manjrekar


Automation Challenge Winners
#2

hey @Ashish32229

Please go through with this link and let me know :slight_smile:

Thanks& Regards…!!


#3

Hi aksh,

I have gone through that however it’s very basic and I need for Datatable.

Regards,
Ashish Manjrekar


#4

Hi @Ashish32229

Please not that they were deliberately not allowed on DataTables, it’s just that DataTables pre-date the IQueryable and generic IEnumerable constructs on which Linq queries can be performed.

Both interfaces require some sort type-safety validation. DataTables are not strongly typed. This is the same reason why people can’t query against an ArrayList, for example.http://www.codecompiled.com/query-datatable-using-linq-in-c/

For Linq to work you need to map your results against type-safe objects and query against that instead.

You can check this article as well:, May be it will help you on this.

Regards…!!


#5

This is a good exercise. Moved to Automation Challenge :slight_smile:


#6

below Linq should get all the roles for a specific id:

(((From dr As Datarow In dt
Where Convert.ToString(dr(“ColumnA”))="2"
Select Convert.ToString(dr(“ColumnB”))).ToList()).Distinct()).ToList

This returns a List Of String
Is this what you wanted?


#7

We need an working XAML uploaded :slight_smile:


#8

Hello, this is the result I got.

ID : 1 Role in company : Analyst,
ID : 2 Role in company : Medical,Engineer,Accountant,IT,Specialist,
ID : 3 Role in company : Analyst,
ID : 4 Role in company : Scientist,Advisor,
ID : 5 Role in company : Scientist,

Please take a look and let me know if this count for linq, the thing is that I’m just using dataTable.Select("ID = " & idTolook) but not where or those type of things lol.
.3. RPA Challenge - LINQ to extract values from datatable.zip (3.2 KB)


#9

Hi everyone,

Please find attached a working workflow using linq to extract values from a DataTable :wink:

RPA Challenge - LINQ to extract values from datatable.zip (3.2 KB)


Need help
#10

Very elegant solution @Masire ! :smiley: :smiley:


#11

Thanks @richarddenton :+1:


#12

Assume the Datatable is DataTableObject having two columns “column1” and “column2”.

Below are certain examples of LINQ on this Datatable -

DataTableObject.AsEnumerable.Where(Function(dr) Convert.ToInt32(dr(“column1”)) = someString).Select(Function(dr) Convert.ToString(dr(“column2”))).ToArray()

DataTableObject.AsEnumerable.Where(Function(dr) Convert.ToInt32(dr(“column1”)) = someString).CopyToDataTable


#13

With CSV file saved and having the first cell filled with string : ID - there is issue to reopen csv file - it looks for sylk file and the content is messed up.

MS Office Bug :wink: just avoid ID in first cell.


#14

Hi Masire,

Can you please explain the in your assign activity you used as " from dr As datarow in dtData select convert …

sorry to bother as I did not understand that.

Thanks
Hari.