LINQ to SQL: union on constant values

Question

I want to populate a dropdown with values from database and constant hard-coded literals using linq. For example, assuming I already have northwind datacontext, i want to populate the dropdown with category table and some constants so that the dropdown will have something like below.

value text

0 [All] -1 [None] 1 Beverages 2 Condiments 3 Confections : : 8 Seafood

and so on. First two items are hard-coded constants and the rest are from database using linq.

I tried this, but did not work. Please help. Thanks.

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim db As New NorthwindDataContext

Dim q = From c In db.Categories _
Select c.CategoryID, c.CategoryName

Dim q2 = From c In db.Categories _
Select CategoryID = 0, CategoryName = "All"

DropDownList1.DataTextField = "CategoryName"
DropDownList1.DataValueField = "CategoryID"
DropDownList1.DataSource = q.Union(q2)
DropDownList1.DataBind()
End Sub

I want to use LINQ to accomplish this. Yes, you can add to DropdownList but...

Answer 1

you don't have to use LINQ to add those items to the DropDown ... use DropDownList.Items.Add() or DropDownList.Items.Insert() after you bound the control

Since you want to add your hardcodes items at the start of the list, Insert() would work better for you.

See this article for more info

Answer 2

Try with (the extraCats expression is meant to be an inline array declaration, used c# syntax)

Dim db As New NorthwindDataContext

Dim q = From c In db.Categories _
Select c.CategoryID, c.CategoryName
Dim dbCats = q.ToList()

Dim extraCats = new[] { 
    new { CategoryID = 0, CategoryName = "All" }
   }

DropDownList1.DataTextField = "CategoryName"
DropDownList1.DataValueField = "CategoryID"
DropDownList1.DataSource = dbCats.Union(extraCats)
DropDownList1.DataBind()

Answer 3

You can just add your hard-coded values to the dropdown, set the AppendDataBoundItems property to true, and then set your datasource as normally.

DropDownList1.Items.Add(New ListItem("0", "All"))
DropDownList1.AppendDataBoundItems = True
DropDownList1.DataTextField = "CategoryName"
DropDownList1.DataValueField = "CategoryID"
DropDownList1.DataSource = q
DropDownList1.DataBind()