"Recommended" beginner. NET programmer, you must know the EF knowledge and experience

Read Catalogue

• Recommended Miniprofiler Plugins
• Data preparation
• Sink in a foreach loop
• AutoMapper Tools
• Query statistics of the Union table
• Asnonunicode of performance improvement
• Asnotracking of performance improvement
• Multi-field combination sorting (string)
• LAMDBA condition Combination
• Warm-up of EF
• COUNT (*) Have you broken it (use of any)
• Transparent identifiers
• entityframework.extended
• Custom IQueryable Extension Methods

[The article has been cursed.] After the top look, will raise wages oh:)]

Note: The EF6.0 version, code First mode, is used by default if not specifically stated below .

Recommended Miniprofiler Plugins

工欲善其事, its prerequisite.

We use EF and have greatly improved the speed of development, but with this comes a lot of poorly written writing and less efficient SQL generation.

While we can use SQL Server Profiler to monitor the execution of SQL, the individual feels it is troublesome to open, filter, clear, and close each time.

A plugin Miniprofiler is strongly recommended here. Real-time monitoring of page requests corresponding to the execution of the SQL statement, execution time. Simple, convenient, targeted and strong.

(Please visit for specific use and introduction)

Data preparation

New entity: Score (score table), Student (student table), Teacher (Teacher's Watch)

The demo code download link will be given later

Sink in a foreach loop

1. About Lazy Loading

Please look at the red box. Why StudentID have a value, and Studet is null? Because using code first, you need to set the navigation property to virtual to load the lazy load data.

2. Exception handling for accessing navigation properties in a loop (then above, plus virtual will report the following exception)

"There is already an open DataReader associated with this Command, and it must be closed first." "

Solution:

• Scenario 1, setting ConnectionString plus multipleactiveresultsets=true, but only applicable to later versions of SQL 2005
• Scenario 2, or read-ahead placement in list

3. The above two points only for warm-up, we say the trap has just begun!

Then we click to open the Miniprofiler tool (don't be frightened)

Solution: Use include to display the connection query (note: You need to import the using System.Data.Entity manually or the include can only pass the table name string).

Look at the monitoring of Miniprofiler (instant 101 SQL into 1, the performance of which can be imagined. ）

AutoMapper Tools

The connection query for the execution table shown above is obviously good, but not enough. If we only need to query some fields of the data, it is not a waste of memory storage space and application and database data transfer bandwidth to query all the fields above.

We can:

corresponding to the monitored sql:

We see the generated SQL and the query fields are a lot less. Only we show the list of fields and a studentid,studentid used to connect the query criteria.

Yes, that's a good way to do it. But is there any better plan or way? The answer is yes. (Otherwise, it's not going to be a fart here.) If you have a lot of table fields and we need to use a lot of field and a lot of navigation properties, this kind of manual mapping doesn't look so good. So let's start by introducing the use of AutoMapper to complete the mapping:

Note: You will need nuget to download AutoMapper first.

We see that there are no manual mappings for the above query statements, and the mappings are configured independently. Where Createmap should be written into the Global.asax file. (In fact, it is the separation of the mapping section, clear query statements.) The careful classmate may have noticed, this way also eliminates the active include)

We saw that the generated SQL was slightly different from the previous one, but only a single SQL was generated and the results were correct. (In fact, there is one more case when ([extent2].[] Id] is isn't NULL) then 1 END as [C1]. It seems that this statement doesn't really make sense, but this is the SQL generated by AutoMapper, and I don't understand why it's different from EF generation.

The benefits of doing so?

1. Avoid accessing navigation properties in loops to execute SQL statements more than once.
2. Avoids too many manual mappings in the query statement, which affects the reading of the code.

Some 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

Query statistics of the Union table

Requirements: Check the first 100 student test types ("Mock test", "formal examination"), test number, language average, student name, and the number of exams is more than 3 times. (classified by exam type)

The code is as follows:

Seeing this code, my first reaction was miserable. Again in the loop execution of SQL. Monitoring is as follows:

In fact, we only need to change the 101 SQL into 1, as follows:

Change 1 at once.

We open to view detailed SQL statements

Found that this is just a collection of query results, which according to the test type of statistics is the program to get all the data in the calculation (rather than in the database, and then directly return the results), this is also a waste of database query data transmission.

About join query Grouping statistics We can use SelectMany, as follows:

Monitor SQL as follows: (Is it more concise?) ）

About SelectMany Information:

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

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

Asnonunicode of performance improvement

The SQL that is monitored

We see that the SQL generated by EF normally takes "n" in front, and if we add Dbfunctions.asnonunicode generated SQL is not "n", when you find that the SQL with "n" is less than the "n" When SQL queries are much slower, you know what to do.

(Previous use of Oracle with no "N" query efficiency difference is particularly obvious, today with SQL Server testing did not find any difference.) And I found that EF6 will generate a sql,oracle database with "N" based on the nvarchar in the database. No test, interested classmates can test it)

Asnotracking of performance improvement

We look at the generated SQL

SQL is generated exactly the same, but the execution time is 4.8 times times. The reason is simply that the first EF statement adds an extra asnotracking.

What's asnotracking doing? No tracking query, that is, the objects queried can not be directly modified. Therefore, we do the data collection query display, and do not need to modify the collection and update to the database, we must not forget to add asnotracking.

Multi-field combination sorting (string)

Requirements: Query names with "Zhang San" students, first sorted by first name, and then by age sort.

Gee, that's not right. Sorted by name is covered by the age sort. We should use ThenBy to assemble the sort.

Yes, that's exactly what we want. If you don't want to use ThenBy and are all ascending, we can also:

The generated SQL is the same. With the order, ThenBy corresponding descending has orderbydescending, thenbydescending.

It looks like it's perfect. In fact, most of our situations are dynamic in sort. For example, we will be more front page different operations require different sorts of fields. So what do we do backstage?

Of course, this is fine, if you want to. Can so many possible judgments have been felt very SB? Yes, we certainly have a better solution. If you can pass the string directly???

Solution:

1. Guget Download System.Linq.Dynamic
2. Import System.Linq.Dynamic namespaces
3. Write an extension method by

Then the long, smelly code above can be written as:

Let's look at the generated SQL:

And we want the effect is completely in line with, is not feeling Meimei!!

LAMDBA condition Combination

Requirements: According to the different circumstances of the inquiry, the possible situation

1. Find all students in Name= "Zhang San"
2. Query Name= "Zhang San" or all students of age=18

Implementation code:

Does it smell the same? Here we have the flexibility to assemble lamdba conditions.

Solution:

This code I also stole from the Internet, the specific link can not be found.

Then our code can be written as:

There is no Meimei point. Then we'll see if the generated SQL is correct:

Warm-up of EF

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

COUNT (*) Have you broken it (use of any)

Requirements: Query whether there is a student named "Zhang San". (How does your code write?) ）

The first kind? The second kind? The Third Kind? Oh, I used to be the first type, and then someone said "you count is broken by you", and then I thought about how it was I used to break it? I know until I compare the performance of these three statements.

The performance of the difference is more than 300 times, count is really I use bad. (I guess I'm not the only one who has broken it.) ）

What do we see on the above? The official explanation is:

I have been reading this Chinese explanation for a long time and I can't understand it. Even earlier, some people have asked the same question, "I can't read MSDN's interpretation of any."

So my personal understanding is also "determine if there are elements in the collection that meet a certain condition." Let's take a look at any other usage:

Requirements: Query teachers who have taught "Zhang San" or "John Doe"

Implementation code:

Two ways, before I would get used to writing the first kind. Of course, when we look at the generated SQL and execution efficiency, the view changes.

The difference in efficiency is nearly six times times.

We'll compare the count:

Come to a strange conclusion:

1. The use of count in the navigation properties and the use of the any performance difference is not small, but firstordefault () = NULL in the way of the worst performance.
2. In the direct attribute judgment, any and firstordefault ()! = NULL performance differs little, and the count performance is much worse.
3. So, whether it's a direct property or a navigation property, we use any to determine if the existence is the surest.

Transparent identifiers

If for various reasons we need to write the following logical statement

We can write it so much better

Look at the generated SQL and you'll know.

The second way to generate SQL is much cleaner and better performance.

entityframework.extended

Here recommend the next plugin entityframework.extended, looked under, very good.

The biggest highlight is that you can directly batch modification, delete, not like the EF default needs to do the first query operation.

As for why the official EF did not provide such support, I do not know. However, there are several points to note when using entityframework.extended:

1. Supports SQL Server only
2. Transactions cannot be implemented in bulk modification or deletion (that is, exceptions cannot be rolled back)
3. No cascade Delete

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

Custom IQueryable Extension Methods

Finally, organize the extensions of the custom IQueryable.

SOURCE Download: HTTP://PAN.BAIDU.COM/S/1O8MYOZW

Article First chain: http://www.cnblogs.com/zhaopei/p/5721789.html

Thank you for your reading, if you learn, please like (code word is not easy)!

Welcome to the Garden friends to add!

"Recommended" beginner. NET programmer, you must know the EF knowledge and experience