Mvc3 + ef4.1 Learning Series (9) -- ef4.1 use of other skills

Article index and introduction

In the previous section, we implemented it through a series of simple reconstruction projects. However, some EF functions have not been discussed. In this section, we will talk about EF's other functions and skills through the project.

1. directly execute SQL statements

In general, EF does not need to write SQL statements, but in some cases, for example, it is not satisfied with the generated SQL statements to be optimized or it is necessary to write abnormal SQL statements during report statistics.

In this case, the vulnerabilities of Using ORM are revealed in batch operations. However, as an excellent ORM framework, EF provides three methods to support the original SQL.

1.Dbset. sqlquery query with tracking status


2.Dbdatabase. sqlquery queries with no trace status


3.Dbdatabase. sqlcommand directly executes SQL statements, which are generally used for batch addition, deletion, and modification.

Next let's take a look at the usage

Add a method to the public resource library in the previous article

   Public     Virtual Ienumerable  <  Tentity  >  Getwithrawsql (  String  Query,  Params     Object  [] Parameters)
{
  Return  Dbset. sqlquery (query, parameters );
}


  Public     Virtual Ienumerable  <  Tentity  >  Getwhithdbsql (  String  Query,  Params     Object  [] Parameters)
{
  Return  Context. database. sqlquery  <  Tentity  > (Query, parameters );
} 

The difference between the first and the second is described through comparison ~~

Execute these two methods in the course controller.

 VaR Query  =     "  Select * from course where courseid = @ P0  "  ;
Course course  =  Unitofwork. courserepository. getwithrawsql (query, ID). Single ();
Entitystate state  =  Unitofwork. getstate (course );
 String  Name  =  Course. Department. Name;

Course coursetwo  =  Unitofwork. courserepository. getwhithdbsql (query, ID). Single ();
Entitystate statetwo  =  Unitofwork. getstate (coursetwo );
  String  Nametwo  =  Coursetwo. Department. Name; 

Result After execution

1. First, we can see that the first State is "unchanged", which means that he has added the trace state to the context.

And the following can be read smoothlyCourse. Department. Name, that is, the navigation attribute can be read.

2. Check that the second State is that detached is not addedNo trace status in context Context

Therefore, the following error will be reported when reading the content of the navigation attribute ~~

3. Let's talk about it again.Dbdatabase. sqlcommand: This is relatively simple to add, delete, and use. This is good. Here is an example of batch update ~

If you need to update all course credits to N, it will be inconvenient to use the ORM framework in this batch operation. In this case, we can use this to directly execute SQL statements.

This business belongs to the course, so it inherits the general resource library class.CodeAs follows:

 Using  System;
  Using  Contosouniversity. models;

  Namespace Contosouniversity. dal
{
  Public     Class  Courserepository: genericrepository  <  Course  >  
{
  Public  Courserepository (schoolcontext context)
:  Base  (Context)
{
}

  Public    Int  Updatecoursecredits (  Int  Multiplier)
{
  Return  Context. database. executesqlcommand (  "  Update course set credits = credits * {0}  "  , Multiplier );
}

}
} 

Modify the previousUnitofwork class

 Private  Courserepository;

  Public Courserepository
{
  Get  
{

  If  (  This  . Courserepository  =     Null  )
{
  This  . Courserepository  =     New Courserepository (context );
}
  Return  Courserepository;
}
} 

The Controller is as follows:

PublicActionresult updatecoursecredits (Int?Multiplier)
{
If(Multiplier! = Null)
{
Viewbag. rowsaffected=Unitofwork. courserepository. updatecoursecredits (multiplier. value );
}
ReturnView ();
}
 

Add a view ~~

Running result Diagram

Ii. No trace Query

What is tracing query enabled for EF by default? Let's take a look at the following code.

 Department duplicatedepartment  =  DB. analytics
. Where (d  =>  D. instructorid  =  Department. instructorid)
. Firstordefault ();

Duplicatedepartment. Name  =     "  Wuhawuha  "  ;

 Bool  Isupdate  =  DB. Entry  <  Department  >  (Duplicatedepartment). Property (P  =>  P. Name). ismodified; // whether to modify
  String  Now  =  DB. Entry  <  Department >  (Duplicatedepartment). Property (P  =>  P. Name). currentvalue; // Current Value
  String  Before  =  DB. Entry  <  Department  >  (Duplicatedepartment). Property (P  =>  P. Name). originalvalue; // previous value 

We first randomly find out a department information and modify his name. This is what we will use the above information to get the current value. The previous value. This is what we do. Let's see what we didn't do.

It is easy to add a trace-free configuration during query.Asnotracking ()You can. After adding the parameter, we can see that it has changed but isupdate is still false. In addition, an exception is thrown when the previous value is obtained.

In general, we do not need to track these statuses. we can remove the trace query during the query to improve the performance ~ I used to think that removing this will affect the use of navigation attributes, but it will not be affected after testing ~

This tracing query also brings about another problem. See the figure below.

This is because an error is reported if you add a statement before the modification to query the modification and then modify it.An object with the same key already exists in objectstatemanager. Objectstatemanager cannot trace multiple objects with the same key.

When this error occurs, addAsnotracking ()You can. At present, I do not like tracing when using EF ~~ Like to addAsnotracking ()

3. Use proxy

After understanding the orm framework, we know that the navigation attribute uses the proxy mode, which is also related to the navigation attribute. Disabling proxy can speed up serialization.

The code for disabling proxy adds the following code to the class that inherits dbcontext

 
PublicSchoolcontext ()
{
This. Configuration. proxycreationenabled= False;
}
 

The following shows the differences between agent and agent.

Our administrator is a navigation attribute. We can see a long string of numbers and letters. When we access this administrator attribute below, we will get the Administrator attribute through delayed loading.

Let's look at the figure when the proxy is disabled.

If all of our navigation attributes are null, the navigation attributes cannot be obtained correctly.

Iv. Automatic Detection

• Dbset. Find
• Dbset. Local
• Dbset. Remove
• Dbset. Add
• Dbset. Attach
• Dbcontext. savechanges
• Dbcontext. getvalidationerrors
• Dbcontext. Entry
• Dbchangetracker. Entries

In the preceding method, the automatic detection function is called. This feature is enabled by default. we can disable this feature during batch operations to improve performance. For example:

 Using  (VAR Context  =     New Unicornscontext ())
{
  Try  
{
Context. configuration. autodetectchangesenabled  =     False  ;

  //  Make calls callin a loop  
    Foreach  (VAR unicorn  In Myunicorns)
{
Context. Unicorns. Add (UNICORN );
}
}
  Finally  
{
Context. configuration. autodetectchangesenabled  =     True  ;
}
} 

This automatic detection is detecting something that I have not studied myself and want to know ~~

Please refer to this msdn Automatic Detection Introduction

5. Learn more about EF.

1. Fluent API method definition and database ing

2. Use features to map to databases

3. Introduction to some EF conventions

4. EF team blog

5. Julie Lerman's blog

6. EF performance optimization suggestions

7. Analyze EF database activities

Vi. Summary

The content of the original article has been fully written with your own understanding.

Later, I wrote my own things. Next I wrote EF to process the tree structure ~~

Oh ~ By the way, paste the original text

I was totally out of the picture ~~ Don't post it ~