SQL Group by using LINQ

Target audience: SQL programmers
Used tools: SQL,XPath
What’s the purpose: read parameters for SQL query from XML variable

The problem is the same, like in post “Read parameters for SQL store procedure from XML variable” but this time the resolving must be made with LINQ. In the beginning, let’s the list for checking is continuous

Dim ranges As List(Of Integer) = New List(Of Integer) From {0, 49, 99, 149, 199, 249}

Here 0 means interval from 0 to 49,49 means interval from 49 to 99 and so on.Now we can make windows project in Visual Studio with one form Form1 with command button Button1 and RichTextBox1 for writing the result.

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

Dim tmpDt = New DataTable()
 
'Çlear all data controls
RichTextBox1.Text = ""
RichTextBox2.Text = ""
Label1.Text = ""
tmpDt.Clear()
DataGridView1.DataSource = tmpDt
DataGridView1.DataSource = Nothing
 
' Create a list of Regional Offices
Dim regOffices = GetRegOffices()

' Display the names in the list
DisplayList(regOffices)
'Sample with DataTable !!!
tmpDt.Columns.Add("FromTo", GetType(Integer))
tmpDt.Columns.Add("totSum ", GetType(String))
 
Dim dr As DataRow
dr = tmpDt.NewRow
dr("FromTo") = 25
dr("totSum ") = "2.54"
tmpDt.Rows.Add(dr)
 
dr = tmpDt.NewRow
dr("FromTo") = 115
dr("totSum ") = "3.55"
tmpDt.Rows.Add(dr)
 
dr = tmpDt.NewRow
 dr("FromTo") = 124
dr("totSum ") = "8.45"
tmpDt.Rows.Add(dr)
 
dr = tmpDt.NewRow
dr("FromTo") = 165
dr("totSum") = "1.76"
tmpDt.Rows.Add(dr)
 
dr = tmpDt.NewRow
dr("FromTo") = 201
dr("totSum ") = "10.48"
tmpDt.Rows.Add(dr)

dr = tmpDt.NewRow
dr("FromTo") = 249
dr("totSum ") = "3.52"
tmpDt.Rows.Add(dr)
 
Dim ranges As List(Of Integer) = New List(Of Integer) From {0, 49, 99, 149, 199, 249} 
Dim Results = ranges.Select(Function(r) New With {Key .FromTo = r, _
     totCount = tmpDt.AsEnumerable.Where(Function(x) x.Field(Of Integer)("FromTo") >= r _
     And x.Field(Of Integer)("FromTo") < ranges.Where(Function(r2) r2 > r).FirstOrDefault).Count(), _
     totSum = tmpDt.AsEnumerable.Where(Function(x) x.Field(Of Integer)("FromTo") >= r _
     And x.Field(Of Integer)("FromTo") < ranges.Where(Function(r2) r2 > r) _
     .FirstOrDefault).Sum( Function(x) Val(x.Field(Of String)("totSum")))}).ToList
 
 ' Loop through list with a for-to loop.
Dim i As Integer
Dim upLim As String = ""
For i = 0 To Results.Count - 1
   If (i = Results.Count - 1) Then
       upLim = ">"
   Else
       upLim = Results.Item(i + 1).FromTo.ToString
   End If

   RichTextBox1.AppendText(Results.Item(i).FromTo.ToString & " - " & upLim & " = " & _ 
   Results.Item(i).totCount.ToString & "  - " & Results.Item(i).totSum.ToString & " $" & vbCrLf)
Next i

End Sub

But, in the real life, list may be not continuous – something like this:
This is a list of Regional Offices (List of Items):

List(Of regOffice) From
        {
          New regOffice(1, "Western", 1000, 3999),
          New regOffice(2, "South-Central", 4000, 4999),
          New regOffice(4, " North-Central", 5000, 5999),
          New regOffice(3, "South-Eastern”, 6000, 6299),
          New regOffice(2, " South-Central", 6300, 6999),
          New regOffice(4, "North-Central", 7000, 7699),
          New regOffice(5, "North-Eastern", 7700, 7999),
          New regOffice(3, " South-Eastern ", 8000, 8999),
          New regOffice(5, " North-Eastern ", 9000, 9999)
        }

Here item regOffice is a class with the following structure:

Public Class regOffice
    Public Property ID As Integer
    Public Property Name As String
    Public Property fromPK As Integer
    Public Property toPK As Integer
    
    Public Sub New(ByVal porNmr As Integer,
                   ByVal nameRegOffice As String,
                   ByVal otPK As Integer,
                   ByVal doPK As Integer)
        ID = porNmr
        Name = nameRegOffice
        fromPK = otPK
        toPK = doPK
    End Sub
End Class

It has 4 properties:

  1. ID is serial number
  2. Name is the name of the regional Office
  3. fromPK is down limit of interval of postal codes for this region
  4. toPK is upper limit of interval of postal codes for this region
dbTPBPsbAmmount
20001.11
20001.32
10003.21
97002.00
97000.10
10000.10
10000.10
10000.10
10000.10
20000.10
90002.00
10002.00
10003.21
10003.21
97001.32
97001.32
97002.18
97001.23
97001.00
10002.00
97002.00

The result from linq gives counting in ranges

NNameRUCountTotalSum
1Western1317.67
5North-Eastern913.15

Now we add RichTextBox2 and DataGridView1 controls on Form1 and add to the project DataContext with name grabPmntDataClasses1DataContext – this for connection between class regOffice and MS SQL database table grabPmnt

This is the code:

Dim db As New grabPmntDataClasses1DataContext
 
Dim rslrRU = regOffices.Select(Function(r) New With {Key .ruID = r.ID, _ 
    .ruName = r.Name, .ruFromPK = r.fromPK, .ruToPK = r.toPK, _
    .Count = db.grabPmnts.AsEnumerable.Where( _ 
    Function(x) Convert.ToInt32(x.dbTPBP) >= r.fromPK _
    And Convert.ToInt32(x.dbTPBP) < regOffices.Where(Function(r2) _ 
    r2.toPK > r.fromPK).FirstOrDefault.toPK).Count(), _
    .dbAmount = db.grabPmnts.AsEnumerable.Where( _ 
    Function(x) Convert.ToInt32(x.dbTPBP) >= r.fromPK _
    And Convert.ToInt32(x.dbTPBP) < regOffices.Where(Function(r2) r2.toPK >  _ 
    r.fromPK).FirstOrDefault.toPK).Sum(Function(x) x.dbAmount)}).ToList()
 
' Loop through list with a for-to loop.
Dim j As Integer
Dim toPK As String = ""
For j = 0 To rslrRU.Count - 1
  RichTextBox2.AppendText(rslrRU.Item(j).ruID.ToString & " - " &  _
  rslrRU.Item(j).ruName & " = " & _ rslrRU.Item(j).Count.ToString & _
  "  - " & rslrRU.Item(j).dbAmount.ToString & " $" & vbCrLf)
Next j
 
Dim groupQuery = From v In rslrRU _
     Where (v.Count > 0 And v.dbAmount > 0) _
     Order By v.ruID _
     Group v By v.ruID, v.ruName, v.dbAmount, v.Count Into regOff = Group _
     Select [N] = ruID, [nameRU] = ruName, [Count] = Count, [TotalSum] = dbAmount
 
     DataGridView1.DataSource = groupQuery.ToList
 
For Each group In groupQuery
  Label1.Text &= "TOTAL for RU " & group.[N] & "- " & group.[nameRU] & " " &  _ 
  group.[Count] & "  " &  _ group.[TotalSum] & " $" & vbCrLf
Next

And this is the final result:

Published By
Categorized as LINQ