Solved

Query datatable in LINQ

Posted on 2016-09-21
6
55 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
  • 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
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 

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 33

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
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.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

830 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