LINQ To DataSet Samples - Aggregate OperatorsOn This Page |
This sample uses Count to get the number of unique factors of 300.
Public Sub DataSetLinq73()
Dim factorsOf300 = TestDS.Tables("FactorsOF300").AsEnumerable()
Dim uniqueFactors = factorsOf300.Distinct().Count()
Console.WriteLine("There are " & uniqueFactors & " unique factors of 300.")
End Sub
Result:
There are 5 unique factors of 300.
This sample uses Count to get the number of odd ints in the array.
Public Sub DataSetLinq74()
Dim numbers = TestDS.Tables("Numbers").AsEnumerable()
Dim oddNumbers = numbers.Count(Function(n) n("number") Mod 2 = 1)
Console.WriteLine("There are {0} odd numbers in the list.", oddNumbers)
End Sub
Result:
There are 5 odd numbers in the list.
This sample uses Count to return a list of customers and how many orders each has.
Public Sub DataSetLinq76()
Dim customers = TestDS.Tables("Customers").AsEnumerable()
Dim orderCounts = From c In customers _
Select OrderCount = c.GetChildRows("CustomersOrders").Count()
For Each x In orderCounts
Console.WriteLine("OrderCount: {0}", x)
Next
End Sub
Result:
OrderCount: 6
OrderCount: 4
OrderCount: 7
OrderCount: 13
OrderCount: 18
OrderCount: 7
OrderCount: 11
OrderCount: 3
OrderCount: 17
OrderCount: 14
OrderCount: 10
OrderCount: 6
OrderCount: 1
OrderCount: 8
OrderCount: 5
OrderCount: 3
OrderCount: 6
OrderCount: 4
OrderCount: 8
OrderCount: 30
OrderCount: 7
OrderCount: 0
OrderCount: 5
OrderCount: 19
OrderCount: 15
OrderCount: 3
OrderCount: 6
OrderCount: 8
OrderCount: 5
OrderCount: 10
OrderCount: 9
OrderCount: 11
OrderCount: 2
OrderCount: 14
OrderCount: 18
OrderCount: 5
OrderCount: 19
OrderCount: 10
OrderCount: 14
OrderCount: 4
OrderCount: 14
OrderCount: 3
OrderCount: 2
OrderCount: 15
OrderCount: 4
OrderCount: 14
OrderCount: 12
OrderCount: 8
OrderCount: 10
OrderCount: 7
OrderCount: 13
OrderCount: 5
OrderCount: 3
OrderCount: 5
OrderCount: 10
OrderCount: 9
OrderCount: 0
OrderCount: 6
OrderCount: 10
OrderCount: 6
OrderCount: 9
OrderCount: 13
OrderCount: 28
OrderCount: 5
OrderCount: 18
OrderCount: 12
OrderCount: 11
OrderCount: 10
OrderCount: 5
OrderCount: 6
OrderCount: 31
OrderCount: 9
OrderCount: 7
OrderCount: 4
OrderCount: 9
OrderCount: 12
OrderCount: 4
OrderCount: 3
OrderCount: 5
OrderCount: 10
OrderCount: 7
OrderCount: 3
OrderCount: 11
OrderCount: 10
OrderCount: 4
OrderCount: 10
OrderCount: 15
OrderCount: 9
OrderCount: 14
OrderCount: 8
OrderCount: 7
This sample uses Count to return a list of categories and how many products each has.
Public Sub DataSetLinq77()
Dim products = TestDS.Tables("Products").AsEnumerable()
Dim categoryCounts = From p In products _
Group p By Key = p.Field(Of String)("Category") Into Count() _
Select Category = Key, ProductCount = Count
ObjectDumper.Write(categoryCounts)
End Sub
Result:
Category=Beverages ProductCount=12
Category=Condiments ProductCount=12
Category=Produce ProductCount=5
Category=Meat/Poultry ProductCount=6
Category=Seafood ProductCount=12
Category=Dairy Products ProductCount=10
Category=Confections ProductCount=13
Category=Grains/Cereals ProductCount=7
Gets the count as a long
Public Sub DataSetLinq103()
Dim products = TestDS.Tables("Products").AsEnumerable()
Dim numberOfProducts = products.LongCount()
Console.WriteLine("There are {0} products", numberOfProducts)
End Sub
Result:
There are 77 products
This sample uses Count to get the number of odd ints in the array as a long
Public Sub DataSetLinq104()
Dim numbers = TestDS.Tables("Numbers").AsEnumerable()
Dim oddNumbers = numbers.LongCount(Function(n) n("number") Mod 2 = 1)
Console.WriteLine("There are {0} odd numbers in the list.", oddNumbers)
End Sub
Result:
There are 5 odd numbers in the list.
This sample uses Sum to get the total of the numbers in an DataTable.
Public Sub DataSetLinq78()
Dim numbers = TestDS.Tables("Numbers").AsEnumerable()
Dim numSum = (From n In numbers _
Select n.Field(Of Integer)("number")).Sum()
Console.WriteLine("The sum of the numbers is " & numSum)
End Sub
Result:
The sum of the numbers is 45
This sample uses Sum to get the total number of characters of all words in the array.
Public Sub DataSetLinq79()
Dim words = TestDS.Tables("Words").AsEnumerable()
Dim totalChars = words.Sum(Function(w) CStr(w("word")).Length)
Console.WriteLine("There are a total of {0} characters in these words.", totalChars)
End Sub
Result:
There are a total of 20 characters in these words.
This sample uses Sum to get the total units in stock for each product category.
Public Sub DataSetLinq80()
Dim products = TestDS.Tables("Products").AsEnumerable()
Dim categories = From p In products _
Group p By Key = p.Field(Of String)("Category") Into Group _
Select Category = Key, _
TotalUnitsInStock = Group.Sum(Function(p) p("UnitsInStock"))
ObjectDumper.Write(categories)
End Sub
Result:
Category=Beverages TotalUnitsInStock=559
Category=Condiments TotalUnitsInStock=507
Category=Produce TotalUnitsInStock=100
Category=Meat/Poultry TotalUnitsInStock=165
Category=Seafood TotalUnitsInStock=701
Category=Dairy Products TotalUnitsInStock=393
Category=Confections TotalUnitsInStock=386
Category=Grains/Cereals TotalUnitsInStock=308
This sample uses Min to get the lowest number in an DataTable.
Public Sub DataSetLinq81()
Dim numbers = TestDS.Tables("Numbers").AsEnumerable()
Dim minNum = (From n In numbers Select CInt(n("number"))).Min()
Console.WriteLine("The minimum number is " & minNum & ".")
End Sub
Result:
The minimum number is 0.
This sample uses Min to get the cheapest price among each category's products.
Public Sub DataSetLinq83()
Dim products = TestDS.Tables("Products").AsEnumerable()
Dim categories = From p In products _
Group p By Key = p.Field(Of String)("Category") Into Group _
Select Category = Key, CheapestPrice = Group.Min(Function(p) p("UnitPrice"))
ObjectDumper.Write(categories)
End Sub
Result:
Category=Beverages CheapestPrice=4
Category=Condiments CheapestPrice=10
Category=Produce CheapestPrice=10
Category=Meat/Poultry CheapestPrice=7
Category=Seafood CheapestPrice=6
Category=Dairy Products CheapestPrice=2
Category=Confections CheapestPrice=9
Category=Grains/Cereals CheapestPrice=7
This sample uses Min to get the products with the cheapest price in each category.
Public Sub DataSetLinq84()
Dim products = TestDS.Tables("Products").AsEnumerable()
Dim categories = From p In products _
Group p By Category = p("Category") Into Group _
Let minPrice = Group.Min(Function(p) p("UnitPrice")) _
Select Category, _
CheapestProducts = Group.Where(Function(p) p("UnitPrice") = minPrice)
For Each g In categories
Console.WriteLine("Category: {0}", g.Category)
Console.WriteLine("CheapestProducts:")
For Each w In g.CheapestProducts
Console.WriteLine(vbTab & w("ProductName"))
Next
Next
End Sub
Result:
Category: Beverages
CheapestProducts:
Category: Condiments
CheapestProducts:
Aniseed Syrup
Category: Produce
CheapestProducts:
Longlife Tofu
Category: Meat/Poultry
CheapestProducts:
Category: Seafood
CheapestProducts:
Konbu
Category: Dairy Products
CheapestProducts:
Category: Confections
CheapestProducts:
Category: Grains/Cereals
CheapestProducts:
Filo Mix
This sample uses Max to get the highest number in an DataTable.
Public Sub DataSetLinq85()
Dim numbers = TestDS.Tables("Numbers").AsEnumerable()
Dim maxNum = (From n In numbers _
Select n.Field(Of Integer)("number")).Max()
Console.WriteLine("The maximum number is " & maxNum & ".")
End Sub
Result:
The maximum number is 9.
This sample uses Max to get the length of the longest word in an array.
Public Sub DataSetLinq86()
Dim words = TestDS.Tables("Words").AsEnumerable()
Dim longestLength = words.Max(Function(w) w("word").Length)
Console.WriteLine("The longest word is {0} characters long.", longestLength)
End Sub
Result:
The longest word is 9 characters long.
This sample uses Max to get the most expensive price among each category's products.
Public Sub DataSetLinq87()
Dim products = TestDS.Tables("Products").AsEnumerable()
Dim categories = From p In products _
Group p By Category = p.Field(Of String)("Category") Into Group _
Select Category, MostExpensivePrice = Group.Max(Function(p) p("UnitPrice"))
ObjectDumper.Write(categories)
End Sub
Result:
Category=Beverages MostExpensivePrice=264
Category=Condiments MostExpensivePrice=44
Category=Produce MostExpensivePrice=53
Category=Meat/Poultry MostExpensivePrice=124
Category=Seafood MostExpensivePrice=62
Category=Dairy Products MostExpensivePrice=55
Category=Confections MostExpensivePrice=81
Category=Grains/Cereals MostExpensivePrice=38
This sample uses Max to get the products with the most expensive price in each category.
Public Sub DataSetLinq88()
Dim products = TestDS.Tables("Products").AsEnumerable()
Dim categories = From p In products _
Group p By Category = p("Category") Into Group _
Let maxPrice = Group.Max(Function(p) p("UnitPrice")) _
Select Category, MostExpensiveProducts = Group.Where(Function(p) p("UnitPrice") = maxPrice)
For Each g In categories
Console.WriteLine("Category: {0}", g.Category)
Console.WriteLine("MostExpensiveProducts:")
For Each w In g.MostExpensiveProducts
Console.WriteLine(vbTab & w("ProductName"))
Next
Next
End Sub
Result:Category: Beverages
MostExpensiveProducts:
Category: Condiments
MostExpensiveProducts:
Category: Produce
MostExpensiveProducts:
Manjimup Dried Apples
Category: Meat/Poultry
MostExpensiveProducts:
Category: Seafood
MostExpensiveProducts:
Category: Dairy Products
MostExpensiveProducts:
Raclette Courdavault
Category: Confections
MostExpensiveProducts:
Sir Rodney's Marmalade
Category: Grains/Cereals
MostExpensiveProducts:
Gnocchi di nonna Alice
This sample uses Average to get the average of all numbers in an DataTable.
Public Sub DataSetLinq89()
Dim numbers = TestDS.Tables("Numbers").AsEnumerable()
Dim averageNum = (From n In numbers _
Select n.Field(Of Integer)("number")).Average()
Console.WriteLine("The average number is " & averageNum & ".")
End Sub
Result:The average number is 4.5.
This sample uses Average to get the average length of the words in the array.
Public Sub DataSetLinq90()
Dim words = TestDS.Tables("Words").AsEnumerable()
Dim averageLength = words.Average(Function(w) w("word").Length)
Console.WriteLine("The average word length is {0} characters.", averageLength)
End Sub
Result:
The average word length is 6.66666666666667 characters.
This sample uses Average to get the average price of each category's products.
Public Sub DataSetLinq91()
Dim products = TestDS.Tables("Products").AsEnumerable()
Dim categories = From p In products _
Group p By Category = p.Field(Of String)("Category") Into Group _
Select Category, AveragePrice = Group.Average(Function(p) p("UnitPrice"))
ObjectDumper.Write(categories)
End Sub
Result:
AveragePrice=38 Category=Beverages
AveragePrice=23 Category=Condiments
AveragePrice=32.4 Category=Produce
AveragePrice=54 Category=Meat/Poultry
AveragePrice=20.6666666666667 Category=Seafood
AveragePrice=28.7 Category=Dairy Products
AveragePrice=25.0769230769231 Category=Confections
AveragePrice=20.2857142857143 Category=Grains/Cereals
This sample uses Aggregate to create a running account balance that subtracts each withdrawal from the initial balance of 100, as long as the balance never drops below 0.
Public Sub DataSetLinq93()
Dim attemptedWithdrawals = TestDS.Tables("AttemptedWithdrawals").AsEnumerable()
Dim startBalance = 100.0
Dim endBalance = attemptedWithdrawals.Aggregate(startBalance, Function(balance, nextWithdrawal) _
If(nextWithdrawal("withdrawal") <= balance, _
balance - nextWithdrawal("withdrawal"), _
balance))
Console.WriteLine("Ending balance: {0}", endBalance)
End Sub
Result:
Ending balance: 20
