LINQ To SQL Samples - Conversion OperatorsOn This Page |
This sample uses ToArray so that the client-side IEnumerable(Of T) implementation of Where is used, instead of the default Query(Of T) implementation which would be converted to SQL and executed on the server. This is necessary because the where clause references a user-defined client-side method, isValidProduct, which cannot be converted to SQL.
Public Sub LinqToSqlConversions01()
Dim q = From p In db.Products.AsEnumerable() _
Where isValidProduct(p) _
Select p
ObjectDumper.Write(q)
End Sub
Private Function isValidProduct(ByVal p As Product) As Boolean
Return (p.ProductName.LastIndexOf("C") = 0)
End Function
Result:
ProductID=1 ProductName=Chai SupplierID=1 CategoryID=1 QuantityPerUnit=10 boxes x 20 bags UnitPrice=777.7700 UnitsInStock=39 UnitsOnOrder=0 ReorderLevel=10 Discontinued=False Order_Details=... Category={ } Supplier={ }
ProductID=2 ProductName=Chang SupplierID=1 CategoryID=1 QuantityPerUnit=24 - 12 oz bottles UnitPrice=19.0000 UnitsInStock=17 UnitsOnOrder=40 ReorderLevel=25 Discontinued=False Order_Details=... Category={ } Supplier={ }
ProductID=5 ProductName=Chef Anton's Gumbo Mix SupplierID=2 CategoryID=2 QuantityPerUnit=36 boxes UnitPrice=21.3500 UnitsInStock=0 UnitsOnOrder=0 ReorderLevel=0 Discontinued=True Order_Details=... Category={ } Supplier={ }
ProductID=18 ProductName=Carnarvon Tigers SupplierID=7 CategoryID=8 QuantityPerUnit=16 kg pkg. UnitPrice=62.5000 UnitsInStock=42 UnitsOnOrder=0 ReorderLevel=0 Discontinued=False Order_Details=... Category={ } Supplier={ }
ProductID=38 ProductName=Côte de Blaye SupplierID=18 CategoryID=1 QuantityPerUnit=12 - 75 cl bottles UnitPrice=263.5000 UnitsInStock=17 UnitsOnOrder=0 ReorderLevel=15 Discontinued=False Order_Details=... Category={ } Supplier={ }
ProductID=39 ProductName=Chartreuse verte SupplierID=18 CategoryID=1 QuantityPerUnit=750 cc per bottle UnitPrice=18.0000 UnitsInStock=69 UnitsOnOrder=0 ReorderLevel=5 Discontinued=False Order_Details=... Category={ } Supplier={ }
ProductID=48 ProductName=Chocolade SupplierID=22 CategoryID=3 QuantityPerUnit=10 pkgs. UnitPrice=12.7500 UnitsInStock=15 UnitsOnOrder=70 ReorderLevel=25 Discontinued=False Order_Details=... Category={ } Supplier={ }
ProductID=60 ProductName=Camembert Pierrot SupplierID=28 CategoryID=4 QuantityPerUnit=15 - 300 g rounds UnitPrice=34.0000 UnitsInStock=19 UnitsOnOrder=0 ReorderLevel=0 Discontinued=False Order_Details=... Category={ } Supplier={ }
This sample uses ToArray to immediately evaluate a query into an array and get the 3rd element.
Public Sub LinqToSqlConversions02()
Dim q = From c In db.Customers _
Where c.City = "London" _
Select c
Dim qArray() As Customer = q.ToArray()
ObjectDumper.Write(qArray(3), 0)
End Sub
Result:
CustomerID=EASTC CompanyName=Eastern Connection ContactName=Ann Devon ContactTitle=Sales Agent Address=35 King George City=London Region=null PostalCode=WX3 6FW Country=UK Phone=(171) 555-0297 Fax=(171) 555-3373 Orders=... CustomerCustomerDemos=...
This sample uses ToList to immediately evaluate a query into a List(Of T).
Public Sub LinqToSqlConversions03()
Dim q = From e In db.Employees _
Where e.HireDate >= #1/1/1994# _
Select e
Dim qList As List(Of Employee) = q.ToList()
ObjectDumper.Write(qList, 0)
End Sub
Result:
EmployeeID=7 LastName=King FirstName=Robert Title=Sales Representative TitleOfCourtesy=Mr. BirthDate=5/29/1960 HireDate=1/2/1994 Address=Edgeham Hollow
Winchester Way City=London Region=null PostalCode=RG1 9SP Country=UK HomePhone=(71) 555-5598 Extension=465 Photo=... Notes=Robert King served in the Peace Corps and traveled extensively before completing his degree in English at the University of Michigan in 1992, the year he joined the company. After completing a course entitled "Selling in Europe," he was transferred to the London office in March 1993. ReportsTo=5 PhotoPath=http://accweb/emmployees/davolio.bmp Orders=... EmployeeTerritories=... Employees=... Employee={ }
EmployeeID=8 LastName=Callahan FirstName=Laura Title=Inside Sales Coordinator TitleOfCourtesy=Ms. BirthDate=1/9/1958 HireDate=3/5/1994 Address=4726 - 11th Ave. N.E. City=Seattle Region=WA PostalCode=98105 Country=USA HomePhone=(206) 555-1189 Extension=2344 Photo=... Notes=Laura received a BA in psychology from the University of Washington. She has also completed a course in business French. She reads and writes French. ReportsTo=2 PhotoPath=http://accweb/emmployees/davolio.bmp Orders=... EmployeeTerritories=... Employees=... Employee={ }
EmployeeID=9 LastName=Dodsworth FirstName=Anne Title=Sales Representative TitleOfCourtesy=Ms. BirthDate=1/27/1966 HireDate=11/15/1994 Address=7 Houndstooth Rd. City=London Region=null PostalCode=WG2 7LT Country=UK HomePhone=(71) 555-4444 Extension=452 Photo=... Notes=Anne has a BA degree in English from St. Lawrence College. She is fluent in French and German. ReportsTo=5 PhotoPath=http://accweb/emmployees/davolio.bmp Orders=... EmployeeTerritories=... Employees=... Employee={ }
This sample uses ToDictionary to immediately evaluate a query and a key expression into an Dictionary(Of K, T).
Public Sub LinqToSqlConversion04()
Dim q = From p In db.Products _
Where p.UnitsInStock <= p.ReorderLevel AndAlso Not p.Discontinued
Dim qDictionary = q.ToDictionary(Function(p) p.ProductID)
For Each key In qDictionary.Keys
Console.WriteLine("Key {0}:", key)
ObjectDumper.Write(qDictionary(key))
Console.WriteLine()
Next
End Sub
Result:
Key 2:
ProductID=2 ProductName=Chang SupplierID=1 CategoryID=1 QuantityPerUnit=24 - 12 oz bottles UnitPrice=19.0000 UnitsInStock=17 UnitsOnOrder=40 ReorderLevel=25 Discontinued=False Order_Details=... Category={ } Supplier={ }
Key 3:
ProductID=3 ProductName=Aniseed Syrup SupplierID=1 CategoryID=2 QuantityPerUnit=12 - 550 ml bottles UnitPrice=10.0000 UnitsInStock=13 UnitsOnOrder=70 ReorderLevel=25 Discontinued=False Order_Details=... Category={ } Supplier={ }
Key 11:
ProductID=11 ProductName=Queso Cabrales SupplierID=5 CategoryID=4 QuantityPerUnit=1 kg pkg. UnitPrice=21.0000 UnitsInStock=22 UnitsOnOrder=30 ReorderLevel=30 Discontinued=False Order_Details=... Category={ } Supplier={ }
Key 21:
ProductID=21 ProductName=Sir Rodney's Scones SupplierID=8 CategoryID=3 QuantityPerUnit=24 pkgs. x 4 pieces UnitPrice=10.0000 UnitsInStock=3 UnitsOnOrder=40 ReorderLevel=5 Discontinued=False Order_Details=... Category={ } Supplier={ }
Key 30:
ProductID=30 ProductName=Nord-Ost Matjeshering SupplierID=13 CategoryID=8 QuantityPerUnit=10 - 200 g glasses UnitPrice=25.8900 UnitsInStock=10 UnitsOnOrder=0 ReorderLevel=15 Discontinued=False Order_Details=... Category={ } Supplier={ }
Key 31:
ProductID=31 ProductName=Gorgonzola Telino SupplierID=14 CategoryID=4 QuantityPerUnit=12 - 100 g pkgs UnitPrice=12.5000 UnitsInStock=0 UnitsOnOrder=70 ReorderLevel=20 Discontinued=False Order_Details=... Category={ } Supplier={ }
Key 32:
ProductID=32 ProductName=Mascarpone Fabioli SupplierID=14 CategoryID=4 QuantityPerUnit=24 - 200 g pkgs. UnitPrice=32.0000 UnitsInStock=9 UnitsOnOrder=40 ReorderLevel=25 Discontinued=False Order_Details=... Category={ } Supplier={ }
Key 37:
ProductID=37 ProductName=Gravad lax SupplierID=17 CategoryID=8 QuantityPerUnit=12 - 500 g pkgs. UnitPrice=26.0000 UnitsInStock=11 UnitsOnOrder=50 ReorderLevel=25 Discontinued=False Order_Details=... Category={ } Supplier={ }
Key 43:
ProductID=43 ProductName=Ipoh Coffee SupplierID=20 CategoryID=1 QuantityPerUnit=16 - 500 g tins UnitPrice=46.0000 UnitsInStock=17 UnitsOnOrder=10 ReorderLevel=25 Discontinued=False Order_Details=... Category={ } Supplier={ }
Key 45:
ProductID=45 ProductName=Rogede sild SupplierID=21 CategoryID=8 QuantityPerUnit=1k pkg. UnitPrice=9.5000 UnitsInStock=5 UnitsOnOrder=70 ReorderLevel=15 Discontinued=False Order_Details=... Category={ } Supplier={ }
Key 48:
ProductID=48 ProductName=Chocolade SupplierID=22 CategoryID=3 QuantityPerUnit=10 pkgs. UnitPrice=12.7500 UnitsInStock=15 UnitsOnOrder=70 ReorderLevel=25 Discontinued=False Order_Details=... Category={ } Supplier={ }
Key 49:
ProductID=49 ProductName=Maxilaku SupplierID=23 CategoryID=3 QuantityPerUnit=24 - 50 g pkgs. UnitPrice=20.0000 UnitsInStock=10 UnitsOnOrder=60 ReorderLevel=15 Discontinued=False Order_Details=... Category={ } Supplier={ }
Key 56:
ProductID=56 ProductName=Gnocchi di nonna Alice SupplierID=26 CategoryID=5 QuantityPerUnit=24 - 250 g pkgs. UnitPrice=38.0000 UnitsInStock=21 UnitsOnOrder=10 ReorderLevel=30 Discontinued=False Order_Details=... Category={ } Supplier={ }
Key 64:
ProductID=64 ProductName=Wimmers gute Semmelknödel SupplierID=12 CategoryID=5 QuantityPerUnit=20 bags x 4 pieces UnitPrice=33.2500 UnitsInStock=22 UnitsOnOrder=80 ReorderLevel=30 Discontinued=False Order_Details=... Category={ } Supplier={ }
Key 66:
ProductID=66 ProductName=Louisiana Hot Spiced Okra SupplierID=2 CategoryID=2 QuantityPerUnit=24 - 8 oz jars UnitPrice=17.0000 UnitsInStock=4 UnitsOnOrder=100 ReorderLevel=20 Discontinued=False Order_Details=... Category={ } Supplier={ }
Key 68:
ProductID=68 ProductName=Scottish Longbreads SupplierID=8 CategoryID=3 QuantityPerUnit=10 boxes x 8 pieces UnitPrice=12.5000 UnitsInStock=6 UnitsOnOrder=10 ReorderLevel=15 Discontinued=False Order_Details=... Category={ } Supplier={ }
Key 70:
ProductID=70 ProductName=Outback Lager SupplierID=7 CategoryID=1 QuantityPerUnit=24 - 355 ml bottles UnitPrice=15.0000 UnitsInStock=15 UnitsOnOrder=10 ReorderLevel=30 Discontinued=False Order_Details=... Category={ } Supplier={ }
Key 74:
ProductID=74 ProductName=Longlife Tofu SupplierID=4 CategoryID=7 QuantityPerUnit=5 kg pkg. UnitPrice=10.0000 UnitsInStock=4 UnitsOnOrder=20 ReorderLevel=5 Discontinued=False Order_Details=... Category={ } Supplier={ }
