How to use Linq DataLoadOptions
Editor to share with you how to use Linq DataLoadOptions, I believe that 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 go to know it!
Linq DataLoadOptions restriction
Linq to sql's use of Linq DataLoadOptions is limited, and it only supports one one-to-many relationship. A customer may have multiple orders, and an order may have multiple detailed orders:
DataLoadOptions options = new DataLoadOptions (); options.LoadWith (c = > c.Orders); options.LoadWith (o = > o.Order_Details); ctx.LoadOptions = options; IEnumerable customers = ctx.Customers.ToList ()
The execution of such a statement causes the following SQL to execute N times (with different parameters):
SELECT [t0]. [OrderID], [t0]. [CustomerID], [t0]. [EmployeeID], [t0]. [OrderDate]
[t0]. [RequiredDate], [t0]. [ShippedDate], [t0]. [ShipVia], [t0]. [Freight], [t0].
[ShipName], [t0]. [ShipAddress], [t0]. [ShipCity], [t0]. [ShipRegion], [t0].
[ShipPostalCode], [t0]. [ShipCountry], [t1]. [OrderID] AS [OrderID2], [t1].
[ProductID], [T1]. [UnitPrice], [T1]. [Quantity], [T1]. [Discount], (
SELECT COUNT (*)
FROM [dbo]. [Order Details] AS [t2]
WHERE [T2]. [OrderID] = [t0]. [OrderID]
) AS [count]
FROM [dbo]. [Orders] AS [t0]
LEFT OUTER JOIN [dbo]. [Order Details] AS [T1] ON [T1]. [OrderID] = [t0]. [OrderID]
WHERE [t0]. [CustomerID] = @ x1
ORDER BY [t0]. [OrderID], [t1]. [ProductID]
-- @ x1: Input StringFixedLength (Size = 5; Prec = 0; Scale = 0) [ALFKI]
For many-to-1 relationships, Linq to sql has no restrictions on Linq DataLoadOptions:
DataLoadOptions options = new DataLoadOptions (); options.LoadWith (c = > c.Category); options.LoadWith (c = > c.Order_Details); options.LoadWith (o = > o.Order); ctx.LoadOptions = options; IEnumerable products = ctx.Products.ToList ()
Since multiple products correspond to 1 category and multiple detailed orders correspond to 1 order, only products and detailed orders have a many-to-1 relationship, so there will be only one SQL (however, it is better to do so less, because it consumes too much).
SELECT [t0]. [ProductID], [t0]. [ProductName], [t0]. [SupplierID], [t0].
[CategoryID], [t0]. [QuantityPerUnit], [t0]. [UnitPrice], [t0].
[UnitsInStock], [t0]. [UnitsOnOrder], [t0]. [ReorderLevel], [t0].
[Discontinued], [t3]. [OrderID], [t3]. [ProductID] AS [ProductID2], [t3].
[UnitPrice] AS [UnitPrice2], [t3]. [Quantity], [t3]. [Discount], [t4].
[OrderID] AS [OrderID2], [t4]. [CustomerID], [t4]. [EmployeeID], [t4].
[OrderDate], [t4]. [RequiredDate], [t4]. [ShippedDate], [t4]. [ShipVia]
[t4]. [Freight], [t4]. [ShipName], [t4]. [ShipAddress], [t4]. [ShipCity]
[T4]. [ShipRegion], [T4]. [ShipPostalCode], [T4]. [ShipCountry],
SELECT COUNT (*)
FROM [dbo]. [Order Details] AS [t5]
INNER JOIN [dbo]. [Orders] AS [T6] ON [T6]. [OrderID] = [T5]. [OrderID]
WHERE [T5]. [ProductID] = [t0]. [ProductID]
) AS [count], [T2]. [test], [T2]. [CategoryID] AS [CategoryID2], [T2].
[CategoryName], [t2]. [Description], [t2]. [Picture]
FROM [dbo]. [Products] AS [t0]
LEFT OUTER JOIN (
SELECT 1 AS [test], [t1]. [CategoryID], [t1]. [CategoryName], [t1].
[Description], [t1]. [Picture]
FROM [dbo]. [Categories] AS [t1]
) AS [T2] ON [T2]. [CategoryID] = [t0]. [CategoryID]
LEFT OUTER JOIN ([dbo]. [Order Details] AS [T3]
INNER JOIN [dbo]. [Orders] AS [T4] ON [T4]. [OrderID] = [T3].
[OrderID]) ON [T3]. [ProductID] = [t0]. [ProductID]
ORDER BY [t0]. [ProductID], [t2]. [CategoryID], [t3]. [OrderID]
The above is all the contents of this article "how to use Linq DataLoadOptions". 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!
