Load LINQ query into datatable

Larry Brister
Larry Brister used Ask the Experts™
on
How would I load the results of this LINQ Query into a datatable?

        Dim Query = _
  (From Region In dataset1.Tables(0) _
  Select Region.Field(Of String)("Region")).Distinct()

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Éric MoreauSenior .Net Consultant
Top Expert 2016

Commented:
You need to use LoadDataRow. Check this example:

DataTable dt = new DataTable();
dt.Columns.Add("Id", typeof(int));
dt.Columns.Add("Name", typeof(string));
dt.Columns.Add("IsDefault", typeof(bool));
dt.Columns.Add("DisplayOrder", typeof(int));
dt.Columns.Add("FullName", typeof(string), "Name + Iif(IsDefault, ' *', '')");

//The LoadDataRow method fills the DataTable declared above
var items = (from item in Items
      orderby item.DisplayOrder, item.Name
      select dt.LoadDataRow(new object[] { item.Id, item.Name, item.IsDefault, item.DisplayOrder }, false)).ToList();
Larry Bristersr. Developer

Author

Commented:
Eric
That's in C# and I can't seem to get the conversion to work correctly
Fernando SotoRetired
Distinguished Expert 2017

Commented:
Hi  lrbrister;

The following is a generic code, helper class to do what you want to do. Please see the following article.

How to: Implement CopyToDataTable<T> Where the Generic Type T Is Not a DataRow
HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

Éric MoreauSenior .Net Consultant
Top Expert 2016

Commented:
have you tried:

dim items = (from item in Items
      orderby item.DisplayOrder, item.Name
      select dt.LoadDataRow(new object() { item.Id, item.Name, item.IsDefault, item.DisplayOrder }, false)).ToList()
Senior .Net Consultant
Top Expert 2016
Commented:
this is a working example in VB:

Public Class Form1

      Private Sub UltraButton1_Click(sender As Object, e As EventArgs) Handles UltraButton1.Click
            Dim dt As DataTable = New DataTable()
            dt.Columns.Add("Id", GetType(Integer))
            dt.Columns.Add("Name", GetType(String))


            '//The LoadDataRow method fills the DataTable declared above
            Dim Items As New List(Of ItemType)
            Items.Add(New ItemType() With {.Id = 1, .Name = "item 1"})
            Items.Add(New ItemType() With {.Id = 2, .Name = "item 2"})
            Items.Add(New ItemType() With {.Id = 3, .Name = "item 3"})
            Dim xx = (From item In Items Order By item.Name Select dt.LoadDataRow(New Object() {item.Id, item.Name}, False)).ToList()
      End Sub

End Class

Public Class ItemType

      Public Id As Integer
      Public Name As String

End Class
Larry Bristersr. Developer

Author

Commented:
Thanks for sticking with me

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial