LINQ to summarize the data

[StoneCodeMonkey]

I have a Sytem.Data.Datatable with the following columns:

DTS Date
AREA String
STATUS String

I would like to use LINQ to summarize the data, for instance the following SQL would produce the result I desire.

SELECT
AREA, STATUS, COUNT(*)
FROM MYTABLE
WHERE DTS BETWEEN '1/JAN/2011'AND '31/JAN/2011'
GROUP BY
AREA, STATUS
ORDER BY AREA, STATUS

Any suggestions would be appreciated.

If there are alternatives to LINQ I would also give that a try.


Regards,

 

[StoneCodeMonkey]

This works:

Public Function GetSummaryByLine(ByVal DbKey As String, ByVal StartTime As Date, ByVal EndTime As Date) As Data.DataTable
'Raw is a Datatable
If Not Raw Is Nothing Then
Dim query = From row In Raw
Where row.Field(Of DateTime)("DTS") >= StartTime And row.Field(Of DateTime)("DTS") < EndTime _
And row.Field(Of String)("ASSEMBLY_LINE") = DbKey
Group row By AssyLine = row.Field(Of String)("ASSEMBLY_LINE") Into Ran = Count(), Completed = Sum(row.Field(Of Decimal)("VALS"))
Select AssyLine, Ran, Completed

Dim boundTable As New Data.DataTable
boundTable.Columns.Add("RAN", GetType(Integer))
boundTable.Columns.Add("COMPLETED", GetType(Integer))
boundTable.Columns.Add("REJECTED", GetType(Integer))
boundTable.Columns.Add("YIELD_RATE", GetType(Decimal))
boundTable.Columns.Add("DEFECT_RATE", GetType(Decimal))

For Each grp In query
Dim dr As Data.DataRow = boundTable.Rows.Add
dr(0) = grp.Ran 'Total Ran
dr(1) = grp.Completed 'Total Completed Good
dr(2) = grp.Ran - grp.Completed 'Total Rejected
If Not grp.Completed = 0 Then 'Yield
dr(3) = dr(1) / dr(0)
Else
dr(3) = 0
End If
dr(4) = 1 - dr(3)
Next
Return boundTable
Else
Return Nothing
End If
End Function

This is the first time I have tried LINQ. I believe I may just like it. My original process was making 8 calls to the database and taking nearly 3 minutes to summarize the data. With LINQ, I make one call to the database and the process completes in under 5 seconds. Woot! Woot!