Convert a LINQ Query Resultset to a DataTable

Question

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?

Answer 1

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.

Answer 2

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()

Answer 3

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