Solved

Query datatable in LINQ

Posted on 2016-09-21
6
66 Views
Last Modified: 2016-10-16
How can query datatable using linq and set the return value in a new datatable?

Dim dt As DataTable = From o In datableorder.Select 'Where u.Item("OrderHeader_Id") = Wherever value I want to pass to

datableorder

OrderHeader_Id      username itemno      zip
1            john       a1111      11111
1            john       b1111  22222
2            john       c1111      33333
2            john       c1111      11111
3 etc
0
Comment
Question by:jagr12
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 35

Expert Comment

by:YZlat
ID: 41809489
Let me clarify, you want to update a value in existing datatable?
0
 

Author Comment

by:jagr12
ID: 41809491
No, If I pass OrderHeader_Id 1 it should return only

1            john       a1111      11111
1            john       b1111  22222
0
 

Author Comment

by:jagr12
ID: 41809492
like Dim dt As DataTable = From o In datableorder.Select 'Where u.Item("OrderHeader_Id") = 1
this new dt datable should return only
1            john       a1111      11111
1            john       b1111  22222
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:jagr12
ID: 41809496
sorry, with header
like Dim dt As DataTable = From o In datableorder.Select 'Where u.Item("OrderHeader_Id") = 1
this new dt datable should return only

OrderHeader_Id      username itemno      zip
1            john       a1111      11111
1            john       b1111  22222
0
 
LVL 35

Assisted Solution

by:YZlat
YZlat earned 250 total points
ID: 41809514
try

var query= from ord in datatableorder whereord.OrderHeader_Id.Equals(val) select ord;

DataTable dt = query.CopyToDataTable<DataRow>();

Open in new window


or VB:

dim query

query= from ord in datatableorder whereord.OrderHeader_Id.Equals(val) select ord

Dim dt as DataTable = query.ToDataTable()

Open in new window

0
 
LVL 34

Accepted Solution

by:
it_saige earned 250 total points
ID: 41809567
If you don't want to move the rows from the original table then you could just use the CopyToDataTable method:
Imports System.Runtime.CompilerServices
Imports System.ComponentModel

Module Module1
	Sub Main()
		Dim table1 = (From i In Enumerable.Range(1, 20) Select New With {.OrderHeaderID = If(i And 1, i, i - 1), .UserName = "John", .ItemNo = String.Format("{0}1111", If(i And 1, Chr(65 + i), Chr(65 + (i - 1)))), .ZipCode = If(i And 1, 11111, 22222)}).ConvertToDataTable()
		Dim table2 = (From row In table1 Where row("OrderHeaderID").Equals(1) Select row).CopyToDataTable()
		Console.WriteLine("Rows in table2")
		For Each row In table2.Rows.Cast(Of DataRow)()
			Console.WriteLine("OrderHeaderID: {0}; UserName: {1}; ItemNo: {2}; ZipCode: {3}", row("OrderHeaderID"), row("UserName"), row("ItemNo"), row("ZipCode"))
		Next
		Console.WriteLine()
		Console.WriteLine("Rows in table1")
		For Each row In table1.Rows.Cast(Of DataRow)()
			Console.WriteLine("OrderHeaderID: {0}; UserName: {1}; ItemNo: {2}; ZipCode: {3}", row("OrderHeaderID"), row("UserName"), row("ItemNo"), row("ZipCode"))
		Next
		Console.ReadLine()
	End Sub
End Module

Module Extensions
	<Extension()> _
	Public Function ConvertToDataTable(Of T)(ByVal source As IEnumerable(Of T)) As DataTable
		Dim properties As PropertyDescriptorCollection = TypeDescriptor.GetProperties(GetType(T))
		Dim table As DataTable = New DataTable()

		For i As Integer = 0 To properties.Count - 1
			Dim [property] As PropertyDescriptor = properties(i)
			If [property].PropertyType.IsGenericType AndAlso [property].PropertyType.GetGenericTypeDefinition().Equals(GetType(Nullable)) Then
				table.Columns.Add([property].Name, [property].PropertyType.GetGenericArguments()(0))
			Else
				table.Columns.Add([property].Name, [property].PropertyType)
			End If
		Next

		Dim values(properties.Count - 1) As Object
		For Each item As T In source
			For i As Integer = 0 To properties.Count - 1
				values(i) = properties(i).GetValue(item)
			Next
			table.Rows.Add(values)
		Next

		Return table
	End Function
End Module

Open in new window

Which produces the following output -Capture.JPG
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Send SMS from vb.net desktop app 30 51
Timeouts during development 3 28
.NET Core supports which cell phone platforms? 3 35
Intermittent OleDbConnection Error 20 52
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question