Solved

Query datatable in LINQ

Posted on 2016-09-21
6
40 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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

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 32

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction Hi all and welcome to my first article on Experts Exchange. A while ago, someone asked me if i could do some tutorials on object oriented programming. I decided to do them on C#. Now you may ask me, why's that? Well, one of the re…
This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

743 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now