Entity Framework Quick Start-one-to-one link Processing

I haven't updated my blog for a long time, just taking advantage of the empty space during the Dragon Boat Festival. I will summarize my previous experiences in EF and work.

In this article, I will share with my friends some notes on how to use the 0 .. 1 object model, including basic addition and query.

First, the edmx entity model:

The model is simple. It is a user entity and an entity associated with user information. In some cases, we have such a requirement: user login authentication, and every time we query user information, we often query the user table, at this time, we only use two or three fields, that is, whether the user account and password are consistent. If they are consistent, true is returned; otherwise, false is returned, and many other fields are hung on the user table, this results in unnecessary performance losses. Of course, it doesn't matter if the traffic volume is small. Sometimes we place some fields that can be null or are often not used in a userinfo table, while frequently accessed and verified user forms only generate a small table, used for authentication and other services. For example, edmx design is only a simulated example.

Based on the relationship between 1-to-0 and 1, we can also think of it by ourselves, so when we persist user information to the database, the user information table entity (userinfo) it must be attached to a user entity before it can be persisted to the database. The User table is not affected by the userinfo table. You can insert a data record separately. See the following:CodeBoth can be correctly executed:

Insert user object data separately:

 Static     Void  Insertdata ()
{
 Using  (Companycontainer  =     New  Companycontainer ())
{
User user  =     New  User ();
User. Code  =     "  Flydragon  "  ;
User. Name =     "  Yunlong Tianjing  "  ;
User. Password  =     "  Haha  "  ;
Companycontainer. User. addobject (User );
Companycontainer. savechanges ();
}
} 

We can also save a whole piece of data at the same time, as shown in the following code: insert user + userinf Entity Data

 Static     Void Insertfulldata ()
{
  Using  (Companycontainer  =     New  Companycontainer ())
{
User user  =     New  User ();
User. Code  =     "  Flydragon3  " ;
User. Name  =     "  Yunlong Tianjing 3  "  ;
User. Password  =     "  Haha3  "  ;
Companycontainer. User. addobject (User );

Userinfo  =     New Userinfo ();
Userinfo. Email  =     "  Malun666@hotmail.com  "  ;
Userinfo. Address  =     "  Beijing Shangdi  "  ;
Userinfo. Phone  =     " 110  "  ;
Userinfo. User  =  User;
Userinfo. rmark  =     ""  ;
Companycontainer. userinfo. addobject (userinfo );
Companycontainer. savechanges ();
}
} 

There is nothing to say above, and there is no good idea. Of course, many details are still encountered in the project. Of course, one of the headaches is the bug of an abnormal query of 1 to 0... 1 relational model data. When we query user object data, the SQL script generated by EF automatically performs left out join on the userinfo table. In many cases, we only retrieve the data in the User table, but also perform the join operation. This allows us to perform the join operation to improve the efficiency of User table data query, the data volume is queried in the result of the Cartesian product of the two tables. Check the following code and the SQL statement tracked by sqlprofile:

 Static    Void  Main (  String  [] ARGs)
{
Companycontainer  =     New  Companycontainer ();
VaR result  =  Companycontainer. User. Where  <  User  >  (U  => U. Code  =     "  Flydragon3  "  )
. Where  <  User  >  (U  =>  U. Password  =     "  Haha3 "  )
. Firstordefault  <  User  >  ();
  If  (Result  ! =     Null  )
{
Console. writeline (result. Name );
}
Console. readkey ();
} 

The following is the execution SQL script tracked by the sqlprofile tool:

Obviously, we can see a join operation between the user table and the userinfo table. I guess the details of EF's internal implementation may be that when the user entity is initialized, the relationship between the navigation property userinfo is 1 to 0 .. 1. Special processing is performed, and the userinfo data is retrieved to initialize the navigation property userinfo.

This requires you to pay attention to this detail when using EF.

So how can we avoid such a result:

The following methods prevent this external connection:

For example:

 Static     Void  Main (  String  [] ARGs)
{
Companycontainer  =     New  Companycontainer ();
VaR result  =  Companycontainer. User. Where  < User  >  (U  =>  U. Code  =     "  Flydragon3  "  )
. Where  <  User  >  (U  =>  U. Password =     "  Haha3  "  ). Count ();
Console. writeline (result );
Console. readkey ();
} 

The corresponding SQL statement is:

 Select   
  [  Groupby1  ]  .  [  A1  ]    As     [  C1  ]  
  From  (  Select   
  Count  (  1  )  As     [  A1 ]  
  From     [  DBO  ]  .  [  User  ]     As     [  Extent1  ]  
 Where  (N  '  Flydragon3  '     =     [  Extent1  ]  .  [  Code  ]  )  And  (N '  Haha3  '     =     [  Extent1  ]  .  [  Password  ]  )
)  As     [  Groupby1 ] 

In addition, how to query only the user and retrieve several fields in the User table is as follows:

 Static void main (string  []  ARGs)
{
Companycontainer  =  New companycontainer ();
  VaR  Result  =  Companycontainer.  User  .  Where  <  User >  (U  =>  U. Code  =  "Flydragon3 ")
.  Where  <  User  >  (U  =>  U. Password  =  "Haha3 ")
.  Select  (U =>  New {ID  =  U. ID, name  =  U. name}). firstordefault ();
  If  (Result  ! =     Null  )
{
Console. writeline (result. Name );
}
Console. readkey ();
} 

The final generated SQL script is:

 Select     Top (  1  )
  [  Extent1  ]  .  [  ID  ]     As     [  ID  ]  ,
  [ Extent1  ]  .  [  Name  ]     As     [  Name  ]  
  From     [  DBO  ] .  [  User  ]     As     [  Extent1  ]  
  Where  (N  '  Flydragon3  '     =    [  Extent1  ]  .  [  Code  ]  )  And  (N  '  Haha3  '     =     [ Extent1  ]  .  [  Password  ]  ) 

WriteArticleTime-consuming and laborious! I hope everyone can share their knowledge and make progress together! Happy Dragon Boat Festival!

Entity Framework Quick Start-index stickers