LINQ experience (12) -- object identification and object loading in the LINQ to SQL statement

Object ID

• Objects in the Runtime Library have a unique identifier. Two variables that reference the same object actually reference the same instance of this object. After you change a variable, you can see the changes through another variable.
• The row in the relational database table does not have a unique identifier. Because each row has a unique primary key, no two rows share the same key value.

In fact, we usually extract data from the database and put it into another layer, where the application processes the data. This is the model supported by LINQ to SQL. When you extract data from the database as rows, you do not expect the two rows with the same data to actually correspond to the same row instance. If you query a specific customer twice, you will get two rows of data. Each row contains the same information.

For objects. When you repeatedly request the same information from DataContext, it will actually provide you with the same object instance. You design them as hierarchies or relational graphs. You want to retrieve them just like retrieving objects, instead of receiving a large number of duplicate instances simply because you have repeatedly asked for the same content.

In LINQ to SQL, DataContext manages object identifiers. As long as you retrieve a new row from the database, the row will be recorded by its primary key to the identification table, and a new object will be created. As long as you retrieve the row, the original object instance will be passed to the response application. In this way, DataContext converts the concept of identity (that is, the primary key) seen by the database into the concept of identity (that is, the instance) seen in the corresponding language. The application only displays the objects in the State at the first retrieval. New data is discarded if they are different.

This method is used to manage the integrity of local objects to support open update. Since the only change that occurs after the object is initially created is made by the application, the intention of the application is very clear. If a change is made by an external party in the intermediate stage, the changes are identified when SubmitChanges () is called.

The above is from MSDN. Indeed, it seems a bit "regular". I will illustrate it with two examples below.

Object Cache

In the first example, if we execute the same query twice, we will receive a reference to the same object in the memory each time. Obviously, cust1 and cust2 are referenced by the same object.

Customer cust1 = db.Customers.First(c => c.CustomerID == "BONAP");Customer cust2 = db.Customers.First(c => c.CustomerID == "BONAP");

In the following example, If you perform different queries in the same row in the database, you will receive a reference to the same object in the memory each time. Cust1 and cust2 are referenced by the same object, but the database queries twice.

Customer cust1 = db.Customers.First(c => c.CustomerID == "BONAP");Customer cust2 = (    from o in db.Orders    where o.Customer.CustomerID == "BONAP"    select o )    .First()    .Customer;

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. Methods include:
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.

This series of links: navigation to the LINQ Series

Recommended resources of LINQ

Special topics: http://kb.cnblogs.com/zt/linq/ on all aspects of the entry, advanced, in-depth articles on LINQ.
LINQ group: a good place to learn questions or questions about http://space.cnblogs.com/group/linq.

This article is based on the signature 2.5 mainland China license agreement. You are welcome to reprint, interpret, or use it for commercial purposes. However, you must keep this article's signature Li yongjing (including the link). For more information, see here. If you have any questions or authorization negotiation, please leave a message for me.