Convert a LINQ Query Resultset to a DataTable

How can i write this query with LINQ to a FoxPro database?

SELECT count(*) FROM Table group by item1

I wrote it as below, but it doesn't work

Dim Query

Dim dt As New DataTable

Dim da = New Odbc.OdbcDataAdapter("SELECT * FROM  table1",connection)
da.Fill(dt)

Query = (From row In dt.AsEnumerable Select row_
          Group By item1 = row.Item(6) Into_       
         count = Count(row.Item(6))).ToList

The following line works:

    Dim q = From p In dt Group p By item = p.Item(6) Into count = Count()

How can I bind the results of the above query to a GridView? Unfortunately, setting q as DataSource doesn't work

    grid.DataSource= q

I found that i shoud bind it this way

    Dim table As DataTable = q.ToDataTable()
    DataGridView1.DataSource = table
开发者_开发技巧

but i et error like this

    'copytodatatable' is not a member of 'System.Collections.Generic.IEnumerable

what is this error refers to?

You could skip the DataTable all together and use LINQ to VFP.

I searched for days and finally came across this great function written by Lus Oliveira that converts LINQ result to a DataTable. ( Article ) The actual function:

    Public Function EQToDataTable(ByVal parIList As System.Collections.IEnumerable) As System.Data.DataTable
    Dim ret As New System.Data.DataTable()
    Try
        Dim ppi As System.Reflection.PropertyInfo() = Nothing
        If parIList Is Nothing Then Return ret
        For Each itm In parIList
            If ppi Is Nothing Then
                ppi = DirectCast(itm.[GetType](), System.Type).GetProperties()
                For Each pi As System.Reflection.PropertyInfo In ppi
                    Dim colType As System.Type = pi.PropertyType
                    If (colType.IsGenericType) AndAlso
                       (colType.GetGenericTypeDefinition() Is GetType(System.Nullable(Of ))) Then colType = colType.GetGenericArguments()(0)
                    ret.Columns.Add(New System.Data.DataColumn(pi.Name, colType))
                Next
            End If
            Dim dr As System.Data.DataRow = ret.NewRow
            For Each pi As System.Reflection.PropertyInfo In ppi
                dr(pi.Name) = If(pi.GetValue(itm, Nothing) Is Nothing, DBNull.Value, pi.GetValue(itm, Nothing))
            Next
            ret.Rows.Add(dr)
        Next
        For Each c As System.Data.DataColumn In ret.Columns
            c.ColumnName = c.ColumnName.Replace("_", " ")
        Next
    Catch ex As Exception
        ret = New System.Data.DataTable()
    End Try
    Return ret
End Function

It is called by:

dim q = [linq query you  write]
Dim dt as DataTable = EQToDataTable(q)

Your query is pulling EVERY record down... if you want the count, you can just do...

select COUNT(*) from Table1

or, to explicitly name the result column OF the count...

select COUNT(*) as CountOfRecords from Table1

If you want to intentionally pull all the records down and know how many actual records in the result set (full table, or where clause applied)... You can get the results from

int TotalRecords = dt.Rows.Count

My bad... group by the item...

select item, count(*) as TotalPerItem from Table1 group by item

Then, the dt.Rows.Count would have how many "ITEMS" were returned in the list... You would then be able to scroll through the records per item and have its respective count for that item.

Change the following line

Dim table As DataTable = q.ToDataTable() 

to

Dim table As DataTable = q.ToDataTable().AsEnumerable()

then you can try binding it to a GridView

DataGridView1.DataSource = table    
DataGridView1.DataBind()