Problems while constructing Linq query to find value in one Excel using a value from another

[RobinsonFrancis (Robin Francis)]

I need to look for a value iterate through an datatable. For each of the row, I take the a value from the first and search for whether it is available in another.
This is the first table:

I am planning to take the value in the first column, a.input_extern_file_name, and count the number of times that it appears in the first column of the second table shown below:

And then I would add it to another datatable, MissingDT which will have all the columns in the first datatable and an extra column which has the Count of times the value in the first column appears in the second table. I am able to achieve this using the following LINQ:

(From d In SourceDT.AsEnumerable()
             Let fileName = d.Field(Of String)("a.input_extern_file_name")
			 Let remarks  = ReferenceDT.AsEnumerable().Where(Function(f) f.Field(Of String)("INPUT FILENAME") = fileName).Count()
			 Let ra       = d.ItemArray.Take(3).Append(remarks).ToArray
			 Select MissingDT.Rows.Add(ra)).CopyToDataTable

However I am required to lookup the the value in the first column of the first file, a.input_extern_file_name, in 9 more datatables and update the number of rows in one Excel . So I thought I might use Invoke Code activity inside a For Each activity, where a different ReferenceDT is passed in as an argument.
However I don’t think it would work.
So I tried to write this query:

From d In SourceDT.AsEnumerable()
			 Let fileName = d.Field(Of String)("a.input_extern_file_name")
			 Let ra_sys   = d.Field(Of String)("a.ra_sys")
			 Let colName  = d.Field(Of String)("ColumnName")
			 Let remarks  = ReferenceDT.AsEnumerable().Where(Function(f) f.Field(Of String)("INPUT FILENAME") = fileName).Count()
			 Select d

And Assign it to a System.Data.EnumerableRowCollection[System.Data.DataRow]
variable but it only returns one row.
Can someone assist me on this. How can I lookup a value in a column in one table in another Excel, but it isn’t just one Excel I need to search but I also need to look into 9 other excels.
Attached is also my Code. I attached 2 files, one has the traditional approach using UiPath activities(SearchForFile.xaml) another is my attempt to translate it into LINQ(Filter.xaml). I need to use LINQ as the dataset is quite big. Please let me know if you need more information. Thank you.
SearchForFile.xaml (24.0 KB)
Filter.xaml (14.1 KB)

[kumar.varun2 (Varun Kumar)]

Hi @RobinsonFrancis

You could try with your first LINQ.

Follow the approach suggested below

Refer the xaml file

LINQ_ValueCountOfColumnInMultipleDT.xaml (12.6 KB)

[RobinsonFrancis (Robin Francis)]

I have tried it. And it does work. But it seems to be adding the table for every iteration.
I think I need to try to add each rows to the DataTable.
However, I am getting an error.
This is the code I am trying to build:
Filter.xaml (12.3 KB)

This is the code I have invoked:

Dim q = From d In Source.AsEnumerable()
	  Let fileName = d.Field(Of String)("a.input_extern_file_name")
	  Let ra_sys   = d.Field(Of String)("a.ra_sys")
	  Let colName  = d.Field(Of String)("ColumnName")
	  Let remarks  = Reference.AsEnumerable().Where(Function(f) f.Field(Of String)("INPUT_FILENAME") = fileName).Count()
	  Select d
For Each missingFile In q
	Missing.Rows.Add(missingFile)
Next

The exception that was thrown was as follows:

RemoteException wrapping System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation.  ---> RemoteException wrapping System.ArgumentException: This row already belongs to another table. 
   at System.Data.DataTable.InsertRow(DataRow row, Int64 proposedID, Int32 pos, Boolean fireEvent)
   at UiPathCodeRunner_e2d4c8e1f2b64e21a0a111c51b35f22e.Run(DataTable Reference, DataTable Source, DataTable& Missing)
	--- End of inner exception stack trace ---
   at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
   at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
   at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
   at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
   at UiPath.Activities.System.Utilities.InvokeCode.CompilerRunner.Run(Object[] args)
   at UiPath.Activities.System.Utilities.InvokeCode.NetCodeInvoker.Run(String userCode, List`1 inArgs, IEnumerable`1 imps, Object[] args)
   at UiPath.Core.Activities.InvokeCode.Execute(CodeActivityContext context)
   at System.Activities.CodeActivity.InternalExecute(ActivityInstance instance, ActivityExecutor executor, BookmarkManager bookmarkManager)
   at System.Activities.ActivityInstance.Execute(ActivityExecutor executor, BookmarkManager bookmarkManager)
   at System.Activities.Runtime.ActivityExecutor.ExecuteActivityWorkItem.ExecuteBody(ActivityExecutor executor, BookmarkManager bookmarkManager, Location resultLocation)

Could you help on this as well? Thank you.

[kumar.varun2 (Varun Kumar)]

Hi @RobinsonFrancis

If the Reference excel files have same structure

then give a try to this approach

LINQ_ValueCountOfColumnInMultipleDT_2.xaml (12.5 KB)