Mvc ef SQL statement

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.

In addition, the course. Department. name can be read to the navigation attributes.

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:

 
Public actionresult updatecoursecredits (Int? Multiplier)
{
If (multiplier! = NULL)
{
Viewbag. rowsaffected = unitofwork. courserepository. updatecoursecredits (multiplier. value );
}
Return view ();
}
 

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. Departments
. 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; // The 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 asnotracking () to the query without tracking. After adding this, we find that the isupdate is still false even though it is obviously changed. 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, add asnotracking () to the query. Currently, I do not like tracing when using EF ~~ Like adding asnotracking ()

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

 
Public schoolcontext ()
{
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 your cballs in a loop
Foreach (VAR Unicorn in myunicorns)
{
Context. Unicorns. Add (UNICORN );
}
}
Finally
{
Context. configuration. autodetectchangesenabled = true;
}
}