Object loading in the LINQ to SQL statement (17), linqsql

Object loading in the LINQ to SQL statement (17), linqsql
Object loading Delay

When querying an object, you actually only query this object. This object is not automatically obtained at the same time. This is delayed loading.

For example, you may need to view customer data and order data. You do not need to retrieve all order data related to each customer at first. The advantage is that you can use delayed loading to delay the retrieval operations of additional information until you actually need to retrieve them. See the following example: retrieve the CustomerID and query the OrderID based on this ID.

Var custs = from c in db. customers where c. city = "Sao Paulo" select c; // The preceding query syntax does not cause the statement to be executed immediately. It is just a descriptive statement, execute foreach (var cust in custs) {foreach (var ord in cust. orders) {// view customer data and order data at the same time }}

Statement Description: The original query does not request data. In the retrieved object link, navigate to how to trigger a new query for the database.

Preload: LoadWith Method

If you want to query the methods of some object sets at the same time. LINQ to SQL provides DataLoadOptions for loading objects immediately. The LoadWith method is used to load data related to the primary object immediately. The AssociateWith method is used to filter objects retrieved for a specific link.

Use the LoadWith method to specify which data related to the primary target should be retrieved at the same time. For example, if you know that you need information about the customer's order, you can use LoadWith to retrieve the order information while searching the customer information. You can use this method to access the database only once, but get two groups of information at the same time. In the following example, we set DataLoadOptions to instruct DataContext to load corresponding Orders while loading MERs, when a query is executed, all Orders of all MERs located in Sao Paulo are retrieved. In this way, the continuous access to the Orders attribute of the Customer object does not trigger new database queries. The left join is used for the SQL statement generated during execution.

NorthwindDataContext db = new NorthwindDataContext();DataLoadOptions ds = new DataLoadOptions();ds.LoadWith<Customer>(p => p.Orders);db.LoadOptions = ds;var custs = (     from c in db2.Customers     where c.City == "Sao Paulo"     select c);foreach (var cust in custs){    foreach (var ord in cust.Orders)    {        Console.WriteLine("CustomerID {0} has an OrderID {1}.",            cust.CustomerID,            ord.OrderID);    }}

Statement Description: In the original query process, LoadWith is used to request the relevant data, so that no additional round-trip is required for the database when the retrieved objects are routed.

Delayed loading: AssociateWith Method

The AssociateWith method is used to specify a subquery to limit the amount of data to be retrieved. In the following example, the AssociateWith method limits the retrieved Orders to those not shipped on the current day. If this method is not available, all Orders will be retrieved, even if only one subset is required. However, many SQL statements are generated when SQL statements are generated.

NorthwindDataContext db2 = new NorthwindDataContext (); DataLoadOptions ds = new DataLoadOptions (); ds. associateWith <Customer> (p => p. orders. where (o => o. shipVia> 1); db2.LoadOptions = ds; var custs = from c in db2.Customers where c. city = "London" select c; foreach (var cust in custs) {foreach (var ord in cust. orders) {foreach (var orderDetail in ord. orderDetails) {// You can query cust. customerID, ord. orderID, ord. shipVia, // orderDetail. productID, orderDetail. product. productName }}}

Statement Description: The original query does not request data. In the retrieved object link, navigate to how to trigger a new query for the database. This example also shows that you can use Assoicate With to filter relational objects when they are delayed.

Preload: LoadWith method and Associate With Method

This example shows that the LoadWith method is used to retrieve order information while searching customer information, order details while searching order information, and access the database only once. That is, you can retrieve many objects in a query. Use the Associate With method to limit the ordering rules of order details.

NorthwindDataContext db2 = new NorthwindDataContext (); DataLoadOptions ds = new DataLoadOptions (); ds. loadWith <Customer> (p => p. orders); ds. loadWith <Order> (p => p. orderDetails); ds. associateWith <Order> (p => p. orderDetails. orderBy (o => o. quantity); db2.LoadOptions = ds; var custs = (from c in db2.Customers where c. city = "London" select c); foreach (var cust in custs) {foreach (var ord in cust. orders) {foreach (var orderDetail in ord. orderDetails) {// query cust. customerID, ord. orderID // orderDetail. productID, orderDetail. quantity }}}

Statement Description: LoadWith is used to request data during the original query, this example allows you to sort relational objects by using Assoicate With when they are eager to be loaded.

Load rewrite

This example provides a LoadProducts division method in the Category class. When the product category is loaded, the LoadProducts method is called first to query products without source of goods.

Private IEnumerable <Product> LoadProducts (Category category) {// The LoadProducts division method takes precedence over the execution of the LINQ to SQL statement. The stored procedure can also be used here. return this. products. where (p => p. categoryID = category. categoryID ). where (p =>! P. Discontinued );}

When performing the following query, use the data returned by the preceding method to perform the following operations:

NorthwindDataContext db2 = new NorthwindDataContext (); DataLoadOptions ds = new DataLoadOptions (); ds. loadWith <Category> (p => p. products); db2.LoadOptions = ds; var q = (from c in db2.Categories where c. categoryID <3 select c); foreach (var cat in q) {foreach (var prod in cat. products) {// query cat. categoryID, prod. productID }}

Statement Description: override the division method LoadProducts in the Category class. When a product of a certain category is loaded, LoadProducts is called to load products that have not been discontinued in this category.