MVC series Learning (III)-delayed loading of EF, mvc-ef

MVC series Learning (III)-delayed loading of EF, mvc-ef

1. What is delayed loading?

Literally, an action that is supposed to be executed immediately is not executed immediately.

2. Understand the code

Static void Main (string [] args) {// when executing this statement, check the SQL Monitor and find that the SQL statement IEnumerable <Student> stu = dbContext is not generated. students. where (s => s. id = 1 ). select (s => s); // The SQL statement Student = stu is generated only when it is used. firstOrDefault ();}

An SQL statement is generated only when the object is used.

3. Find the cause and what causes delayed Loading

First, understand the two Where () methods:

A. Where () of the Set ()

List<string> listStr = new List<string>(){    "A",    "BB",    "CCC"};string  bb = listStr.Where(s => s.Length == 2).Select(s => s).FirstOrDefault();

Go to the Where definition and find that the Where Method in the set is actually an extension method of IEnumerable. This interface inherits from

public static IEnumerable<TSource> Where<TSource>(this IEnumerable<TSource> source, Func<TSource, bool> predicate);

public interface IEnumerable<out T> : IEnumerable

B. Where Method in DbSet

var stu = dbContext.Students.Where(s => s.Id == 1);

public partial class SchoolEntities : DbContext{    public virtual DbSet<Student> Students { get; set; }}

Public static IQueryable <TSource> Where <TSource> (this IQueryable <TSource> source, Expression <Func <TSource, bool> predicate );

public interface IQueryable<out T> : IEnumerable<T>, IQueryable, IEnumerable

public interface IQueryable : IEnumerable

 

C. Differences between IEnumerable and IQueryable

Var s1 = dbContext. Students. Where (s => s. Id = 1 ); Var s2 = s1.Where (s => s. Age> 0 ); Var s3 = s2.Select (s => s). FirstOrDefault ();

You can use the SQL Monitor to view and find that an SQL query is executed in total.

Conclusion:

1. The implementation of EF delayed loading is actually an extension method on IQueryable. More specifically, it is implemented by the DbQuery type. 2. IEnumerable <T> run the command directly. The result after the first execution is saved to the memory and the command tree is not spliced. 3. IQueryable <T> Concatenates the statement into a command tree and then executes 4. Both operations only access the database once.

4. Why delayed loading?

A. You cannot determine whether the query conditions have been added and ended.

DbQuery <Student> s1 = dbContext. Students. Where (s => s. Id = 1). Where (s => s. Age> 0) as DbQuery <Student>;

When a query condition is added, only the DbQuery objects containing all the conditions are returned. The corresponding SQL statement is generated only when the conditions are used.

B. For foreign key entities, load as needed

The two tables that need to be used this time have the primary-foreign key relationship as follows:

Var tea = dbContext. teachers. where (t => t. tId = 1); // generate an SQL statement. Code 1: Teacher teacher = tea. firstOrDefault (); // generates an SQL statement. Code 2: string className = teacher. teachClass. cName;

Code 1, for example:

SELECT TOP (1)     [Extent1].[tId] AS [tId],     [Extent1].[tName] AS [tName],     [Extent1].[tAge] AS [tAge],     [Extent1].[tClass] AS [tClass]    FROM [dbo].[Teacher] AS [Extent1]    WHERE 1 = [Extent1].[tId]

Code 2, such:

exec sp_executesql N'SELECT     [Extent1].[cId] AS [cId],     [Extent1].[cName] AS [cName]    FROM [dbo].[TeachClass] AS [Extent1]    WHERE [Extent1].[cId] = @EntityKeyValue1',N'@EntityKeyValue1 int',@EntityKeyValue1=1

Observe and draw a conclusion:

1. Load as needed: During the first execution, the foreign key attribute is not used, so when an SQL statement is generated, the TeachClass table is not queried. 2. When EF needs to use the foreign key attribute, the corresponding table will be loaded.

C. disadvantages of On-Demand Loading:

The instance code is as follows:

DbQuery <Teacher> teachers = dbContext. teachers; StringBuilder sbTeacher = new StringBuilder (100); foreach (Teacher tea in teachers) {// each time you call a foreign key entity on the foreign key table Teachers, the database will be queried. // EF has an optimization. The same foreign key entity is queried only once, that is, the same TeachClass is queried only once sbTeacher. append (tea. teachClass. cName );}

The generated SQL script is as follows:

exec sp_executesql N'SELECT     [Extent1].[cId] AS [cId],     [Extent1].[cName] AS [cName]    FROM [dbo].[TeachClass] AS [Extent1]    WHERE [Extent1].[cId] = @EntityKeyValue1',N'@EntityKeyValue1 int',@EntityKeyValue1=1

The second time, and the third time, because the value of TeachClass is the same, only one query is performed.

exec sp_executesql N'SELECT     [Extent1].[cId] AS [cId],     [Extent1].[cName] AS [cName]    FROM [dbo].[TeachClass] AS [Extent1]    WHERE [Extent1].[cId] = @EntityKeyValue1',N'@EntityKeyValue1 int',@EntityKeyValue1=2

5. Connection Query

Since EF loads the corresponding table only when foreign key entities are used, how can we connect two tables?

A. Use the Include Method

DbQuery <Teacher> teachers = dbContext. teachers. include ("TeachClass"); StringBuilder sbTeacher = new StringBuilder (100); foreach (Teacher tea in teachers) {// only used for the first query, query and save the data to the memory. // the next operation is to read the data in the memory and the database sbTeacher is not read. append (tea. teachClass. cName );}

View the SQL statement. EF generates left outer join for us and connects two tables.

SELECT     [Extent1].[tId] AS [tId],     [Extent1].[tName] AS [tName],     [Extent1].[tAge] AS [tAge],     [Extent1].[tClass] AS [tClass],     [Extent2].[cId] AS [cId],     [Extent2].[cName] AS [cName]    FROM  [dbo].[Teacher] AS [Extent1]    LEFT OUTER JOIN [dbo].[TeachClass] AS [Extent2] ON [Extent1].[tClass] = [Extent2].[cId]

B. Another way to generate join

var teachers = dbContext.Teachers.Select(t => new {tName = t.tName, ClassName = t.TeachClass.cName}).ToList();

The generated SQL statement is as follows:

SELECT     1 AS [C1],     [Extent1].[tName] AS [tName],     [Extent2].[cName] AS [cName]    FROM  [dbo].[Teacher] AS [Extent1]    LEFT OUTER JOIN [dbo].[TeachClass] AS [Extent2] ON [Extent1].[tClass] = [Extent2].[cId]