Linq to SQL 绑定 ComboBox_.NET_编程开发_程序员俱乐部

最近学习Linq to SQL，发现Linq是一个开发轻量数据库的好东西，大大简化了数据连接、查询过程。但是在绑定ComBoBox的时间发现了一个问题：Linq查询后得到的数据tolist后，只能实现DataSource绑定，无法赋值DisplayMember和ValueMember，即使赋值后，SelectedValue也得不到想要的数值。例如：

Dim tempProvince = From cust In db.AddressProvince                           
                   Select New With {.id = cust.ProvinceID, .name = cust.ProvinceName}

cmbProvince.DataSource = tempProvince.tolist
cmbProvince.DisplayMember = "name"
cmbProvince.ValueMember = "id"

在网上搜索了几天，有说解决的办法是将List转换为DataTable，然后绑定，转换代码：

''' <summary>
''' List转换为DataTable
''' </summary>
''' <param name="list">List</param>
''' <returns>DataTable</returns>
''' <remarks></remarks>
Public Function ConvertToDataTable(ByVal list As IList) As DataTable
     Dim table As New DataTable()
     Dim fields() As PropertyInfo
     If list.Count > 0 Then
         fields = list（0）.GetType.GetProperties
         For Each field In fields
             table.Columns.Add(field.Name, field.PropertyType)
         Next
         For Each item In list
             Dim row As DataRow = table.NewRow()
             For Each field In fields
                 row(field.Name) = field.GetValue(item)
             Next
             table.Rows.Add(row)
         Next
     End If
     Return table
 End Function

 绑定代码：

Dim tempProvince = From cust In db.AddressProvince
                           Select New With {.id = cust.ProvinceID, .name = cust.ProvinceName}
Dim tempdatatable As New DataTable
tempdatatable = ConvertToDataTable(tempProvince.ToList)
cmbProvince.DataSource = tempdatatable '绑定DataTable数据时引发SelectedIndexChanged事件
cmbProvince.DisplayMember = "name"
cmbProvince.ValueMember = "id"

通过上述转换后，可以正确的绑定到ComBoBox了，但有出现了一个新问题，在绑定数据后引发了SelectedIndexChanged事件，ComBoBox的SelectedValue值还是错误的，代码：

Private Sub cmbProvince_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cmbProvince.SelectedIndexChanged        

  Dim tempCity = From cust In db.AddressCity
                 Where cust.ProvinceID = cmbProvince.SelectedValue.ToString
 
                 Select New With {.id = cust.CityID, .name = cust.CityName}
End Sub

郁闷了半天，灵机一动，更改后的绑定代码：

Dim tempProvince = From cust In db.AddressProvince
                           Select New With {.id = cust.ProvinceID, .name = cust.ProvinceName}
Dim tempdatatable As New DataTable
tempdatatable = ConvertToDataTable(tempProvince.ToList)
cmbProvince.DisplayMember = "name"
cmbProvince.ValueMember = "id"cmbProvince.DataSource = tempdatatable '重点：DataSource放到了DisplayMember和ValueMember赋值后。

哈哈，问题解决了！......

你以为这就结束了？

NO！

这不是重点，高潮来了：

心血来潮，把转换过程去掉了，直接绑定Linq查询结果：

Dim tempProvince = From cust In db.AddressProvince
                   Select cust.ProvinceID, cust.ProvinceName

cmbProvince.DisplayMember = "ProvinceName"
cmbProvince.ValueMember = "ProvinceID"
cmbProvince.DataSource = tempProvince

居然也正常运行了！！！

 最后总结：

ComBoBox可以绑定Linq查询的结果，唯一的要求就将DataSource放到ComBoBox的SelectedValue的后面。