Shulou(Shulou.com)06/02 Report--

Editor to share with you how to use user-defined Linq table-valued functions, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's learn about it!

Use user-defined Linq table-valued functions

The Linq table-valued function returns a single rowset (unlike stored procedures, stored procedures can return multiple result shapes). Because the return type of the Linq table-valued function is Table, you can use the Linq table-valued function anywhere you can use the table in SQL. In addition, you can handle Linq table-valued functions exactly like tables.

The following SQL user-defined function explicitly declares that it returns a TABLE. Therefore, the structure of the returned rowset is implicitly defined.

ALTER FUNCTION [dbo]. [ProductsUnderThisUnitPrice] (@ price Money) RETURNS TABLE AS RETURN SELECT * FROM Products as P Where p.UnitPrice < @ price

Drag into the designer, and LINQ to SQL maps this function as follows:

IsComposable=true)] public IQueryable ProductsUnderThisUnitPrice ([Parameter (DbType= "Money")] System.Nullable price) {return this.CreateMethodCallQuery (this, ((MethodInfo) (MethodInfo.GetCurrentMethod (), price);}

At this point, we slightly modify the Discontinued property to a nullable bool type.

Private System.Nullable _ Discontinued; public System.Nullable Discontinued {}

We can call and use this:

Var Q = from p in db.ProductsUnderThisUnitPrice (10.25m) where! (p.Discontinued?? False) select p

The SQL statement is generated as follows:

SELECT [t0]. [ProductID]. [ProductName]. [SupplierID], [t0]. [CategoryID], [t0]. [QuantityPerUnit]. [UnitPrice], [t0]. [UnitsInStock], [t0]. [UnitsOnOrder], [t0]. [ReorderLevel], [t0]. [Discontinued] FROM [dbo]. [ProductsUnderThisUnitPrice] (@ p0) AS [t0] WHERE NOT (COALESCE ([t0]. [Discontinued]) @ p1)) = 1)-@ p0: Input Money (Size = 0) Prec = 19; Scale = 4) [10.25]-@ p1: Input Int (Size = 0; Prec = 0; Scale = 0) [0]

Use user-defined Linq table-valued functions in a join manner

Using the ProductsUnderThisUnitPrice user-defined function above, in LINQ to SQL, we call the following:

Var Q = from c in db.Categories join p in db.ProductsUnderThisUnitPrice (8.50m) on c.CategoryID equals p.CategoryID into prods from p in prods select new {c.CategoryID, c.CategoryName, p.ProductName, p.UnitPrice}; the generated SQL code indicates that the join is performed on the table returned by this function.

SELECT [t0]. [CategoryID]. [CategoryName], [T1]. [ProductName], [T1]. [UnitPrice] FROM [dbo]. [Categories] AS [t0] CROSS JOIN [dbo]. [ProductsUnderThisUnitPrice] (@ p0) AS [T1] WHERE ([t0]. [CategoryID]) = [T1]. [CategoryID]-@ p0: Input Money (Size = 0; Prec = 19) Scale = 4) [8.50] above is all the content of the article "how to use user-defined Linq table-valued functions". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.