{"id":4963,"date":"2007-12-13T18:53:00","date_gmt":"2007-12-13T18:53:00","guid":{"rendered":"https:\/\/blogs.msdn.microsoft.com\/vbteam\/2007\/12\/13\/linq-cookbook-recipe-11-desktop-search-statistics-doug-rothaus\/"},"modified":"2024-07-05T14:36:50","modified_gmt":"2024-07-05T21:36:50","slug":"linq-cookbook-recipe-11-desktop-search-statistics-doug-rothaus","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/vbteam\/linq-cookbook-recipe-11-desktop-search-statistics-doug-rothaus\/","title":{"rendered":"LINQ Cookbook, Recipe 11: Desktop Search Statistics (Doug Rothaus)"},"content":{"rendered":"

Ingredients:<\/font><\/span><\/b><\/p>\n


·        <\/span><\/span><\/span>Visual Studio 2008 (Beta2 or Higher)<\/span><\/p>\n

<\/span><\/font><\/p>\n

<\/p>\n

 <\/p>\n

<\/span><\/p>\n

Categories:<\/span><\/b> LINQ to DataSet<\/p>\n

<\/span><\/font><\/p>\n

<\/p>\n

 <\/font><\/p>\n

<\/span><\/p>\n

Introduction:<\/p>\n

<\/font><\/span><\/b><\/p>\n

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<\/b>, Any<\/b>, Average<\/b>, Count<\/b>, LongCount<\/b>, Max<\/b>, Min<\/b>, Sum<\/b>. These functions are documented in the reference topics for the <\/font><\/span>Aggregate Clause<\/font><\/span><\/a>. You can use aggregate functions as part of the Aggregate<\/b> clause for an entire set of data, or as part of the Into<\/b> portion of a Group By<\/b> or Group Join<\/b> clause, where the aggregate function will be applied to each group of data as shown in the following example.<\/p>\n

<\/font><\/span><\/p>\n

Dim<\/span> customersByCountry = From<\/span> cust In<\/span> customers _<\/p>\n

<\/span><\/p>\n

                         <\/span>Order<\/span> By<\/span> cust.City _<\/p>\n

<\/span><\/p>\n

                         <\/span>Group By<\/span> CountryName = cust.Country _<\/p>\n

<\/span><\/p>\n

                         <\/span>Into<\/span> RegionalCustomers = Group<\/span>, Count() _<\/p>\n

<\/span><\/p>\n

                         <\/span>Order<\/span> By<\/span> CountryName<\/span><\/p>\n

<\/p>\n

<\/span> <\/p>\n

In this recipe, you will create a Windows Forms application that queries for items indexed using <\/font><\/span>Windows Desktop Search<\/font><\/span><\/a>. The application returns the t\notal 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<\/b> and Sum<\/b> aggregate functions.<\/p>\n

<\/font><\/span><\/p>\n

Instructions:<\/font><\/span><\/b><\/p>\n

<\/p>\n

<\/font><\/span><\/b> <\/p>\n

·        <\/span><\/span><\/span>Create a Windows Forms Application.<\/p>\n

<\/font><\/span><\/p>\n

·        <\/span><\/span><\/span>From the Toolbox<\/span><\/b>, drag a ListBox<\/b> control, and a DataGridView<\/b> control onto the form. Resize the form and controls as needed.<\/p>\n

<\/font><\/span><\/p>\n

·        <\/span><\/span><\/span>Select the ListBox<\/b> control. In the Properties<\/span><\/b> page, locate the Items<\/b> property and click the ellipsis (…) button to add items to the ListBox<\/b>. Add the following three items and click OK<\/span><\/b>:<\/p>\n

<\/font><\/span><\/p>\n

o   <\/span><\/span><\/span>Document<\/p>\n

<\/font><\/span><\/p>\n

o   <\/span><\/span><\/span>E-mail<\/p>\n

<\/font><\/span><\/p>\n

o   <\/span><\/span><\/span>Image<\/p>\n

<\/font><\/span><\/p>\n

·        <\/span><\/span><\/span>Double-click the ListBox<\/b> control to edit the SelectedIndexChanged<\/b> event and add the following code:<\/p>\n

<\/font><\/span><\/p>\n

‘ Connect to the Desktop Search OLEDB provider. Return a list of items and the size<\/p>\n

<\/span><\/p>\n

‘ of each item where the item type contains the search string selected from the ListBox.<\/p>\n

<\/span><\/p>\n

Dim<\/span> conn As<\/span> New<\/span> OleDbConnection(“Provider=Search.CollatorDSO.1;”<\/span> & _<\/p>\n

<\/span><\/p>\n

                                <\/span>“Persist Security Info=False;”<\/span> & _<\/p>\n

<\/span><\/p>\n

                                <\/span>“Extended Properties=’Application=Windows'”<\/span>)<\/p>\n

<\/span><\/p>\n

Dim<\/span> cmd As<\/span> New<\/span> OleDbCommand(“SELECT System.ItemTypeText, System.Size “<\/span> & _<\/p>\n

<\/span><\/p>\n

                            <\/span>“FROM SystemIndex WHERE “<\/span> & _<\/p>\n

<\/span><\/p>\n

                            <\/span>“CONTAINS(System.ItemTypeText, ‘”””<\/span> & ListBox1.SelectedItem & “””‘)”<\/span>, conn)<\/p>\n

<\/span><\/p>\n

Dim<\/span> adapter As<\/span> New<\/span> OleDbDataAdapter(cmd)<\/p>\n

<\/span><\/p>\n

Dim<\/span> ds As<\/span> New<\/span> DataSet<\/p>\n

<\/span><\/p>\n

adapter.Fill(ds, “SearchResults”<\/span>)<\/p>\n

<\/span><\/p>\n

<\/p>\n

 <\/p>\n

<\/span><\/p>\n

‘ Group the results from the Desktop Search. Count the number of items in each <\/p>\n

<\/span><\/p>\n

‘ group and sum the size of the items in each group.<\/p>\n

<\/span><\/p>\n

Dim<\/span> query = From<\/span> row In<\/span> ds.Tables(“SearchResults”<\/span>) _<\/p>\n

<\/span><\/p>\n

            <\/span>Let<\/span> ItemTypeText = row(“System.ItemTypeText”<\/span>), _<\/p>\n

<\/span><\/p>\n

         <\/span>       <\/span>Size = CInt<\/span>(row(“System.Size”<\/span>)) _<\/p>\n

<\/span><\/p>\n

            <\/span>Group<\/span> By<\/span> ItemTypeText Into<\/span> TotalItems = Count(), Bytes = Sum(Size) _<\/p>\n

<\/span><\/p>\n

            <\/span>Select<\/span> Type = ItemTypeText, Count = TotalItems, _<\/p>\n

<\/span><\/p>\n

                   <\/span><\/span>Size = (Bytes \/ 1000).ToString(“#,###”<\/span>) _<\/p>\n

<\/span><\/p>\n

            <\/span>Order<\/span> By<\/span> Type<\/p>\n

<\/span><\/p>\n

<\/p>\n

 <\/p>\n

<\/span><\/p>\n

‘ Display the grouped results.<\/p>\n

<\/span><\/p>\n

DataGridView1.DataSource = query.ToList()<\/span><\/p>\n

<\/p>\n

<\/span><\/span> <\/p>\n

Press F5<\/b> to see the<\/span> code run. Click on the different search terms in the ListBox<\/b> to see the grouped results including the count of each item and the size (in kilobytes) of each item group.<\/font><\/p>\n

\n

 <\/font><\/p>\n<\/p>\n<\/h2>\n","protected":false},"excerpt":{"rendered":"

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 […]<\/p>\n","protected":false},"author":260,"featured_media":8818,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[192,195],"tags":[59,93,94,117,166],"class_list":["post-4963","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-featured","category-visual-basic","tag-doug-rothaus","tag-linq-cookbook","tag-linqvb9","tag-orcas","tag-vb2008"],"acf":[],"blog_post_summary":"

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 […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/posts\/4963","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/users\/260"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/comments?post=4963"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/posts\/4963\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/media\/8818"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/media?parent=4963"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/categories?post=4963"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/tags?post=4963"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}