LINQ To SQL Samples - User-Defined Functions |
This sample demonstrates using a scalar user-defined function in a projection.
Public Sub LinqToSqlUserDefined01()
Dim q = From c In db.Categories _
Select New With {c.CategoryID, .TotalUnitPrice = db.TotalProductUnitPriceByCategory(c.CategoryID)}
ObjectDumper.Write(q)
End Sub
Result:
CategoryID=1 TotalUnitPrice=465.7500
CategoryID=2 TotalUnitPrice=286.7500
CategoryID=3 TotalUnitPrice=340.0800
CategoryID=4 TotalUnitPrice=297.3000
CategoryID=5 TotalUnitPrice=148.7500
CategoryID=6 TotalUnitPrice=330.0400
CategoryID=10 TotalUnitPrice=null
CategoryID=11 TotalUnitPrice=null
CategoryID=12 TotalUnitPrice=null
CategoryID=13 TotalUnitPrice=null
CategoryID=14 TotalUnitPrice=null
CategoryID=15 TotalUnitPrice=null
CategoryID=16 TotalUnitPrice=null
CategoryID=17 TotalUnitPrice=null
CategoryID=18 TotalUnitPrice=null
CategoryID=19 TotalUnitPrice=null
CategoryID=7 TotalUnitPrice=166.8500
CategoryID=8 TotalUnitPrice=260.1900
This sample demonstrates using a scalar user-defined function in a where clause.
Public Sub LinqToSqlUserDefined02()
Dim q = From p In db.Products _
Where p.UnitPrice = db.MinUnitPriceByCategory(p.CategoryID) _
Select p
ObjectDumper.Write(q)
End Sub
Result:
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={ }
ProductID=13 ProductName=Konbu SupplierID=6 CategoryID=8 QuantityPerUnit=2 kg box UnitPrice=7.0000 UnitsInStock=24 UnitsOnOrder=0 ReorderLevel=5 Discontinued=False Order_Details=... Category={ } Supplier={ }
ProductID=19 ProductName=Teatime Chocolate Biscuits SupplierID=8 CategoryID=3 QuantityPerUnit=10 boxes x 12 pieces UnitPrice=10.2000 UnitsInStock=25 UnitsOnOrder=0 ReorderLevel=5 Discontinued=False Order_Details=... Category={ } Supplier={ }
ProductID=24 ProductName=Guaraná Fantástica SupplierID=10 CategoryID=1 QuantityPerUnit=12 - 355 ml cans UnitPrice=5.5000 UnitsInStock=20 UnitsOnOrder=0 ReorderLevel=0 Discontinued=True Order_Details=... Category={ } Supplier={ }
ProductID=33 ProductName=Geitost SupplierID=15 CategoryID=4 QuantityPerUnit=500 g UnitPrice=3.5000 UnitsInStock=112 UnitsOnOrder=0 ReorderLevel=20 Discontinued=False Order_Details=... Category={ } Supplier={ }
ProductID=52 ProductName=Filo Mix SupplierID=24 CategoryID=5 QuantityPerUnit=16 - 2 kg boxes UnitPrice=8.0000 UnitsInStock=38 UnitsOnOrder=0 ReorderLevel=25 Discontinued=False Order_Details=... Category={ } Supplier={ }
ProductID=54 ProductName=Tourtière SupplierID=25 CategoryID=6 QuantityPerUnit=16 pies UnitPrice=8.4500 UnitsInStock=21 UnitsOnOrder=0 ReorderLevel=10 Discontinued=False Order_Details=... Category={ } Supplier={ }
ProductID=74 ProductName=Longlife Tofu SupplierID=4 CategoryID=7 QuantityPerUnit=5 kg pkg. UnitPrice=11.0000 UnitsInStock=4 UnitsOnOrder=20 ReorderLevel=5 Discontinued=False Order_Details=... Category={ } Supplier={ }
This sample demonstrates selecting from a table-valued user-defined function.
Public Sub LinqToSqlUserDefined03()
Dim q = From p In db.ProductsUnderThisUnitPrice(10.25#) _
Where Not p.Discontinued _
Select p
ObjectDumper.Write(q)
End Sub
Result:
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
ProductID=13 ProductName=Konbu SupplierID=6 CategoryID=8 QuantityPerUnit=2 kg box UnitPrice=7.0000 UnitsInStock=24 UnitsOnOrder=0 ReorderLevel=5 Discontinued=False
ProductID=19 ProductName=Teatime Chocolate Biscuits SupplierID=8 CategoryID=3 QuantityPerUnit=10 boxes x 12 pieces UnitPrice=10.2000 UnitsInStock=25 UnitsOnOrder=0 ReorderLevel=5 Discontinued=False
ProductID=23 ProductName=Tunnbröd SupplierID=9 CategoryID=5 QuantityPerUnit=12 - 250 g pkgs. UnitPrice=10.0000 UnitsInStock=61 UnitsOnOrder=0 ReorderLevel=25 Discontinued=False
ProductID=33 ProductName=Geitost SupplierID=15 CategoryID=4 QuantityPerUnit=500 g UnitPrice=3.5000 UnitsInStock=112 UnitsOnOrder=0 ReorderLevel=20 Discontinued=False
ProductID=52 ProductName=Filo Mix SupplierID=24 CategoryID=5 QuantityPerUnit=16 - 2 kg boxes UnitPrice=8.0000 UnitsInStock=38 UnitsOnOrder=0 ReorderLevel=25 Discontinued=False
ProductID=54 ProductName=Tourtière SupplierID=25 CategoryID=6 QuantityPerUnit=16 pies UnitPrice=8.4500 UnitsInStock=21 UnitsOnOrder=0 ReorderLevel=10 Discontinued=False
ProductID=75 ProductName=Rhönbräu Klosterbier SupplierID=12 CategoryID=1 QuantityPerUnit=24 - 0.5 l bottles UnitPrice=8.7500 UnitsInStock=125 UnitsOnOrder=0 ReorderLevel=25 Discontinued=False
This sample demonstrates joining to the results of a table-valued user-defined function.
Public Sub LinqToSqlUserDefined04()
Dim q = From c In db.Categories _
Group Join p In db.ProductsUnderThisUnitPrice(8.5#) On c.CategoryID Equals p.CategoryID Into prods = Group _
From p In prods _
Select New With {c.CategoryID, c.CategoryName, p.ProductName, p.UnitPrice}
ObjectDumper.Write(q)
End Sub
Result:
CategoryID=8 CategoryName=Seafood ProductName=Konbu UnitPrice=7.0000
CategoryID=1 CategoryName=Beverages ProductName=Guaraná Fantástica UnitPrice=5.5000
CategoryID=4 CategoryName=Dairy Products ProductName=Geitost UnitPrice=3.5000
CategoryID=5 CategoryName=Grains/Cereals ProductName=Filo Mix UnitPrice=8.0000
CategoryID=6 CategoryName=Meat/Poultry ProductName=Tourtière UnitPrice=8.4500
