Ingredients:

·        Visual Studio 2008 (Beta2 or Higher)

 

Categories: LINQ to DataSet

 

Introduction:

You can use aggregate functions in LINQ queries to perform calculations on sets of data. Visual Basic includes a set of standard aggregate functions for LINQ queries: All, Any, Average, Count, LongCount, Max, Min, Sum. These functions are documented in the reference topics for the Aggregate Clause. You can use aggregate functions as part of the Aggregate clause for an entire set of data, or as part of the Into portion of a Group By or Group Join clause, where the aggregate function will be applied to each group of data as shown in the following example.

Dim customersByCountry = From cust In customers _

                         Order By cust.City _

                         Group By CountryName = cust.Country _

                         Into RegionalCustomers = Group, Count() _

                         Order By CountryName

 

In this recipe, you will create a Windows Forms application that queries for items indexed using Windows Desktop Search. The application returns the t otal count and total kilobytes for items that are documents, e-mails, or images. The application uses the OLEDB provider for Windows Desktop Search to retrieve item information and LINQ to DataSet to group the item information and apply the Count and Sum aggregate functions.

Instructions:

 

·        Create a Windows Forms Application.

·        From the Toolbox, drag a ListBox control, and a DataGridView control onto the form. Resize the form and controls as needed.

·        Select the ListBox control. In the Properties page, locate the Items property and click the ellipsis (…) button to add items to the ListBox. Add the following three items and click OK:

o   Document

o   E-mail

o   Image

·        Double-click the ListBox control to edit the SelectedIndexChanged event and add the following code:

‘ Connect to the Desktop Search OLEDB provider. Return a list of items and the size

‘ of each item where the item type contains the search string selected from the ListBox.

Dim conn As New OleDbConnection(“Provider=Search.CollatorDSO.1;” & _

                                “Persist Security Info=False;” & _

                                “Extended Properties=’Application=Windows'”)

Dim cmd As New OleDbCommand(“SELECT System.ItemTypeText, System.Size “ & _

                            “FROM SystemIndex WHERE “ & _

                            “CONTAINS(System.ItemTypeText, ‘””” & ListBox1.SelectedItem & “””‘)”, conn)

Dim adapter As New OleDbDataAdapter(cmd)

Dim ds As New DataSet

adapter.Fill(ds, “SearchResults”)

 

‘ Group the results from the Desktop Search. Count the number of items in each

‘ group and sum the size of the items in each group.

Dim query = From row In ds.Tables(“SearchResults”) _

            Let ItemTypeText = row(“System.ItemTypeText”), _

                Size = CInt(row(“System.Size”)) _

            Group By ItemTypeText Into TotalItems = Count(), Bytes = Sum(Size) _

            Select Type = ItemTypeText, Count = TotalItems, _

                   Size = (Bytes / 1000).ToString(“#,###”) _

            Order By Type

 

‘ Display the grouped results.

DataGridView1.DataSource = query.ToList()

 

Press F5 to see the code run. Click on the different search terms in the ListBox to see the grouped results including the count of each item and the size (in kilobytes) of each item group.