Use deferredloadingenabled in Series 3 of LINQ to SQL, and specify the loading option in dataloadoption.

The data model used in this example is as follows:

Student and class are multi-to-one, while student and course are multi-to-Multi.

The deferredloadingenabled attribute of datacontext specifies whether delayed loading is required. The default value is true.. Taking student as an example, the delayed loading object refers to the class and the corresponding course. When the value of delayed loading is set to true, data in the class table and studentcourse table is automatically loaded when the class attribute or studentcourse attribute of the student instance is accessed, as shown in the following example.Code:

 
Static void main (string [] ARGs) {using (VAR writer = new streamwriter (watchsqlpath, false, encoding. utf8) {using (dbappdatacontext DB = new dbappdatacontext () {dB. log = writer; // set the delayed loading attribute to true dB. deferredloadingenabled = true; // get a student var astudent = dB. students. first (); // directly access the class attribute classname console of student. writeline ("{0} belongs to {1}", astudent. name, astudent. class. classname) ;}} console. readline ();}
 

When the deferredloadingenabled attribute of datacontext is set to true, You can directly access the data in the relational table. We can see the SQL statement used by the above Code:

 
Select top (1) [t0]. [studentid], [t0]. [name], [t0]. [hometown], [t0]. [gender], [t0]. [birthday], [t0]. [classid], [t0]. [weightinkg], [t0]. [heightincm], [t0]. [DESC] as [DESC] from [DBO]. [Student] as [t0] -- Context: sqlprovider (sql2005) model: attributedmetamodel build: 4.0.30319.1select [t0]. [classid], [t0]. [classname] from [DBO]. [Class] as [t0] Where [t0]. [classid] = @ P0 -- @ P0: Input int (size =-1; prec = 0; scale = 0) [1] -- Context: sqlprovider (sql2005) model: attributedmetamodel build: 4.0.30319.1
 

Two SQL statements are used. When we access the class attribute of student, datacontext automatically loads the class data.

In some cases, you can set the deferredloadingenabled attribute to false, and then directly access the class attribute of student if it is set to false, an exception of null reference will be thrown.

With delayed loading, what is the loadwith method used?

On msdn, loadwith is interpreted as using lambda expressions to retrieve specified data related to the primary target.

Loadwith is not a datacontext method, but a dataloadoptions method. You can set the loadoptions attribute for datacontext to change the way datacontext loads data; in other words, loadoptions sets whether to use join to load the associated table data when the select primary table data.

Suppose the scenario: I want to use an SQL statement to select the attribute of the class in the data of the select student table. The following code:

 
Static void main (string [] ARGs) {using (VAR writer = new streamwriter (watchsqlpath, false, encoding. utf8) {using (dbappdatacontext DB = new dbappdatacontext () {dB. log = writer; // declare the dataloadoptions variable VAR loadoptions = new dataloadoptions (); // set the loadoptions to be loaded at the same time when loading student. loadwith <student> (S => S. class); // assign the dataloadoptions instance to the DB. loadoptions dB. loadoptions = loadoptions; // load a student var student = dB. students. first (); console. writeline ("{0} belongs to {1}", student. name, student. class. classname) ;}} console. readline ();}
 

The results are exactly the same as in the preceding example, but the executed SQL is different. Let's look at the SQL:

 
Select top (1) [t0]. [studentid], [t0]. [name], [t0]. [hometown], [t0]. [gender], [t0]. [birthday], [t0]. [classid], [t0]. [weightinkg], [t0]. [heightincm], [t0]. [DESC] as [DESC], [T1]. [classid] as [classid2], [T1]. [classname] from [DBO]. [Student] as [t0] inner join [DBO]. [Class] as [T1] on [T1]. [classid] = [t0]. [classid] -- Context: sqlprovider (sql2005) model: attributedmetamodel build: 4.0.30319.1
 

This execution only uses one SQL statement. The student table inner joins the class table. This is what loadwith means.

Loadwith can use an SQL statement to load the data of the relevant table. What is the purpose of the associatewith method?

Let's assume that we want to know the information of a certain class and the information of students who experience more than 30 kilograms in this class. That is to say, we have attached a condition when obtaining the joined table data, see the following code and comments:

Static void main (string [] ARGs) {using (VAR writer = new streamwriter (watchsqlpath, false, encoding. utf8) {using (dbappdatacontext DB = new dbappdatacontext () {dB. log = writer; var loadoptions = new dataloadoptions (); // when loading the students attribute of the class, append the condition loadoptions with a weight greater than 30. associatewith <class> (C => C. students. where (S => S. weightinkg> 30); dB. loadoptions = loadoptions; // assign the dataloadoptions instance to the DB. loadoptions dB. loadoptions = loadoptions; // obtain the class var Aclass = dB with ID 1. classes. where (C => C. classid = 1 ). single (); console. writeline ("{0} students weighing more than 30kg:", Aclass. classname); foreach (VAR item in Aclass. students) {console. writeline ("\ t {0}", item. name) ;}} console. readline ();}
 

Let's take a look at the real SQL statements executed by the Code:

 select [t0]. [classid], [t0]. [classname] from [DBO]. [Class] as [t0] Where [t0]. [classid] = @ P0 -- @ P0: Input int (size =-1; prec = 0; scale = 0) [1] -- Context: sqlprovider (sql2005) model: attributedmetamodel build: 4.0.30319.1select [t0]. [studentid], [t0]. [name], [t0]. [hometown], [t0]. [gender], [t0]. [birthday], [t0]. [classid], [t0]. [weightinkg], [t0]. [heightincm], [t0]. [DESC] as [DESC] from [DBO]. [Student] as [t0] Where ([t0]. [weightinkg]> @ P0) and ([t0]. [classid] = (select [T2]. [classid] From (select top (1) [T1]. [classid] from [DBO]. [Class] as [T1] Where [T1]. [classid] = @ P1) as [T2]) -- @ P0: Input float (size =-1; prec = 0; scale = 0) [30] -- @ P1: Input int (size =-1; prec = 0; scale = 0) [1] -- Context: sqlprovider (sql2005) model: attributedmetamodel build: 4.0.30319.1 

We can see that there are two SQL statements. associatewith plays a role in the second SQL statement. The first condition is that the weight is greater than @ P0, and the second condition is classid; we can see that the SQL statement generated here is very bad. classid is the only primary key. It is reasonable to say that datacontext already knows this, but nested queries are used when the statement is generated, it can be said that it is a failure of writing to SQL.

Additional notes related to LINQ to SQL:

1. Starting from cud, how to insert, modify, and delete data using LINQ to SQL

2. query simple queries using LINQ to SQL

3. query delayed loading and immediate loading, using loadwith and associatewith

4. query inner join and left Outer Join

5. aggregate grouping having in LINQ to SQL

6. Do I have to worry about performance when optimizing the query of LINQ to SQL?