Using Linq count function with vb.net datatable

Question

I write the code below. I know it was stupid. I want to make short using linq count. Please give me the light.

            Dim query0 = From obj In dtAginglist _
                        Where (obj.Field(Of String)("CurrentStatus") = "Open" Or obj.Field(Of String)("CurrentStatus") = "Acknowledge") _
                          And obj.Field(Of Integer)("OpenDays") = 0
            Dim count0 As Integer = query0.Count

            Dim query1 = From obj In dtAginglist _
                        Where (obj.Field(Of String)("CurrentStatus") = "Open" Or obj.Field(Of String)("CurrentStatus") = "Acknowledge") _
                          And obj.Field(Of Integer)("OpenDays") = 1
            Dim count1 As Integer = query1.Count

            Dim query2 = From obj In dtAginglist _
                        Where (obj.Field(Of String)("CurrentStatus") = "Open" Or obj.Field(Of String)("CurrentStatus") = "Acknowledge") _
                          And obj.Field(Of Integer)("OpenDays") = 1
            Dim count2 As Integer = query2.Count


            Dim query3 = From obj In dtAginglist _
                        Where (obj.Field(Of String)("CurrentStatus") = "Open" Or obj.Field(Of String)("CurrentStatus") = "Acknowledge") _
                          And obj.Field(Of Integer)("OpenDays") = 3
            Dim count3 As Integer = query3.Count

            Dim query4 = From obj In dtAginglist _
                        Where (obj.Field(Of String)("CurrentStatus") = "Open" Or obj.Field(Of String)("CurrentStatus") = "Acknowledge") _
                          And obj.Field(Of Integer)("OpenDays") = 4
            Dim count4 As Integer = query4.Count

            Dim query5 = From obj In dtAginglist _
                        Where (obj.Field(Of String)("CurrentStatus") = "Open" Or obj.Field(Of String)("CurrentStatus") = "Acknowledge") _
                          And obj.Field(Of Integer)("OpenDays") = 5
            Dim count5 As Integer = query5.Count

            Dim queryOver6 = From obj In dtAginglist _
                        Where (obj.Field(Of String)("CurrentStatus") = "Open" Or obj.Field(Of String)("CurrentStatus") = "Acknowledge") _
                          And obj.Field(Of Integer)("OpenDays") > 5
            Dim countOver6 As Integer = queryOver6.Count

            Dim Result As String
            Result = String.Format("0 Day : {1}{0}1 Day :{2}{0}2 Days :{3}{0}3 Days :{3}{0}4 Days :{5}{0}5 Days :{6}{0}Over 6 Days :{7}{0}", _
                                   vbCrLf, count0, count1, count2, count3, count4, count5)

I try to use code below but it dosen't work with if source is datatable.

Dim orderCounts = From c In customers New With { _
    c.CustomerID, Key .OrderCount = c.Orders.Count() }

score 1 · Accepted Answer · edited May 23 '17 at 12:04

I believe you need to use the AsQueryable() extension to do this:

Dim orderCounts = From c In customers.AsQueryable()
                  New With
                  {
                      c.CustomerID,
                      Key.OrderCount = c.Orders.Count()
                  }

You'll need to add Import System.Data.DataSetExtensions as well.

The reason for this is the DataTable's Rows collection doesn't implement IEnumerable. See LINQ query on a DataTable

Using Linq count function with vb.net datatable

1 Answers1