Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 96
  • Last Modified:

Query datatable in LINQ

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
jagr12
Asked:
jagr12
  • 3
  • 2
2 Solutions
 
YZlatCommented:
Let me clarify, you want to update a value in existing datatable?
0
 
jagr12Author Commented:
No, If I pass OrderHeader_Id 1 it should return only

1            john       a1111      11111
1            john       b1111  22222
0
 
jagr12Author Commented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
jagr12Author Commented:
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
 
YZlatCommented:
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
 
it_saigeDeveloperCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now