LINQ To DataSet Samples - Restriction OperatorsOn This Page |
This sample uses where to find all DataRows of an DataTable less than 5.
Public Sub DataSetLinq1()
Dim numbers = TestDS.Tables("Numbers").AsEnumerable
Dim lowNums = From r In numbers _
Where r("number") < 5 _
Select r
Console.WriteLine("Numbers < 5:")
For Each x In lowNums
Console.WriteLine(x("number"))
Next
End Sub
Result:
Numbers < 5:
4
3
0
2
1
-1
This sample uses where to find all DataRows that are out of stock.
Public Sub DataSetLinq2()
Dim products = TestDS.Tables("Products").AsEnumerable()
Dim soldOutProducts = From p In products _
Where p("UnitsInStock") = 0 _
Select p
Console.WriteLine("Sold out products:")
For Each p In soldOutProducts
Console.WriteLine(p("ProductName") + " is sold out!")
Next
End Sub
Result:
Sold out products:
Chef Anton's Gumbo Mix is sold out!
Alice Mutton is sold out!
Thüringer Rostbratwurst is sold out!
Gorgonzola Telino is sold out!
Perth Pasties is sold out!
This sample uses where to find all DataRows that are in stock and cost more than 3.00 per un
Public Sub DataSetLinq3()
Dim products = TestDS.Tables("Products").AsEnumerable()
Dim expensiveInStockProducts = _
From p In products _
Where p("UnitsInStock") > 0 AndAlso p("UnitPrice") > 3.0# _
Select p
Console.WriteLine("In-stock products that cost more than 3.00:")
For Each product In expensiveInStockProducts
Console.WriteLine(product("ProductName") & "is in stock and costs more than 3.00.")
Next
End Sub
Result:
In-stock products that cost more than 3.00:
Chaiis in stock and costs more than 3.00.
Changis in stock and costs more than 3.00.
Aniseed Syrupis in stock and costs more than 3.00.
Chef Anton's Cajun Seasoningis in stock and costs more than 3.00.
Grandma's Boysenberry Spreadis in stock and costs more than 3.00.
Uncle Bob's Organic Dried Pearsis in stock and costs more than 3.00.
Northwoods Cranberry Sauceis in stock and costs more than 3.00.
Mishi Kobe Nikuis in stock and costs more than 3.00.
Ikurais in stock and costs more than 3.00.
Queso Cabralesis in stock and costs more than 3.00.
Queso Manchego La Pastorais in stock and costs more than 3.00.
Konbuis in stock and costs more than 3.00.
Tofuis in stock and costs more than 3.00.
Genen Shouyuis in stock and costs more than 3.00.
Pavlovais in stock and costs more than 3.00.
Carnarvon Tigersis in stock and costs more than 3.00.
Teatime Chocolate Biscuitsis in stock and costs more than 3.00.
Sir Rodney's Marmaladeis in stock and costs more than 3.00.
Sir Rodney's Sconesis in stock and costs more than 3.00.
Gustaf's Knäckebrödis in stock and costs more than 3.00.
Tunnbrödis in stock and costs more than 3.00.
Guaraná Fantásticais in stock and costs more than 3.00.
NuNuCa Nuß-Nougat-Cremeis in stock and costs more than 3.00.
Gumbär Gummibärchenis in stock and costs more than 3.00.
Schoggi Schokoladeis in stock and costs more than 3.00.
Rössle Sauerkrautis in stock and costs more than 3.00.
Nord-Ost Matjesheringis in stock and costs more than 3.00.
Mascarpone Fabioliis in stock and costs more than 3.00.
Sasquatch Aleis in stock and costs more than 3.00.
Steeleye Stoutis in stock and costs more than 3.00.
Inlagd Sillis in stock and costs more than 3.00.
Gravad laxis in stock and costs more than 3.00.
Côte de Blayeis in stock and costs more than 3.00.
Chartreuse verteis in stock and costs more than 3.00.
Boston Crab Meatis in stock and costs more than 3.00.
Jack's New England Clam Chowderis in stock and costs more than 3.00.
Singaporean Hokkien Fried Meeis in stock and costs more than 3.00.
Ipoh Coffeeis in stock and costs more than 3.00.
Gula Malaccais in stock and costs more than 3.00.
Rogede sildis in stock and costs more than 3.00.
Spegesildis in stock and costs more than 3.00.
Zaanse koekenis in stock and costs more than 3.00.
Chocoladeis in stock and costs more than 3.00.
Maxilakuis in stock and costs more than 3.00.
Valkoinen suklaais in stock and costs more than 3.00.
Manjimup Dried Applesis in stock and costs more than 3.00.
Filo Mixis in stock and costs more than 3.00.
Tourtièreis in stock and costs more than 3.00.
Pâté chinoisis in stock and costs more than 3.00.
Gnocchi di nonna Aliceis in stock and costs more than 3.00.
Ravioli Angelois in stock and costs more than 3.00.
Escargots de Bourgogneis in stock and costs more than 3.00.
Raclette Courdavaultis in stock and costs more than 3.00.
Camembert Pierrotis in stock and costs more than 3.00.
Sirop d'érableis in stock and costs more than 3.00.
Tarte au sucreis in stock and costs more than 3.00.
Vegie-spreadis in stock and costs more than 3.00.
Wimmers gute Semmelknödelis in stock and costs more than 3.00.
Louisiana Fiery Hot Pepper Sauceis in stock and costs more than 3.00.
Louisiana Hot Spiced Okrais in stock and costs more than 3.00.
Laughing Lumberjack Lageris in stock and costs more than 3.00.
Scottish Longbreadsis in stock and costs more than 3.00.
Gudbrandsdalsostis in stock and costs more than 3.00.
Outback Lageris in stock and costs more than 3.00.
Flotemysostis in stock and costs more than 3.00.
Mozzarella di Giovanniis in stock and costs more than 3.00.
Röd Kaviaris in stock and costs more than 3.00.
Longlife Tofuis in stock and costs more than 3.00.
Rhönbräu Klosterbieris in stock and costs more than 3.00.
Lakkalikööriis in stock and costs more than 3.00.
Original Frankfurter grüne Soßeis in stock and costs more than 3.00.
This sample uses where to find all customers in France and then uses the resulting sequence to drill down into their orders.
Public Sub DataSetLinq4()
Dim customers = TestDS.Tables("Customers").AsEnumerable()
Dim franceCustomers = From c In customers _
Where c("Country") = "France" _
Select c
Console.WriteLine("Customers from France and their orders:")
For Each customer In franceCustomers
Console.WriteLine("Customer {0}: {1}", customer("CustomerID"), customer("CompanyName"))
For Each order In customer.GetChildRows("CustomersOrders")
Console.WriteLine(" Order {0}: {1}", order("OrderID"), order("OrderDate"))
Next
Next
End Sub
Result:
Customers from France and their orders:
Customer BLONP: Blondel père et fils
Order 10265: 7/25/1996 12:00:00 AM
Order 10297: 9/4/1996 12:00:00 AM
Order 10360: 11/22/1996 12:00:00 AM
Order 10436: 2/5/1997 12:00:00 AM
Order 10449: 2/18/1997 12:00:00 AM
Order 10559: 6/5/1997 12:00:00 AM
Order 10566: 6/12/1997 12:00:00 AM
Order 10584: 6/30/1997 12:00:00 AM
Order 10628: 8/12/1997 12:00:00 AM
Order 10679: 9/23/1997 12:00:00 AM
Order 10826: 1/12/1998 12:00:00 AM
Customer BONAP: Bon app'
Order 10331: 10/16/1996 12:00:00 AM
Order 10340: 10/29/1996 12:00:00 AM
Order 10362: 11/25/1996 12:00:00 AM
Order 10470: 3/11/1997 12:00:00 AM
Order 10511: 4/18/1997 12:00:00 AM
Order 10525: 5/2/1997 12:00:00 AM
Order 10663: 9/10/1997 12:00:00 AM
Order 10715: 10/23/1997 12:00:00 AM
Order 10730: 11/5/1997 12:00:00 AM
Order 10732: 11/6/1997 12:00:00 AM
Order 10755: 11/26/1997 12:00:00 AM
Order 10827: 1/12/1998 12:00:00 AM
Order 10871: 2/5/1998 12:00:00 AM
Order 10876: 2/9/1998 12:00:00 AM
Order 10932: 3/6/1998 12:00:00 AM
Order 10940: 3/11/1998 12:00:00 AM
Order 11076: 5/6/1998 12:00:00 AM
Customer DUMON: Du monde entier
Order 10311: 9/20/1996 12:00:00 AM
Order 10609: 7/24/1997 12:00:00 AM
Order 10683: 9/26/1997 12:00:00 AM
Order 10890: 2/16/1998 12:00:00 AM
Customer FOLIG: Folies gourmandes
Order 10408: 1/8/1997 12:00:00 AM
Order 10480: 3/20/1997 12:00:00 AM
Order 10634: 8/15/1997 12:00:00 AM
Order 10763: 12/3/1997 12:00:00 AM
Order 10789: 12/22/1997 12:00:00 AM
Customer FRANR: France restauration
Order 10671: 9/17/1997 12:00:00 AM
Order 10860: 1/29/1998 12:00:00 AM
Order 10971: 3/24/1998 12:00:00 AM
Customer LACOR: La corne d'abondance
Order 10858: 1/29/1998 12:00:00 AM
Order 10927: 3/5/1998 12:00:00 AM
Order 10972: 3/24/1998 12:00:00 AM
Order 10973: 3/24/1998 12:00:00 AM
Customer LAMAI: La maison d'Asie
Order 10350: 11/11/1996 12:00:00 AM
Order 10358: 11/20/1996 12:00:00 AM
Order 10371: 12/3/1996 12:00:00 AM
Order 10413: 1/14/1997 12:00:00 AM
Order 10425: 1/24/1997 12:00:00 AM
Order 10454: 2/21/1997 12:00:00 AM
Order 10493: 4/2/1997 12:00:00 AM
Order 10500: 4/9/1997 12:00:00 AM
Order 10610: 7/25/1997 12:00:00 AM
Order 10631: 8/14/1997 12:00:00 AM
Order 10787: 12/19/1997 12:00:00 AM
Order 10832: 1/14/1998 12:00:00 AM
Order 10923: 3/3/1998 12:00:00 AM
Order 11051: 4/27/1998 12:00:00 AM
Customer PARIS: Paris spécialités
Customer SPECD: Spécialités du monde
Order 10738: 11/12/1997 12:00:00 AM
Order 10907: 2/25/1998 12:00:00 AM
Order 10964: 3/20/1998 12:00:00 AM
Order 11043: 4/22/1998 12:00:00 AM
Customer VICTE: Victuailles en stock
Order 10251: 7/8/1996 12:00:00 AM
Order 10334: 10/21/1996 12:00:00 AM
Order 10450: 2/19/1997 12:00:00 AM
Order 10459: 2/27/1997 12:00:00 AM
Order 10478: 3/18/1997 12:00:00 AM
Order 10546: 5/23/1997 12:00:00 AM
Order 10806: 12/31/1997 12:00:00 AM
Order 10814: 1/5/1998 12:00:00 AM
Order 10843: 1/21/1998 12:00:00 AM
Order 10850: 1/23/1998 12:00:00 AM
Customer VINET: Vins et alcools Chevalier
Order 10274: 8/6/1996 12:00:00 AM
Order 10295: 9/2/1996 12:00:00 AM
Order 10737: 11/11/1997 12:00:00 AM
Order 10739: 11/12/1997 12:00:00 AM
This sample demonstrates an indexed Where clause that returns digits whose name is shorter than their value.
Public Sub DataSetLinq5()
Dim digits As IEnumerable(Of DataRow) = TestDS.Tables("Digits").AsEnumerable()
Dim shortDigits = digits.Where(Function(digit, index) digit.Field(Of String)(0).Length < index)
Console.WriteLine("Short digits:")
For Each d In shortDigits
Console.WriteLine("The word " & d("digit") + " is shorter than its value.")
Next
End Sub
Result:
Short digits:
The word five is shorter than its value.
The word six is shorter than its value.
The word seven is shorter than its value.
The word eight is shorter than its value.
The word nine is shorter than its value.
Turns a seqeunce into a single result
Public Sub DataSetLinq106()
'create a table with a single row
Dim singleRowTable = New DataTable("SingleRowTable")
singleRowTable.Columns.Add("id", GetType(Integer))
singleRowTable.Rows.Add(New Object() {1})
Dim singleRow = singleRowTable.AsEnumerable().Single()
Console.WriteLine(singleRow IsNot Nothing)
End Sub
Result:
True
Returns a single element based on the specified predicate
Public Sub DataSetLinq107()
'create an table with a two rows
Dim table = New DataTable("MyTable")
table.Columns.Add("id", GetType(Integer))
table.Rows.Add(New Object() {1})
table.Rows.Add(New Object() {2})
Dim singleRow = table.AsEnumerable().Single(Function(r) r("id") = 1)
Console.WriteLine(singleRow IsNot Nothing)
End Sub
Result:
True
