EntityFramework4.1 development, entityframework

EntityFramework4.1 development, entityframework

There are several common problems

I. ef4.1 codeFirst: to modify the table structure, add fields, and so on. EF code first needs to regenerate the database, causing data loss.

2. ef4.1 without complicated things such as edmx, it becomes simple and clean. But how can we use stored procedures? stored procedures can return tables, return values, or execute modifications, deletions, and additions. How can we do this?

Iii. How does ef4.1 use the database view? Should a corresponding object class be created for each view? Is there a simple method?

4. How does ef4.1 perform SQL functions?

5. How does ef4.1 access data across databases?

6. ef4.1 execute connection query? When will the left join be executed? When will I execute an internal connection? What does ef determine based on?

7. Some common error messages for beginners to use ef4.1

In fact, these problems are relatively simple ~ Therefore, this article is for beginners who have just used ef4.1 ~

Next we will start to solve these problems one by one.

　　I. ef4.1 codeFirst: how to modify the table structure and add fields. EF code first needs to regenerate the database, leading to data loss.

Before talking about this question, let me first explain how to use ef4.1 codefirst. it is because there can be purer POCO and there is no longer EDMX, instead of actually using code first to generate a database. so although I use

It is codefirst, but in essence it is still the database priority.

Therefore, the solution to this many problems is actually very simple. as long as your database already exists, even if you use code first ef, it will not generate a database for you. when you add table fields or even add tables

If the object class is modified accordingly, the data in the database will not be cleared.

Let's talk about my development steps. First, design a database and create a database. Then, use the EF tool to generate ing and object classes. Then, use the development code to modify the database, such as adding fields or tables. => modify the object class again => continue development

In this way, there will be no worries about re-generating data, and edmx will not appear in the project ~

　　2. ef4.1 without complicated things such as edmx, it becomes simple and clean. But how can we use stored procedures? stored procedures can return tables, return values, or execute modifications, deletions, and additions. How can we do this?

Before talking about this question, I would like to talk about my point of view. I personally think that since the orm framework is used, we should put the business logic and so on at the business logic layer, instead of using the stored procedure. I am more inclined to develop business logic layer-light stored procedures ~

In ef4.0, it is easier to add the stored procedure with edmx when you call the stored procedure. But in ef4.1, only the clean poco does not have edmx. What should I do? In particular, stored procedures can be used to query values in the table or perform modification and deletion.

One by one

1. Execute the stored procedure of the returned table Type

First, the stored procedure is the simplest

Create PROCEDURE [dbo].[ProSelectStu]
    @StudentID int
AS
BEGIN

Select Student.* from Enrollment,Student 
where Enrollment.StudentID=Student.StudentID
and Enrollment.StudentID=@StudentID
 
END

GO

The method for executing the stored procedure is to execute SQL directly. I have provided a detailed introduction in the ninth article of my article ~ You can check it out first.

Create procedure [dbo]. [ProSelectCount]
@ StuId int
AS
BEGIN
Select COUNT (*) from Enrollment where StudentID = @ StuId
END

Number of simple queries

In this example, SQL query is used to access the database. Because we need to provide the return type and we return the int type, we get the int type first.

Create procedure [dbo]. [ProDel]
@ StuId int,
@ CourseId int
AS
BEGIN

Delete from [WLFSchool]. [dbo]. [Enrollment]
Where StudentID = @ stuId and CourseID = @ courseId

END

This operation uses the database to return the affected number of rows.

From c in classes
From s in students
Where c. ClassID = s. ClassID
Order by c. CreateTime
Select new
{
Name = s. Name,
Age = s. Age,
ClassName = c. ClassName
};

Using var result to accept the above values, we don't need to create a database view, and no more entity classes, isn't it easy?

If the company's powerful DBA has already built many views for us, is it necessary to write entity classes one by one? If you are using C #4.0, you can solve this problem dynamically ~

Is it nice to use it like below?

Public static class DatabaseExtensions
{
Public static IEnumerable SqlQueryForDynamic (this Database db,
String SQL,
Params object [] parameters)
{
IDbConnection defaultConn = new System. Data. SqlClient. SqlConnection ();

Return SqlQueryForDynamicOtherDB (db, SQL, defaultConn, parameters );
}

Public static IEnumerable SqlQueryForDynamicOtherDB (this Database db,
String SQL,
IDbConnection conn,
Params object [] 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 ())
{
Return null; // Null is returned If no result is returned.
}

# Region build 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 );
}
}

Public static 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;
}

Public static void CreateAutoImplementedProperty (
TypeBuilder builder,
String propertyName,
Type propertyType)
{
Const string PrivateFieldPrefix = "m _";
Const string GetterPrefix = "get _";
Const string 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
// Ld1_, _ field
// Ret
ILGenerator getterILCode = getterMethod. GetILGenerator ();
GetterILCode. Emit (OpCodes. Ldarg_0 );
GetterILCode. Emit (OpCodes. lddes, 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. stdes, fieldBuilder );
SetterILCode. Emit (OpCodes. Ret );

PropertyBuilder. SetGetMethod (getterMethod );
PropertyBuilder. SetSetMethod (setterMethod );
}
}

　4. How does ef4.1 perform SQL functions?

Add reference System. Data. Objects. SqlClient. SqlFunctions mainly for this namespace

Usage ~ Example in the previous job ~

var query = from s in student.T_StudentInfo
                        where SqlFunctions.DateDiff("day", s.CreateTime, "2011/11/4") == 0
                        select s.StudentName;

Use the datadiff function of SQL ~~

　5. How does ef4.1 access data across databases?

Every time someone asks me this question, I will not hesitate to post dudu's post ~ He has solved it well ~

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

Core Ideas spoofing SQL implementation by creating Synonyms
6. ef4.1 execute connection query? When will the left join be executed? When will I execute an internal connection? What does ef determine based on?

When we use Include to forcibly load multiple tables or select to query multiple tables, we find that the generated SQL statement is sometimes left join or inner join.

In fact, EF is determined based on whether the connection field of our object class can be empty ~ For example, the foreign key studentID

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

Whether it can be empty or not will result in left join or inner join ~~
　　7. Some common error messages for beginners to use ef4.1

1. An error occurred while executing the command definition.

This error is caused by many database statement errors. We can first check whether the SQL statement is sent to the database and then execute this SQL statement to check whether there is any problem.

The cause of this error is that the connection object is always occupied because EF has delayed loading, but the select operation is not actually executed in the database.

We can first execute the previous query statement tolist and then perform the following operations.

2.

System. Data. Edm. EdmEntityType: EntityType "Enrollment" does not define a key. Define a key for this EntityType.
System. Data. Edm. EdmEntitySet: EntityType: EntitySet, Enrollment is based on the undefined key type Enrollment.
In this case, try to add a [Key] to the primary Key.

3. An error occurred while updating the entries.

Check whether the database statements have foreign key constraints and cause insertion errors.