Tuesday, November 23, 2010

LINQ Group By Examples

These are 2 samples of  Group By Examples, one is very simple one but not simplest, second one is little bit intermediate. 

First creating a class to use in the given example

   1: Public Class TestRecord
   2:     Property ID As String
   3:     Property Value As String
   4:     Property RecordDate As Date
   5:  
   6:     Sub New()
   7:  
   8:     End Sub
   9:  
  10:     Sub New(ByVal id As String, ByVal value As String, ByVal recDate As Date)
  11:         Me.ID = id
  12:         Me.Value = value
  13:         Me.RecordDate = recDate
  14:     End Sub
  15: End Class

 

Now, let say use a button and Gridview to present the data on a page.

   1: Protected Sub Button4_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button4.Click
   2:         Dim showSimpleGroup As Boolean = False
   3:  
   4:         Dim lstItems As New List(Of TestRecord)
   5:         lstItems.Add(New TestRecord("1", "100", Now))
   6:         lstItems.Add(New TestRecord("2", "100", Date.MinValue))
   7:         lstItems.Add(New TestRecord("3", "100", #2/2/1980#))
   8:         lstItems.Add(New TestRecord("4", "200", Date.MinValue))
   9:         lstItems.Add(New TestRecord("5", "200", #1/1/2010#))
  10:         lstItems.Add(New TestRecord("6", "300", Now))
  11:  
  12:         If showSimpleGroup Then
  13:             Dim recList = From r In lstItems Group By r.Value Into g = Group Select New With {g, .ID = g.Max(Function(p) p.ID), .Value = g.Max(Function(p) p.Value)}
  14:             Me.gvwTest.DataSource = recList
  15:             Me.gvwTest.DataBind()
  16:         Else
  17:             Dim tempList = From d As TestRecord In lstItems Group By d.Value Into g = Group
  18:                             Select New With {g, .ID = g.Max(Function(d) d.ID), .RecordDate = g.Max(Function(d) IIf(d.RecordDate = Date.MinValue, Now, d.RecordDate))}
  19:  
  20:             If tempList IsNot Nothing AndAlso tempList.Count > 0 Then
  21:                 Dim recList = From r As TestRecord In lstItems Join t In tempList On t.ID Equals r.ID Select r
  22:                 Me.gvwTest.DataSource = recList
  23:                 Me.gvwTest.DataBind()
  24:             End If
  25:         End If
  26:     End Sub

 

The first grouping will display the distinct value excluding Record Date , second one will use the record’s max date to display the record the distinct value.

No comments: