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