VB ASP.NET - Have a Linq command to display in GridView

I have a Linq in VB that I need to display in a gridview:
Dim myLnq = From x In Db.tbl
                                  Where x.field = "foobar" 

Open in new window


I have tried following the link below but don't quite understand what needs doing:

https://www.experts-exchange.com/questions/27632834/return-LINQ-results-and-bind-to-gridview-VB-NET.html
UnboundAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

it_saigeDeveloperCommented:
Essentially, you want to take the results of your query and convert it to a list or a datatable; e.g. -

Form1.vb -
Imports System.ComponentModel
Imports System.Runtime.CompilerServices

Public Class Form1
	Private Sub OnLoad(sender As Object, e As EventArgs) Handles MyBase.Load
		DataGridView1.DataSource = (From i In Enumerable.Range(0, 30) Select New With {.ID = i, .Name = $"Employee{i}", .JobType = If(i Mod 4 = 0, 4, If(i Mod 3 = 0, 3, If(i Mod 2 = 0, 2, 1))), .HireDate = DateTime.Now.AddMonths(-(9 * i))}).ToList()
		DataGridView2.DataSource = (From i In Enumerable.Range(0, 30) Select New With {.ID = i, .Name = $"Employee{i}", .JobType = If(i Mod 4 = 0, 4, If(i Mod 3 = 0, 3, If(i Mod 2 = 0, 2, 1))), .HireDate = DateTime.Now.AddMonths(-(9 * i))}).ConvertToDataTable()
	End Sub

	Private Sub OnScroll(sender As Object, e As ScrollEventArgs) Handles DataGridView2.Scroll, DataGridView1.Scroll
		If TypeOf sender Is DataGridView Then
			Dim grid As DataGridView = CType(sender, DataGridView)
			If (grid.Equals(DataGridView1)) Then
				DataGridView2.FirstDisplayedScrollingRowIndex = grid.FirstDisplayedScrollingRowIndex
				DataGridView2.HorizontalScrollingOffset = grid.HorizontalScrollingOffset
			Else
				DataGridView1.FirstDisplayedScrollingRowIndex = grid.FirstDisplayedScrollingRowIndex
				DataGridView1.HorizontalScrollingOffset = grid.HorizontalScrollingOffset
			End If
		End If
	End Sub
End Class

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

Form1.Designer.vb -
<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> _
Partial Class Form1
	Inherits System.Windows.Forms.Form

	'Form overrides dispose to clean up the component list.
	<System.Diagnostics.DebuggerNonUserCode()> _
	Protected Overrides Sub Dispose(ByVal disposing As Boolean)
		Try
			If disposing AndAlso components IsNot Nothing Then
				components.Dispose()
			End If
		Finally
			MyBase.Dispose(disposing)
		End Try
	End Sub

	'Required by the Windows Form Designer
	Private components As System.ComponentModel.IContainer

	'NOTE: The following procedure is required by the Windows Form Designer
	'It can be modified using the Windows Form Designer.  
	'Do not modify it using the code editor.
	<System.Diagnostics.DebuggerStepThrough()> _
	Private Sub InitializeComponent()
		Me.DataGridView1 = New System.Windows.Forms.DataGridView()
		Me.DataGridView2 = New System.Windows.Forms.DataGridView()
		Me.Label1 = New System.Windows.Forms.Label()
		Me.Label2 = New System.Windows.Forms.Label()
		CType(Me.DataGridView1, System.ComponentModel.ISupportInitialize).BeginInit()
		CType(Me.DataGridView2, System.ComponentModel.ISupportInitialize).BeginInit()
		Me.SuspendLayout()
		'
		'DataGridView1
		'
		Me.DataGridView1.AutoSizeColumnsMode = System.Windows.Forms.DataGridViewAutoSizeColumnsMode.Fill
		Me.DataGridView1.AutoSizeRowsMode = System.Windows.Forms.DataGridViewAutoSizeRowsMode.AllCells
		Me.DataGridView1.ColumnHeadersHeightSizeMode = System.Windows.Forms.DataGridViewColumnHeadersHeightSizeMode.AutoSize
		Me.DataGridView1.Location = New System.Drawing.Point(12, 29)
		Me.DataGridView1.Name = "DataGridView1"
		Me.DataGridView1.RowHeadersVisible = False
		Me.DataGridView1.SelectionMode = System.Windows.Forms.DataGridViewSelectionMode.FullRowSelect
		Me.DataGridView1.Size = New System.Drawing.Size(713, 200)
		Me.DataGridView1.TabIndex = 0
		'
		'DataGridView2
		'
		Me.DataGridView2.AutoSizeColumnsMode = System.Windows.Forms.DataGridViewAutoSizeColumnsMode.Fill
		Me.DataGridView2.AutoSizeRowsMode = System.Windows.Forms.DataGridViewAutoSizeRowsMode.AllCells
		Me.DataGridView2.ColumnHeadersHeightSizeMode = System.Windows.Forms.DataGridViewColumnHeadersHeightSizeMode.AutoSize
		Me.DataGridView2.Location = New System.Drawing.Point(12, 248)
		Me.DataGridView2.Name = "DataGridView2"
		Me.DataGridView2.RowHeadersVisible = False
		Me.DataGridView2.SelectionMode = System.Windows.Forms.DataGridViewSelectionMode.FullRowSelect
		Me.DataGridView2.Size = New System.Drawing.Size(713, 200)
		Me.DataGridView2.TabIndex = 1
		'
		'Label1
		'
		Me.Label1.AutoSize = True
		Me.Label1.Location = New System.Drawing.Point(13, 13)
		Me.Label1.Name = "Label1"
		Me.Label1.Size = New System.Drawing.Size(92, 13)
		Me.Label1.TabIndex = 2
		Me.Label1.Text = "Bound from a List:"
		'
		'Label2
		'
		Me.Label2.AutoSize = True
		Me.Label2.Location = New System.Drawing.Point(13, 232)
		Me.Label2.Name = "Label2"
		Me.Label2.Size = New System.Drawing.Size(122, 13)
		Me.Label2.TabIndex = 3
		Me.Label2.Text = "Bound from a Datatable:"
		'
		'Form1
		'
		Me.AutoScaleDimensions = New System.Drawing.SizeF(6.0!, 13.0!)
		Me.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font
		Me.ClientSize = New System.Drawing.Size(737, 460)
		Me.Controls.Add(Me.Label2)
		Me.Controls.Add(Me.Label1)
		Me.Controls.Add(Me.DataGridView2)
		Me.Controls.Add(Me.DataGridView1)
		Me.Name = "Form1"
		Me.Text = "Form1"
		CType(Me.DataGridView1, System.ComponentModel.ISupportInitialize).EndInit()
		CType(Me.DataGridView2, System.ComponentModel.ISupportInitialize).EndInit()
		Me.ResumeLayout(False)
		Me.PerformLayout()

	End Sub

	Friend WithEvents DataGridView1 As DataGridView
	Friend WithEvents DataGridView2 As DataGridView
	Friend WithEvents Label1 As Label
	Friend WithEvents Label2 As Label
End Class

Open in new window

Which produces the following output -Capture.PNG-saige-
0
Fernando SotoRetiredCommented:
Hi Usama Ahmed;

Your query re-posted here
Dim myLnq = From x In Db.tbl
            Where x.field = "foobar" 

Open in new window

will return a collection of IQueryable(of tbl) with all the columns that is defined in the table. In order to display the results in a DataGrifView assign the variable myLng as a list to the DataSource property of the DataGridView. Something like the following
Dim myLnq = From x In Db.tbl
            Where x.field = "foobar"

'' Assuming that the name of the DataGridView is dvg
dvg.DataSource = myLng.ToList() 

Open in new window

0
UnboundAuthor Commented:
Hi #Fernando Soto

There is actually no ToList function for the below.
dvg.DataSource = myLng.ToList()

Open in new window


I did realise from the link from my original post that the DataSource  would accept a list from LINQ so I have fianlly been able to come up with the a piece of code that works:

Dim myLnq = (From x In Db.tbl
            Where x.field = "foobar").ToList() 

dvg.DataSource = myLnq

Open in new window

0
Fernando SotoRetiredCommented:
Hi Unbound;

You stated in your last post that this worked.
Dim myLnq = (From x In Db.tbl
             Where x.field = "foobar").ToList() 

dvg.DataSource = myLnq

Open in new window

What that does is sends the query to the database and when the results comes back turns the IQueryable returned from the query to a List and assigns that list to the variable myLng. The second line in the code assigns the variable myLng and assigns it to the DataGridView. What I posted does the exact same thing except it turns myLng from the IQueryable to a List just before assigning it to the DataGridView. So my point is if you have a ToList method where you placed it then you most certainly will have it available where I told you where to place it
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Fernando SotoRetiredCommented:
Please see my last post.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.