Friday, December 18, 2009

SELECT * FROM Table JOIN... ListBox???

A Linq way to retrive rows from one table based on rows from a related table whose lines are loaded into a listbox.

The ASP.NET test WebForm:



I want all products related to the selected categories (ListBox.SelectionMode = Multiple). The project is an ASP.NET Web App on VS 2010 Beta 2, and it contains an Entity Data Model on the Northwind sample database.

The LinkButton code:

    Protected Sub LinkButton1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles LinkButton1.Click
Dim data As New NorthwindEntities
Dim selectedCategoriesProducts As New List(Of Products)
selectedCategoriesProducts = _
(From line As Products In data.Products
Join item As Integer In (From x In ListBox1.Items Where x.Selected = True Select x.value)
On line.CategoryID Equals item
Select line).ToList()
GridView1.DataSource = selectedCategoriesProducts
GridView1.DataBind()
End Sub
The Linq (to Entity) query joins the Products table (data.Products) to a integer list (Join item As Integer). The integer list is generated by a Linq (to Objects) query that selects the values (Select x.value) from the listbox options (ListBox1.Items) that are checked (Where x.Selected = True). The returned Products object list is then shown on the GridView.

In other words, we joined the Products table on Nortwind database with the ListBox selected items from an webform. Well, talk about data source abstraction... :-)

"Linq Is Fun!!!"
Buda On Using Linq To Query Data

2 comments:

  1. Fancy coming across this post four years later when looking for a solution to the same issues.
    Keep up the great work. Thanks !

    ReplyDelete
  2. Hi, Great.. Tutorial is just awesome..It is really helpful for a newbie like me.. I am a regular follower of your blog. Really very informative post you shared here. Kindly keep blogging. If anyone wants to become a .Net developer learn from Dot Net Training in Chennai. or learn thru Dot Net Training in Chennai. Nowadays Dot Net has tons of job opportunities on various vertical industry.
    or Javascript Training in Chennai. Nowadays JavaScript has tons of job opportunities on various vertical industry.

    ReplyDelete