LINQ path 10:linq to SQL and Entity Framework (bottom)

In this article, we'll go on to the previous article, "LINQ to SQL and Entity Framework (top)", and continue to use LINQ to SQL and the Entity Framework to practice "Interpreting queries" to learn the key features of these technologies. We are here to focus on the "LINQ" section in LINQ to SQL and the Entity Framework, and compare the similarities and differences between the two technologies. With the LINQ knowledge we've described earlier, and more LINQ Operators that will be discussed in the future, we believe readers can write elegant and efficient Queries for LINQ to SQL and the Entity Framework. For simplicity and clarity, the LINQ to SQL and Entity Framework are abbreviated in some places, namely: L2s and EF.

deferred execution of LINQ to SQL and Entity Framework

As with local queries, L2S and EF queries are deferred, allowing us to incrementally create LINQ queries. However, there is one aspect in which l2s and EF have their own special deferred execution semantics, which is when a subquery appears in the Select expression:

• For local queries, you get two deferred executions because, from a functional standpoint, you select a sequence that contains multiple queries. If you do not traverse the inner subquery when you traverse the outer result sequence, the subquery will not execute at this time.
• For L2S/EF, the subquery and the Outer main query are executed at the same time, thus avoiding excessive connection to the remote database causing performance problems.

For example, for L2S/EF, the following query executes at the first foreach statement and executes only once:

            varContext =NewLifepoemcontext ("Database connection String");

            varquery = fromCinchContext. Customers
                        Select
                             fromOinchContext. Orders
                            Select New{c.name, o.price};
 
            foreach(varCustomerOrdersinchQuery
                foreach(varNamepriceinchCustomerOrders)
Console.WriteLine (Nameprice.name +"spent"+ Nameprice.price);

In other words, the entitysets/entitycollections that we explicitly specify in the select expression is always available in one execution:

            varContext =NewLifepoemcontext ("Database connection String");
            varquery = fromCinchContext. Customers
                        Select New{c.name, c.orders};
 
            foreach(varRowinchQuery
                foreach(varOrderinchRow. Orders)//no additional connection queries
Console.WriteLine (Row. Name +"spent"+ Order. Price);

However, if we do not perform the data conversion beforehand, we will apply the Entityset/entitycollection property to deferred execution. In the following example, L2s and EF perform additional orders queries in each iteration:

            true;   // only EF needs this sentence

            foreach  in context. Customers)
                foreach  in c.orders)   //  will start a new SQL query every time
                    "" + o.price); 

This pattern has an advantage when we need conditional execution of internal queries, such as when we might need to rely on a client to do a conditional test:

            foreach  in context. Customers)
                if (Mywebservice.hasbadcredithistory (C.id))
                    foreach  in c.orders)   //  start a new SQL query
                        "" + o.price); 

Above we see how the Data Transformation (select) of the associated property is displayed to avoid duplication of execution. As we'll see later, L2s and EF also provide other mechanisms to implement this functionality.

dataloadoptions

The DataLoadOptions class is a feature of the L2s, which has two special effects:

• AssociateWith allows you to set filter conditions for EntitySet associations beforehand
• LoadWith allows you to set some entitysets for active loading (eager loading), thus reducing the number of connections to the database

Set Filter Criteria

If we only care about orders with price greater than 1000, we can set the filter by DataLoadOptions:

 var  context = new  Lifepoemconte XT ( " database connection string  ");            
 
 DataLoadOptions options = new  dataloadoptions (); 
 Options. Associatewith<customer> (c = c.orders.where (order = order). Price > 1000 )); 
 Context. loadoptions = options; 
 
 foreach  (Customer c in  cont Ext.                    Customers) 
 if  (Mywebservice.hasbadcredithistory (c.id)) 
 Processcustomer (c); //  

This instructs our DataContext instance to always filter the orders of customer with the given conditions. It is important to note that AssociateWith does not change the semantics of deferred execution, it is simply a command to implicitly filter a particular relationship.

Active load (Eager Loading)

The second function of DataLoadOptions is to request that a entitysets follow the parent entitysets to load together. For example, suppose you want to load all customers together with their orders, instead of querying orders for each customer separately:

            varContext =NewLifepoemcontext ("Database connection String");
 
DataLoadOptions options =NewDataLoadOptions ();
Options. Loadwith<customer> (c = c.orders);
Context. loadoptions = options;
 
            foreach(Customer CinchContext. Customers)//one-time query
                foreach(Order oinchC.orders)//because of the dataloadoptions above, all orders are in the same load in the query above .
Console.WriteLine (C.name +"bought a"+ o.description);

This instructs DataContext that, whenever a customer is acquired, its orders will be loaded at the same time. We can combine the LoadWith and AssociateWith methods to get active loading and to filter the loaded entitysets, such as:

            New DataLoadOptions ();
            Options. Loadwith<customer> (c = c.orders);
            ));

Active loading in the Entity framework (Eager Loading)

In the Entity Framework, if we want an associated entitysets to be actively loaded, you can use the Include method. The following code is in a SQL query that gets all the customers and their orders:

            foreach (var in context.) Customers.include ("Orders"))
                foreach (var in C.orders)
                    Console.WriteLine (Order. Description);

The include can be cascaded, and if each order has OrderDetails and salespersons navigation properties, we can write the following query so that the data is also loaded together:

            Context. Customers.include ("orders.orderdetails")
                             . Include ("orders.salespersons")

Update

L2s and EF will also track your changes to entities and allow you to update them to the database. For L2s, we call the SubmitChanges method of the DataContext object, and for EF, we call the Savechagnes method of the ObjectContext object.

L2s's table<> class provides InsertOnSubmit and DeleteOnSubmit methods for inserting or deleting rows from a table; EF's objectset<> class provides the AddObject and DeleteObject methods to implement the same functionality, see the following code example:

            varContext =NewLifepoemcontext ("Database connection String");
 
Customer cust =NewCustomer {ID = +, Name ="Yoyoo"};
Context. Customers.insertonsubmit (Cust);//Insert Customer, use AddObject in EF
Context. SubmitChanges ();//using SaveChanges in EF

            //now we get the row of data inserted above, update it, and then delete it
Customer Cust2 = context. Customers.single (c = c.id = = +);
Cust2. Name ="Yoyoo2";
Context. SubmitChanges ();//Update Customer
 
Context. Customers.deleteonsubmit (CUST2);//using DeleteObject in EF
Context. SubmitChanges ();//Delete Customer

Submitchanges/savechanges collects all the changes that have been made to entities since the context was created (or last save), and then executes an SQL statement to write them back to the database.

We can also call the Add method to add data rows to a entityset/entitiycollection, and the corresponding foreign keys are automatically generated when the SubmitChanges or savechanges,l2s and EF are executed:

            New  -  ;
            New 101  ;

            1);
            Cust. Orders.add (O1);
            Cust. Orders.add (O2);

            Context. SubmitChanges ();

In this example, L2s/ef automatically writes the foreign key value of 1 to the New Order's CustomerID column, because we define the associated properties for customer and order, such as:

 //  with l2s   
  [Association (Name= " customer_ Order  , Storage="  _orders  , Thiskey="  id  , Otherkey="  customerid  )] 
 public  Entityset<order> Orders {get  {...} set  {...} }

When you remove a row of data from a entityset/entitycollection, his foreign key column is automatically set to null. The following generation removes the association between our recently added two orders and their customer, noting that removing the association only removes the child entities:

            var New Lifepoemcontext ("database connection string");

            1);
            ));
            101));

            Context. SubmitChanges ();

Because the code above sets the CustomerID column of each order to NULL, the Order.customerid column in the database must be nullable, or an exception will be thrown.

If we want to completely delete the child entities, we need to call DeleteOnSubmit:

            // With l2s
            ));
            101));
            Context. SubmitChanges ();

            // With EF
            ));
            101));
            Context. SaveChanges ();

comparison of LINQ to SQL and Entity Framework APIs

As we saw in these two articles, l2s and EF are very similar to LINQ queries and data updates, except that they create objects or invoke methods that differ, and the following table summarizes their API differences:

Objective	LINQ to SQL	Entity Framework
Gets the class that maintains all CRUD operations	DataContext	ObjectContext
Get all entities of a certain type from the database (latency)	GetTable	Createobjectset
The return type of the above method	Table<t>	Objectset<t>
Commit an update to an entity object	SubmitChanges	SaveChanges
Add a single entity	InsertOnSubmit	AddObject
Delete an entity	DeleteOnSubmit	DeleteObject
The type that represents the associated attribute (the party with multiple related entities)	Entityset<t>	Entitycollection<t>
The Type (field type) that represents the associated attribute (the party with multiple related entities)	Entityref<t>	Entityreference<t>
Default policy when loading an associated property	Automatic lazy Loading	Explicitly called
Active load (eager loading)	DataLoadOptions	. Include ()

Through these two articles, we have a targeted understanding of the features of L2s and EF in LINQ query support. By comparing their similarities and differences, let us better their inner links and differentiate them in the use of the difference. In the next few blogs, I'm going to make a more detailed introduction to the LINQ Query Operators (LINQ Operators). Only after you understand most of the query operators can you better write a powerful yet concise and elegant LINQ query.

LINQ path 10:linq to SQL and Entity Framework (bottom)