EntityFramework4.1 Development

Frequently asked questions about these several

I. ef4.1 Codefirst modifying table structure add fields such as EF code first need to rebuild the library causing data loss issues.

Two. ef4.1 without an edmx and other complex things to become simple but how to use stored procedures, stored procedures can return a table can return a numeric value is also possible to do modify delete increase, etc.

Three. ef4.1 how do I use the database view? Do you want to create a corresponding entity class for each view? Is there an easy way?

Four. ef4.1 how do I perform operations such as SQL functions?

Five. How does ef4.1 cross-database access?

Six. ef4.1 perform connection query? When do I perform a left connection? When do I perform an internal connection? What does EF judge by?

Seven. Novice use ef4.1 Some of the common error messages

In fact, these questions are relatively simple ~ so this text to just use ef4.1 of the Novice ~

Here's how to solve these problems

　　I. ef4.1 Codefirst modifying table structure add fields such as EF code first need to rebuild the library causing data loss issues

Before I say this question, first of all, I use ef4.1 codefirst purpose. is because there can be more pure poco that no longer has the edmx of these things instead of actually using code first to generate the database again. So though I use

is Codefirst but the essence is still the database first.

So a lot of the problem solving is actually very simple. As long as your database already exists, even if you use code First EF will not give you to build the database. This time you add table fields or even add tables just put

The entity classes are also modified accordingly, and the data in the database is not emptied.

Next, my development step is to design the database and build the database.-Generate mappings and entity classes with EF Tools + Develop code when it comes to modifying a database, such as adding a field or a table, and then modifying the entity class = + to continue developing

So there's no problem with regenerating the data and there's no edmx~ in the project.

　　Two. ef4.1 without an edmx and other complex things to become simple but how to use stored procedures, stored procedures can return a table can return a numeric value is also possible to do modify delete increase, etc.

Before I say this question, I would like to mention my point of view. Personally, since the ORM framework should be used to put business logic and so on to the business logic layer and should not use stored procedures. I am more emphasis on the business logic layer light storage process such development ~

Add stored procedures in ef4.0 it's easier to have an edmx tune the stored procedure is added but in ef4.1 only clean poco no longer have an edmx what to do? In particular, the stored procedure may be to check the table value or to perform a modification delete.

A one to solve

1. Executing a stored procedure that returns a table type

First on the stored procedure easy to write a simplest

Createprocedure [dbo]. [proselectstu 
 " Span style= "color: #008000;" > @StudentID int 
 as 
 begin 
 
 select student.from enrollment,student 
 where Enrollment.studentid=student.studentid 
 and Enrollment.studentid= @StudentID 
 
 end 
 
 go          

The way to execute a stored procedure is by directly executing SQL I have a detailed introduction in the Nineth article of my article ~ everyone can go first to see

The stored procedure for executing the table is actually very powerful delay loading and so on have reflected the blog Park LU teacher has written a very clear ~ i here no longer write you can go to him that look under the provision of a connection ~

EF uses stored procedures to query the table's

2. Executing a stored procedure that returns a value

First on the stored procedure

[dbo]. [Proselectcount]
Int
As
BEGIN
COUNT (where StudentID=@StuId
END

A simple number of queries

Here we use SQLQuery to access the database because we need to provide a return type and we return an int so we get the type of int first.

3. Perform additions, deletions and changes

CREATEPROCEDURE[Dbo].[Prodel]
 @stuIdInt
 @courseId int
As
BEGIN

DELETE from [wlfschool].  [dbo]. [Enrollment]
where StudentID=@stuId and courseid=@courseId

END                    

This uses the operations database to return the number of affected rows

　　Three. ef4.1 how do I use the database view? Do you want to create a corresponding entity class for each view? Is there an easy way?

First of all, the most traditional method is simply to create a corresponding entity class for the view as a table and then add it to the DbContext. No difficulty.

One more question. Using LINQ has a very beautiful function of projection mapping and c#3.0 anonymous functions so that we do not need the view of many cases

In classes
In students
where C.classid = = S.classid
ORDER BY C.createtime
New
{
     Name = S.name,
     Age = S.age,
     ClassName = C.classname                                 
};

Then we can accept the above value by Var result so that we don't have to go to the database to build the view without building the entity class is it very convenient?

If the company's powerful DBA has already built many views for us, is it necessary to write entity classes? If you are using c#4.0 then you can use the dynamic to solve the problem ~

Is it cool to use it like this?

This can not only query the view ordinary table as long as the SQL statement can automatically generate dynamic classes let you use ~

Here are the extension methods and the use of emit to dynamically build thanks to the help of ASP.

PublicStaticClass Databaseextensions
{
    PublicStatic IEnumerable sqlqueryfordynamic (This Database db,
 String sql,
 Paramsobject[] Parameters)
{
IDbConnection defaultconn =New System.Data.SqlClient.SqlConnection ();

 Return SQLQUERYFORDYNAMICOTHERDB (DB, SQL, defaultconn, parameters);
}

 PublicStatic IEnumerable Sqlqueryfordynamicotherdb (This Database db,
 String sql,
IDbConnection Conn,
 Paramsobject[] Parameters)
{
Conn. ConnectionString = db. connection.connectionstring;

 IF (Conn. State = ConnectionState.Open)
{
Conn. Open ();
}

IDbCommand cmd = conn. CreateCommand ();
Cmd.commandtext = SQL;

IDataReader dataReader = cmd. ExecuteReader ();

 if (!datareader.read ())
{
 ReturnNull//No result returned null
}

 #region Building Dynamic Fields

TypeBuilder builder = Databaseextensions.createtypebuilder (
 "ef_dynamicmodelassembly",
 "DynamicModule",
 "DynamicType");

 int fieldcount = Datareader.fieldcount;
 for (int i =0; i < FieldCount; i++)
{
 //Dic. ADD (i, Datareader.getname (i));

 //Type type = Datareader.getfieldtype (i);

Databaseextensions.createautoimplementedproperty (
Builder
Datareader.getname (i),
Datareader.getfieldtype (i));
}

 #endregion

Datareader.close ();
Datareader.dispose ();
Cmd. Dispose ();
Conn. Close ();
Conn. Dispose ();

Type ReturnType = Builder. CreateType ();

 if (Parameters! =Null
{
 Return DB. SQLQuery (returntype, SQL, parameters);
}
 Else
{
 Return DB. SQLQuery (returntype, SQL);
}
}

 PublicStatic TypeBuilder Createtypebuilder (String AssemblyName,
 String ModuleName,
 String typeName)
{
TypeBuilder TypeBuilder = AppDomain.CurrentDomain.DefineDynamicAssembly (
 New AssemblyName (AssemblyName),
Assemblybuilderaccess.run). DefineDynamicModule (ModuleName). DefineType (TypeName,
Typeattributes.public);
Typebuilder.definedefaultconstructor (Methodattributes.public);
 return typeBuilder;
}

 PublicStaticvoid Createautoimplementedproperty (
TypeBuilder Builder,
 String PropertyName,
Type PropertyType)
{
 ConstString Privatefieldprefix ="M_";
 ConstString Getterprefix ="get_";
 ConstString Setterprefix ="set_";

 //Generate the field.
FieldBuilder FieldBuilder = Builder. DefineField (
 String. Concat (
Privatefieldprefix, PropertyName),
PropertyType,
Fieldattributes.private);

 //Generate the property
PropertyBuilder PropertyBuilder = Builder. DefineProperty (
PropertyName,
System.Reflection.PropertyAttributes.HasDefault,
PropertyType,NULL);

 //Property getter and Setter attributes.
MethodAttributes propertymethodattributes = Methodattributes.public
| MethodAttributes.SpecialName
| Methodattributes.hidebysig;

 //Define the Getter method.
MethodBuilder Gettermethod = Builder. DefineMethod (
 String. Concat (
Getterprefix, PropertyName),
Propertymethodattributes,
PropertyType,
Type.emptytypes);

 //Emit the IL code.
 //ldarg.0
 //Ldfld,_field
 //Ret
ILGenerator Getterilcode = Gettermethod.getilgenerator ();
Getterilcode.emit (OPCODES.LDARG_0);
Getterilcode.emit (OPCODES.LDFLD, FieldBuilder);
Getterilcode.emit (Opcodes.ret);

 //Define the Setter method.
MethodBuilder Settermethod = Builder. DefineMethod (
 String. Concat (Setterprefix, PropertyName),
Propertymethodattributes,
 null, 
 new type[] {propertyType}); 
 
 // Emit the IL code. 
 // ldarg.0 
 // Ldarg.1 
 // Stfld,_field 
 // Ret 
 ILGenerator Setterilcode = Settermethod.getilgenerator (); 
 Setterilcode.emit (OPCODES.LDARG_0); 
 Setterilcode.emit (opcodes.ldarg_1); 
 Setterilcode.emit (OPCODES.STFLD, FieldBuilder); 
 Setterilcode.emit (Opcodes.ret); 
 
 Propertybuilder.setgetmethod (gettermethod); 
 Propertybuilder.setsetmethod (Settermethod); 
} 
}             

　Four. ef4.1 how do I perform operations such as SQL functions?

Add Reference System.Data.Objects.SqlClient.SqlFunctions is primarily this namespace

How to use ~ Examples from previous work ~

in student. T_studentinfo
                        where Sqlfunctions.datediff ("day"2011/11/40
Select S.studentname;

Using SQL's Datadiff function ~ ~

　Five. How does ef4.1 cross-database access?

Every time people ask me this question do not hesitate to the Webmaster Dudu article sent past ~ He has been very good to solve the ~

Http://www.cnblogs.com/dudu/archive/2011/03/29/entity_framework_cross_database_query_fact.html

The core idea of cheating SQL using create synonyms to implement
Six. ef4.1 perform connection query? When do I perform a left connection? When do I perform an internal connection? What does EF judge by?

When we do a multi-table query with include force loading or select to query, we find that the generated SQL statement is sometimes a left connection and sometimes a inner join.

In fact, EF is based on whether the connection field of our entity class can be empty to determine the ~ such as foreign key StudentID

Public nullable<int> StudentID {get; set;}

Whether it can be empty will cause a left join or a inner join~~
　　Seven. Novice use ef4.1 Some of the common error messages

1. An error occurred while executing the command definition

There are many database statement errors that occur because of this error we can start by monitoring whether the SQL statement is sent to the database and then execute the SQL statement to see if there is a problem

The reason for this error is that the connection object is always occupied because EF has deferred loading just select when not really going to the database execution

We can first tolist the previous query statements and so on to perform the following operations

2.

System.Data.Edm.EdmEntityType:: EntityType "Enrollment" has no key defined. Please define the key for the EntityType.
System.data.edm.edmentityset:entitytype:entityset,enrollments is based on a type enrollment that does not have a key defined.
Try to add [key] to the primary key when you encounter this situation

3. Error updating entries

Still detects if the database statement has foreign KEY constraints resulting in insert errors, etc.

EntityFramework4.1 Development