You must know EF knowledge and experience, ef knowledge and experience.

[Switch] You must know EF knowledge and experience and ef knowledge and experience.

[Switch] EF knowledge and experience you must know

Note: If the following content is not stated, EF6.0 and code first modes are used by default.

Recommended MiniProfiler plug-in

To do well, you must first sharpen your tools.

We use EF to greatly improve the development speed, but it brings about a lot of low-performance writing methods and inefficient SQL generation.

Although we can use SQL Server Profiler to monitor the executed SQL statements, I personally think it is really troublesome to open, filter, clear, and close each time.

A plug-in MiniProfiler is strongly recommended here. Monitors the SQL statements and execution time corresponding to the page request in real time. Simple, convenient, and highly targeted.

(For specific usage and introduction, see Step 1)

Data Preparation

New Entity: Score (Score Table), Student (Student table), and Teacher (Instructor table)

The demo code download link will be provided later

Traps of foreach Loops

1. Delayed Loading

See the red box. Why does StudentId have a value while Studet is null? Because code first is used, you must set the navigation attribute to virtual to load delayed loading data.

2. Exception Handling for accessing navigation properties in a loop (the following exception will be reported after the above is added with virtual)

"A DataReader associated with this Command has been opened. You must first disable it. "

Solution:

• Solution 1: Set ConnectionString with MultipleActiveResultSets = true, but only applicable to Versions later than SQL 2005
• Solution 2, or read and place the data in the List first.

3. The above two points are just warm-up. The traps we mentioned are just getting started!

Then we click to open the MiniProfiler tool (Do not be scared)

Solution: UseIncludeDisplay connection query (Note: You must manually import using System. Data. Entity. Otherwise, only the table name string can be uploaded using Include ).

Let's look at the monitoring of MiniProfiler. (in an instant, the number of 101 SQL statements is changed to 1, and the performance can be imagined .)

AutoMapper Tool

The join query of the execution table shown through Include is obviously good, but not enough. If we only need to query some data fields, it is not a waste of memory storage space and data transmission bandwidth between applications and databases to query all the fields above.

We can:

Corresponding to the monitored SQL:

We can see that the generated SQL statements have fewer query fields. Only StudentId and StudentId listed in the field are displayed. StudentId is used to connect the query conditions.

Yes, this is a good method. But is there any better solution or method? The answer is yes. (Otherwise, it won't be the case here .) If there are many table fields, we need to use a lot of fields, and there are many navigation attributes, this manual ing will not look so nice. Next we will introduce how to use AutoMapper to complete ing:

Note: you must first download AutoMapper from NuGet. (Then import the namespace using AutoMapper; using AutoMapper. QueryableExtensions ;)

We can see that the preceding query statements do not have manual mappings, And the mappings are configured independently. CreateMap should be written to the Global. asax file. (In fact, The ing part is separated, and the query statement is clear. Careful students may have noticed that this method also removes the need for active Include)

We can see that the generated SQL is somewhat different from the previous one, but only one SQL is generated, and the result is correct. (In fact, there IS an additional case when ([Extent2]. [Id] is not null) THEN 1 end as [C1]. It seems that this statement has no practical significance. However, this is an SQL statement generated by AutoMapper, and I also don't understand why it is different from EF)

What are the benefits of doing so?

Other information about AutoMapper:

Http://www.cnblogs.com/xishuai/p/3712361.html

Http://www.cnblogs.com/xishuai/p/3700052.html

Http://www.cnblogs.com/farb/p/AutoMapperContent.html

Join Table query statistics

Requirements: query the first 100 student test types ("Simulated Test" and "formal test"), number of tests, average language score, and student name. The number of tests is greater than or equal to three. (Statistics by exam type)

The Code is as follows:

When I saw this code, my first response was miserable. The SQL statement is executed cyclically. Monitoring is as follows:

In fact, we only need to change the 101 SQL statements to one, as shown below:

Change 1 immediately.

Open to view detailed SQL statements

It is found that this is only a set of query results. The statistics based on the test type are calculated after the program obtains all the data (instead of computing in the database, and then directly returning the results ), this also wastes data transmission for database queries.

SelectMany can be used for connection query group statistics as follows:

The monitoring SQL is as follows: (is it more concise ?)

Information about SelectMany:

Http://www.cnblogs.com/lifepoem/archive/2011/11/18/2253579.html

Http://www.cnblogs.com/heyuquan/p/Linq-to-Objects.html

Performance Improvement-AsNonUnicode

Monitored SQL

We can see that the SQL statement generated by EF normally carries "N" in the front, if we add DbFunctions. the SQL statements generated by AsNonUnicode do not contain "N". When you find that the SQL statements with "N" are much slower than those without "N", you will know what to do.

(In the past, there was a particularly significant difference in query efficiency without "N" when using oracle. No difference was found in SQL server tests today. In addition, I found that EF6 will generate SQL statements with "N" only when the database is nvarchar. If you are interested, you can test the SQL statements)

Performance Improvement-AsNoTracking

We can see the generated SQL

The SQL statement is generated exactly, but the execution time is4.8 times. The only reason is that the first EF statement adds an AsNoTracking.

Note:

• What does AsNoTracking do? There is no trace query, that is, the queried objects cannot be directly modified. Therefore, when querying and displaying data sets without modifying the set and updating it to the database, do not forget to add AsNoTracking.
• AsNoTracking is not required if select ing is performed in the query process. For example, db. students. where (t => t. name. contains ("Zhang San ")). select (t => new (t. name, t. age )). toList ();

Multi-field combined sorting (string)

Requirement: query the students whose names contain "Zhang San". They are first sorted by name and then by age.

Sorry, no. The sorting by name is overwritten by age. We should use ThenBy to combine sorting.

It's good. It's exactly what we want. If you do not want to use ThenBy, and it is in ascending order, we can also:

The generated SQL statements are the same. OrderByDescending and ThenByDescending correspond to OrderBy and ThenBy in descending order.

It seems perfect. In fact, most of our cases, the sorting is dynamic. For example, different operations on the front-end page require different sorting of different fields. What should we do in the background?

Of course, it's okay if you want. Can we make so many possible judgments? Is it very SB? Yes, of course we have a better solution. If OrderBy can be directly transmitted as a string ???

Solution:

Then the long and smelly code above can be written:

Let's take a look at the generated SQL:

It is exactly the same as what we want. Do you feel pretty good !!

Note ]:The input sorting field is followed by the sorting keyword asc or desc.

Lamdba condition combination

Requirement: Query Based on different situations, where possible

Implementation Code:

Does it smell the same smell. Next we will flexibly assemble the Lamdba conditions.

Solution:

I also stole this code from the Internet. The specific link cannot be found.

Then our code can be written:

Is there any beauty. Then let's see if the generated SQL is correct:

EF push

Http://www.cnblogs.com/dudu/p/entity-framework-warm-up.html

Is count (*) used badly? (Any usage)

Requirement: Check whether a student named "Zhang San" exists. (How do you write your code ?)

First? Second? Third? Haha, I used to be the first one. Then someone said, "Your count is broken by you." Then I thought about how to break it down? I knew it after comparing the performance of the three statements.

Poor performanceMore than three hundred times, Count is actually broken by me. (I think it's not just me .)

Why did we see Any above? The official explanation is:

I have read this Chinese explanation repeatedly and have never understood it. Some people have even raised the same question: "I really don't understand MSDN's interpretation of Any."

Therefore, I personally understand that "determining whether an element in a collection meets a certain condition ". Let's take a look at other usage of any:

Requirement: query the teachers who have taught "Zhang San" or "Li Si ".

Implementation Code:

Two Methods: I used to write the first one. Of course, after looking at the generated SQL and execution efficiency, our views have changed.

Poor EfficiencyNearly six times.

Let's compare the count:

Come to a strange conclusion:

Transparent identifier

For various reasons, we need to write the following logic statement:

We can write it as this is better

Check the generated SQL.

The SQL statements generated in the second method are much cleaner and have better performance.

EntityFramework. Extended

The EntityFramework. Extended plug-in is recommended here. It looks good.

The biggest highlight is that you can directly modify and delete them in batches without performing query operations as required by EF by default.

Why does official EF not provide such support. Note the following when using EntityFramework. Extended:

Http://www.cnblogs.com/GuZhenYin/p/5482288.html

EntityFramework. Extended does not mean that rollback is not allowed. Thanks to @ GuZhenYin for your correction (forgive me for not performing a hands-on test before ).

Note: NuGet needs to download EntityFramework. Extended and import the namespace: using EntityFramework. Extensions;

The test code is as follows: (if an exception code is commented out, it can be directly updated to the database)

Using (var ctxTransaction = db. database. beginTransaction () {try {db. teachers. where (t => true ). update (t => new Teacher {Age = "1"}); throw new Exception ("manually throw an Exception"); ctxTransaction. commit (); // Commit transaction} catch (Exception) {ctxTransaction. rollback (); // Rollback transaction }}

Custom IQueryable Extension Method

Finally, sort out the custom IQueryable extensions.

 

 

Supplement 1:

The difference between First and Single: the former isTOP (1) the latter is TOP (2). If the latter queries two pieces of data, an exception is thrown. So when necessary, using Single is not much slower than First.

Supplement 2: 

The Package nuget allows you to directly Install-Package Talk. Linq. Extensions or nuget to search for Talk. Linq. Extensions.

Https://github.com/zhaopeiym/Talk/wiki/Talk.Linq.Extensions_demo

 

End:

Source code download: http://pan.baidu.com/s/1o8MYozw

This article synchronizes to C # basic knowledge consolidation Series

Thank you!