Common problem solving in Entity Framework

Blog wrote 10 of ~ ~ There are many friends DMS asked a number of questions, and many questions that everyone asked is the same here are the solutions to these common problems. If we have a better solution, we want to share it.

The problem is probably these few

I. ef4.1 Codefirst Modify table structure add fields such as EF code first need to rebuild the library to cause data loss problems.

Two. ef4.1 without edmx and other complex things become simple and clean but how to use stored procedures, stored procedures can return a table can return a number of values are also possible to perform changes to delete the increase and so how to do.

Three. ef4.1 How to use database views. Each view is going to create the corresponding entity class. Is there a simple way?

Four. ef4.1 how to perform operations such as SQL functions.

Five. How ef4.1 is accessed across databases.

Six. ef4.1 execute connection query. When to perform a left connection. When to perform an inner join. What is the EF to judge.

Seven. The novice uses ef4.1 common some error information

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

The following are the first to solve these problems

I. ef4.1 Codefirst Modify table structure add fields etc EF code first needs to rebuild the library causing data loss

Before you say this question, first of all, I use the purpose of ef4.1 Codefirst. Because there can be more pure Poco no longer have edmx these things instead of really using code first to generate the database. So I used it though

is Codefirst but the essence remains the database priority.

So many of the questions that are being asked are actually very simple. As long as your database is already there, you won't be able to generate the database even if you use code-EF. This time you add table fields or even add tables just put

The entity classes are also modified so that the data in the database is not emptied.

The next step is to design the database and build the database => through the EF tools to generate mappings and entity classes => Development code when you encounter changes => first modify the database such as adding fields or tables => then modify the entity class => continue to develop

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

Two. ef4.1 without edmx and other complex things become simple and clean but how to use stored procedures, stored procedures can return a table can return a number of values are also possible to perform changes to delete the increase and so how to do.

Before I say this question, I will talk about my point of view. I think that since using ORM frameworks, you should put business logic into the business logic layer instead of using stored procedures. I am more emphasis on business logic layer light storage process such development ~

Add stored procedures in ef4.0 is easier to have a edmx modulation stored process added on the But in ef4.1 only clean poco no longer have edmx change how to do it. In particular, stored procedures can be a look-up table to check the value or perform modifications to delete.

A one to solve

1. Perform a stored procedure that returns a table type

First on the stored procedure to write a simple

Create PROCEDURE [dbo]. [Proselectstu]
@StudentID int
As
BEGIN


where Enrollment.studentid=student.studentid
and enrollment.studentid= @StudentID

End

Go

The way to execute a stored procedure is to execute SQL directly. I have a detailed introduction to the Nineth article of my article ~ Everybody can go and see it first.

The execution table stored procedure is actually very powerful delay load and so on has embodied the blog park's teacher has written the very clear ~ I here will no longer write everybody can go to his that look below provides a connection ~

EF uses stored procedures to query the table's

2. Stored procedures that perform return values

First on stored procedures

CREATE PROCEDURE [dbo]. [Proselectcount]
@StuId int
As
BEGIN
Select COUNT (*) from enrollment where studentid= @StuId
End

A simple number of queries

This is done using SQLQuery to access the database because the return type needs to be supplied and we return int so we get the type of int first

3. Implementation and deletion of changes

CREATE PROCEDURE [dbo]. [Prodel]
@stuId int,
@courseId int
As
BEGIN


where studentid= @stuId and courseid= @courseId

End

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

Three. ef4.1 How to use database views. Each view is going to create the corresponding entity class. Is there a simple way?

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

One more question. Using LINQ, There's a wonderful feature projection mapping and c#3.0 anonymous function that gives us a lot of situations where we don't need views.

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

And then you accept the above value with Var result so that we don't have to go to the database to build the view. It is not easy to build the entity class.

If the company's powerful DBA has already built a lot of views for us, is it necessary to write the entity classes all at once? If you are using c#4.0 then you can use dynamic to solve the problem.

Is it cool to use like this?

This can not only query the view of ordinary tables as long as the SQL statements can automatically generate dynamic classes let you use ~

The following is the extension method and the use of emit to dynamically build thanks to ASP.net for help ~ ~

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 returned without result
}

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

public static TypeBuilder Createtypebuilder (String AssemblyName,
String ModuleName,
String typeName)
{
TypeBuilder type