Copy queue name from one data table to another data table without using loops

[Surya_Narayana_Korivipadu]

@ppr I’ve seen the update.

I may not be able to build such query.

But as per my understanding it should be f(“Id”) and then d(“QueueDefinitionId”), because f belongs to QueueDefinitionsDT and d belongs to QueueItemsDT. Can you please correct me if my understanding is wrong?

I assume your linq query is taking each Id from QueueDefinitionsDT and trying to match to QueueDefinitionId for every row in QueueItemsDT and then preparing Name column in FinalQueueDT. Am I correct? If not, can you please elaborate what this linq query does?

[ppr (Peter)]

You are correct. It was just swapped. Can you test again?

Let qn = QueueDefinitionsDT.Where(Function (f) f("Id").toString.Trim.Equals("d("QueueDefinitionId").toString.Trim)).Select(Function (x) x("Name").toString).DefaultIfEmpty("Undefined").First()

Here we are searching in QueueDefinitionsDT for the Id from our QueueItemsDT row
In case of we do not find then we are returning a default value “Undefined” in our imp

[postwick (Paul)]

Why would you copy the value from one table to another? This is two tables in a relational format, and you can use a SQL join to get the resulting data you want without actually copying the value. This is valuable, because if something changes in one table, you don’t have to worry about updating the other table - it’s relational. The point to relational data is you don’t have to copy values from one table to another.

SELECT * FROM QueueDefinitionsDT.*, QueueItemsDT.* FROM QueueItemsDT INNER JOIN QueueDefinitionsDT ON QueueDefinitionsDT.Id = QueueItemsDT.QueueDefinitionId

[Surya_Narayana_Korivipadu]

I’m aware of Joins concept. But I’m not sure if inner join can give me my result, because

1. the no. of rows in QueueItemsDT and QueueDefinitionsDT are not same
2. QueueItemsDT contains many rows of data where QueueDefinitionId can be a duplicate.

Anyways, thank you for the response. I’ll try to understand and try it in my code.

[Surya_Narayana_Korivipadu]

Hi @ppr

I’m trying this linq. I got an error “Where is not a member of System.Data.Datatable”

[ppr (Peter)]

Sorry, today is Bug day. Corrected
From d in QueueItemsDT.AsEnumerable

[Surya_Narayana_Korivipadu]

ha ha. no problem. I’m still learning linq. So it’s taking time for me to understand the complex queries.

Thank you very much for your quick responses. I’ll try this.

[ppr (Peter)]

For learning LINQ also hava look here:

[HowTo] LINQ (VB.Net) Learning Catalogue

This topic references the different Learning HowTos related to LINQ. It will be updated on new published HowTos as well LINQ Learning: Topic LINK First Start with LINQ [HowTo] - First Start with LINQ (VB.Net) Exploring the LINQ universe [HowTo] - Exploring the LINQ Universe (VB.Net) Operators Topic LINK Where [HowTo] LINQ - First Start # Where Operator Select work in progress Any [HowTo] LINQ – First Start # Any Operator All [HowTo] LINQ – First Start # All Operato…

Are the datatable stored in a datavase? Does mean do you retrieve it via SQL Statements?

[Surya_Narayana_Korivipadu]

yes. I’m learning from tutorialsteacher.com as you said in your previous replies to my another posts.

Sorry to bother you once again. Data is not in DB. It’s directly in data table variables. I tried .AsEnumerable for both data tables and I got the following error:

Multiple Assign - Final Queue DT: Can not assign ‘(From d In QueueItemsDT.AsEnumerable
Let qn = QueueDefinitionsDT.AsEnumerable.Where(Function (f) f(“Id”).tostring.trim.equals(d(“QueueDefinitionId”).tostring.trim)).Select(Function (x) x(“Name”).tostring).DefaultIfEmpty(“Undefined”).First()
Let ra = d.ItemArray.Append(qn)
Select r= FinalQueueDT.Rows.Add(ra)).CopyToDataTable’ to ‘FinalQueueDT’.

I’m not able to understand what went wrong. Can you please help?

[ppr (Peter)]

So i cross checked last version and it was valid:

May we ask you to compare to your implementation thanks

@Surya_Narayana_Korivipadu

Data is not in DB. It’s directly in data table variables.

OK, so things cannot be done in SQL directly

As an alternate find a inner join approach here:

(From d1 In QueueItemsDT.AsEnumerable
Join d2 In QueueDefinitionsDT.AsEnumerable
On d1("QueueDefinitionId").toString.Trim Equals d2("Id").toString.Trim
Let qn =d2("Name").tostring
Let ra = d1.ItemArray.Append(qn)
Select r= FinalQueueDT.Rows.Add(ra)).CopyToDataTable

Ensutre that from Copy & Paste all " are correctly taken and no “ or ” is used

[postwick (Paul)]

@Surya_Narayana_Korivipadu

the no. of rows in QueueItemsDT and QueueDefinitionsDT are not same

Doesn’t matter. Inner Join will only give you matching rows. Outer Join will give you all rows from one table, plus matching rows from the other table.

“QueueItemsDT contains many rows of data where QueueDefinitionId can be a duplicate.”

Doesn’t matter.

[Surya_Narayana_Korivipadu]

Hi @ppr

May be I gave extra new lines in my query. I’ll cross check once again and I’ll get back probably next week.

By the way, thank you for sharing the query with join method

[Surya_Narayana_Korivipadu]

Hi @ppr

This linq which prepares FinalQueueDT is valid syntactically. I cross checked and it looks correct. Also updated case properly in toString, Trim and Equals. Still I’m getting same error.

Here’s linq:

Tried to assign this to FinalQueueDT. I got the error. So created one more DT as FinalQueueDT2, but still I’m getting same error.

I haven’t tried the join query yet. I’ll try that too

[ppr (Peter)]

can you please share last validation message?

[Surya_Narayana_Korivipadu]

Did you mean error message?

Here it is:
Multiple Assign - Final Queue DT: Can not assign ‘(From d In QueueItemsDT.AsEnumerable
Let qn = QueueDefinitionsDT.AsEnumerable.Where(Function (f) f(“Id”).toString.Trim.Equals(d(“QueueDefinitionId”).toString.Trim)).Select(Function (x) x(“Name”).toString).DefaultIfEmpty(“Undefined”).First()
Let ra = d.ItemArray.Append(qn)
Select r= FinalQueueDT.Rows.Add(ra)).CopyToDataTable’ to ‘FinalQueueDT’.

This time I used “FinalQueueDT” in both left hand side and right hand side of assign activity

[ppr (Peter)]

use single assign
use statement as tested and provided (Post 14)
About ValidationMessage - if it is failing then create screenshot from hover containing the message
Test alternate Join Syntax

Thanks

[Surya_Narayana_Korivipadu]

@ppr

Used assign activity and tried Join query as well. Got some more error messages as above.

I feel that there’s a problem with QueueDefinitionId data type when assigning result back to FinalQueueDT.

[ppr (Peter)]

check if following is fixing:
Join

Let ra = d1.ItemArray.Append(qn).toArray

first LINQ
d.ItemArray.Append(qn).toArray

[Surya_Narayana_Korivipadu]

Thank you @ppr

I tried .toArray on the join query and it is working. Thank you very much.