LINQ to SQL datagridview query returns length, not

问题:

I've got a Windows Form with a button and a datagridview. The project includes a working database connection and LINQ to SQL class. I'm trying to bind the datagridview to the LINQ to SQL.

In a code module I've got this:

Public Function DataGridList() As BindingSource
    Dim NewBindingSource As New BindingSource()
    Dim db As New DataClasses1DataContext()
    NewBindingSource.DataSource = _
    From Block In db.BLOCK_ASSIGNMENTs
        Where Block.gr912_school = "Franklin"
    Select Block.gr6_school Distinct
    Return NewBindingSource
End Function

And this button_click code in the form:

Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
    DataGridView1.DataSource = DataGridList()
End Sub

When I click the button I get the length of the school names in the datagridview, with a column header of "length."

If I just run this very similar code in the button_click instead, the school names appear correctly in the immediate window:

Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
    Dim db As New DataClasses1DataContext()
    Dim TestQuery =
    From Block In db.BLOCK_ASSIGNMENTs
        Where Block.gr912_school = "Franklin"
    Select Block.gr6_school Distinct
    For Each block In TestQuery
        Debug.Print(block)
    Next

End Sub

回答1:

Give this a try:

Public Function DataGridList() As BindingSource
    Dim NewBindingSource As New BindingSource()
    Dim db As New DataClasses1DataContext()
    NewBindingSource.DataSource = _
    From Block In db.BLOCK_ASSIGNMENTs
        Where Block.gr912_school = "Franklin"
    Select New With { Key .Value = Block.gr6_school } Distinct
    Return NewBindingSource
End Function

This should give it a property that the DataGridView can pick up on. The New With... creates an anonymous object with a Property named Value. The DataGridView works on this type of object by enumerating the public properties and rendering them as columns. If you had needed more than one value, you could have added additional items inside the curly braces in the same way separated by commas. See Anonymous Types (Visual Basic) for more information.

回答2:

You might try adding a .ToString to the Select:

   From Block In db.BLOCK_ASSIGNMENTs
        Where Block.gr912_school = "Franklin"
   Select Block.gr6_school.ToString Distinct

I believe that Debug.Print does an implicit conversion to .ToString when it prints to the immediate window. However, a datagrid cell treats everything as an object and displays the default property for that object.

回答3:

Turns out, of course, this has been addressed often, including on SO, and here. The route I chose was to use an intermediate DataTable:

Public Function DataGridList() As DataTable
    Dim NewDataTable As New DataTable
    Dim db As New DataClasses1DataContext()
    Dim i As Int32

    Dim qry =
    From Block In db.BLOCK_ASSIGNMENTs.AsEnumerable
        Where Block.gr912_school = "Franklin"
    Select Block.gr6_school Distinct

    NewDataTable.Columns.Add("School")
    For i = 0 To qry.Count - 1
        NewDataTable.Rows.Add(qry(i))
    Next
    Return NewDataTable
End Function

This seems pretty slow the first time it runs, so I may try something else in the future, but it allows me to feed the grid via LINQ, which is what I want to work with.

(I would like to use the qry's CopyToDataTable property, but it's only available if the query returns a DataTableRows collection, or some such, and my hacking around didn't reveal how to do that.)