Tutorial :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?

Solution:1

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

Solution:2

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)

Solution:3

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.

Solution:4

Change the following line

Dim table As DataTable = q.ToDataTable()

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

then you can try binding it to a GridView

DataGridView1.DataSource = table      DataGridView1.DataBind()

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com