Why does EF use delayed loading?

EF (Entity Framework) is an ORM framework of Microsoft.

All those who have used EF know that it has a delayed loading function.

So what is the function of delayed loading?

Why is loading delayed?

What are the advantages and disadvantages of using delayed loading?

A simple small example can be used to illustrate EF's problems.

First, two simple data tables are used.

The diagram is as follows:

The UID of t_product is associated with the ID of t_users to form a foreign key relationship.

Is it really easy =

Then add the EF data model according to the database in the test project.

Now the preparation is complete.

The first thing to understand is: what is delayed loading?

Delayed loading can also be understood as loading on demand

It is easy to understand the literal meaning: load data according to the required data

Example above:

Simple code. I believe everyone can understand it.

Next, set the breakpoint to break down the code execution steps.

First, I set a breakpoint at the location.

Enable the SQL statement received by the SQL Server Profiler listener

Run the program and hit the breakpoint

At this time, the program stays on the line of code that retrieves data from the database (so you may think so ).

Because the Code has not been executed, the database cannot receive any SQL statements.

Press F11 to execute Statement by statement

As you can see, the program jumps and the database receives SQL statements.

VaR user = dbentities. t_users.where (u => U. ID = 2). firstordefault (); this line of code sends the SQL statement to the data retrieved from the database.

So where is the so-called latency plus carrier now?

In fact, this line of code is disguised. Even if it is loaded, it seems that the data is retrieved from the database immediately after the where method is called, but do not ignore the firstordefault ()

In EF, calling the where method only saves the SQL statement but does not submit it to the database.

However, when the firstordefault () method is called, it indicates that the program uses data, and then the SQL statement will be submitted to the database.

This means that the data is retrieved only when the data is used.

Let's just say no to it. Next we will continue to use the code to prove this statement.

Split the Code as follows, or set the breakpoint and run it in the place where the where method is called.

F11 next step

At this time, we can see that the Code has executed the where method and stopped the. firstordefault method. However, at this time, the database does not receive any SQL statements.

For ease of viewing, a breakpoint is also set in console. readkey (), and The F5 code stays in console. readkey ()

Now it is obvious that the SQL statement will be sent to the database only when data is used in the Program (firstordefault is called ).

This is the delayed loading of EF.

However, the list set itself has a where method.

Is this where method delayed loading, but it is not to retrieve data from the database?

In the smart prompt, We can vaguely see that the first parameter of the where method is a LINQ expression of ienumerable.

We can go to the VaR user = dbentities. t_users.where (u => U. ID = 2) line of code to convert the where Method to the definition by F12.

Note that the return value type of the where method is iqueryable rather than ienumerable.

This shows that although the two where methods have the same name and even have similar parameters, one returns ienumerable and the other returns iqueryable.

This essentially differentiates the two where methods.

At the same time, pay attention to the this keyword of the first parameter of the where method of iqueryable. What does this mean?

It indicates that the where is not in the iqueryable interface, but is added to the external class through the extension method (this class is queryable, this class provides N extension methods similar to where for the iqueryable interface)

So it can be said that EF can implement delayed loading because of the existence of queryable classes.

But from this line of code, we can see that

VaR user = dbentities. t_users.where (u => U. ID = 2 );

The where method is located from the t_users attribute of the EF context object dbentities.

Isn't the where Method in the queryable class (or iqueryable Interface)

How can it be called from the context attribute of EF?

Now we can find the EF context to see what the t_users attribute is.

We can clearly see that t_users is a dbset <t_users> type generic attribute.

F12 goes to the definition to see what this dbset is.

Can I find iqueryable IN THE dbset base class (Interface!

This explains why the where method can be clicked in the t_users attribute of the EF context and used

Because the t_users type inherits the iqueryable interface, you can naturally use the methods in the iqueryable interface!

The previous article briefly explains the latency Loading Mechanism of EF.

After talking about that, why do we need to delay loading?

Can I use it directly?

From this simple example, latency loading seems to be much more

However, it is usually impossible for us to operate the database.

Take the commonly used Paging for example.

Generally, data is sorted first.

Then, skip several rows of data as required.

Number of rows of data to be retrieved

This is not a simple where method.

At least order needs to be called first for sorting, Then skip skips several rows of data, and finally takes several rows of data

If you use the where, order, Skip, take, and other methods, submit the SQL statement to the database immediately.

In this case, at least four requests must be sent for a paging query.

That is to say, it needs to interact with the database four times.

If delayed loading is used

When the above where/order/skip/take method is called, it can be regarded as just a patchwork condition.

When conditions are met (data is usually used, for example, the firstordefault method)

Submit the entire SQL statement to the database.

In this way, the number of interactions with the database is reduced from 4 to 1.

This is one of the essential reasons for the use of delayed loading: The join conditions are submitted together to reduce the number of database interactions and improve the database throughput.

This is also the advantage of delayed loading.

In this example, t_products has a foreign key uid associated with t_users.

Code:

VaR Product = dbentities. t_products.where (P => P. uid = 2). firstordefault ();
Console. writeline (product. t_users.username );

The table content is as follows:

We can see that uid 2 has two rows.

Retrieve the t_products set with UID 2 and obtain the object of the first row.

And output the username of t_user, the associated foreign key attribute.

Continue to set breakpoint and run on each line of code

Where the program stays, F5 executes, hitting the next breakpoint

We can see that the firstordefault method is called.

So I want the database to send SQL statements

But observe the SQL statement on the left.

The foreign key column is not queried together.

Continue F5 run

Still observe the SQL statement on the left

This line of code is executed because console. writeline (product. t_users.username );

Foreign key columns are required

At this time, EF only submits SQL statements for querying foreign key columns to the database.

This is the second essential cause of delayed loading: For foreign key attributes, EF queries only when this foreign key attribute is used.

Through the above introduction, the advantages of EF delayed loading are obvious.

What are the disadvantages?

For example, the current requirement is

Retrieve t_products

var products = dbEntities.T_Products.Where(p => true);            foreach (var p in products)            {                Console.WriteLine(p.T_Users.UserName);            }            Console.ReadKey();

All objects in the table

Print the foreign key t_users of each object and output its username.
We know

When the first line of code is executed, it does not interact with the database.

But now the problem arises.

Foreach must use the foreign key of the entity in the products set for each loop.

This causes data to be retrieved from the database once every cycle.

This is the disadvantage of EF delayed loading: a database is queried every time a foreign key column is used.

In fact, EF has made some small column optimization internally, and takes the same object only once. For example, in the t_products table, there are two rows with UID 2 and three rows with UID 3, originally, four queries are required. After optimization, only two queries are required.

However, when the data volume is very large, these small optimizations have little effect.

So how can we avoid this defect?

In this case, you can query the foreign key columns through the connection query and use them cyclically.

The Code is as follows:

VaR products = dbentities. t_products.include ("t_users"); // use include to tell EF which foreign key column to query. The most comfortable thing is that include can be kept up ~! Foreach (var p in products) {console. writeline (P. t_users.username);} console. readkey ();

Click Run to see the effect

Note the SQL statement on the left

An inner join SQL statement is generated.

Only one database interaction was performed.