mvc3+ef4.1 Learning Series (11) Common problem solving in----EF4.1

Blog wrote 10 articles ~ There are many friends of the private message asked some questions, and a lot of questions everyone asked the same here about the solutions to these common problems. If you have a better solution, you want to share it.

The question is about the few

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

Eight. ef4.1 return to the DataTable for easy reporting and other complex operations

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.

There are also data migration functions using EF4.3 that can be perfectly solved ~

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

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


where Enrollment.studentid=student.studentid
and [email protected]

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

CREATE PROCEDURE [dbo]. [Proselectcount]
    @StuId int
As
BEGIN
    Select COUNT (*) from enrollment where [email protected]
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

CREATE PROCEDURE [dbo]. [Prodel]
    @stuId int,
    @courseId int
As
BEGIN
    
    
    where [email protected] and [email protected]
    
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

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

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.

extension methods for sqlqueryfordynamic

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 ~

var query = from S in student. T_studentinfo
                        where Sqlfunctions.datediff ("Day", S.createtime, "2011/11/4") = = 0
                        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~~

Added ~ ~ A friend said that this is set to empty is still executed is the inner connection ah ~

Pay attention to your relationship. The block should also be set to use this hasoptional instead of hasrequired ~ ~

Use hasoptional when your foreign key can be empty or use hasrequired

This will also determine whether you are inside the link or the left connection ~ ~

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? Based on the type of the key not defined? Enrollment?.
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.

4.LINQ to Entities unrecognized method "System.String ToString (System.String)" So the method cannot be converted to a storage expression

Or do not recognize other methods ... Similar to this error

Because there is no such method in SQL, it cannot be converted to SQL statements SqlClient and LINQ provider do not implement that method corresponding to SQL, so will be prompted not to support

Workaround:

1. Convert the value you want to convert in advance and not in LINQ or lambda notation to write such a translation.

is to put the variable. ToString () mentions a variable outside and then uses the variable directly in the lambda expression.

2. Convert to Enumerable

IEnumerable is a straightforward way to execute a method without calling provider to turn it into another way

This will get the query out of the database and then operate it in memory, so the database is very inefficient.

Eight. ef4.1 using a DataTable

A DataTable is useful in some cases, such as making a report, for example, because we can't build an entity class for each report.

It's more useful to return to a DataTable at this time

Write an extension method

   <summary>///EF SQL statements return dataTable//</summary>//<param name= "DB" ></pa  ram>//<param name= "SQL" ></param>///<param name= "parameters" ></param>// <returns></returns> public static DataTable sqlqueryfordatatatable (this Database db, str ing sql, sqlparameter[] parameters) {SqlConnection conn = new System.Data.SqlClient.SqlCon           Nection (); Conn. ConnectionString = db.           connection.connectionstring; IF (Conn. state! = ConnectionState.Open) {Conn.           Open ();           } SqlCommand cmd = new SqlCommand (); Cmd.           Connection = conn;           Cmd.commandtext = SQL; if (parameters. length>0) {foreach (var item in parameters) {cmd.               Parameters.Add (item); }} SqlDataAdapter AdaPter = new SqlDataAdapter (cmd);           DataTable table = new DataTable (); Adapter.           Fill (table);       return table; }

Call the following

   protected void Page_Load (object sender, EventArgs e)    {        if (! IsPostBack)        {            Gridview1.datasource = getdatatable ();            Gridview1.databind ();        }    }    Public DataTable getdatatable ()    {        Gardenhotelcontext context = new Gardenhotelcontext ();        int lantype = 0;        int state = 0;        Sqlparameter[] Sqlparams=new sqlparameter[2];        Sqlparams[0]=new SqlParameter ("Lantype", lantype);        Sqlparams[1]=new SqlParameter ("state", state);        DataTable datatable = Context. Database.sqlqueryfordatatatable ("Select Leavename,leaveemail from leaveinfo where [email protected] and [email Protected] ", sqlparams);        return DataTable;         }

To share a method first the invoke effect takes advantage of the returned VAR anonymous type so there's no need to declare the entity class.

  Public DataTable GetDataTable2 ()    {        Gardenhotelcontext context = new Gardenhotelcontext ();        var list = (from L in context. Leaveinfoes                   Group L by L.lantype into G                   Select New                   {                       g.key,                       num = G.count ()                   }). ToList ();        return pubclass.listtodatatable (list);    }

The core method reflects the call

      #region Reflection List to DataTable///<summary>//Convert collection class to DataTable//</summary>  <param name= "List" > Collection </param>//<returns></returns> public static            DataTable listtodatatable (IList list) {datatable result = new DataTable (); if (list. Count > 0) {propertyinfo[] Propertys = list[0]. GetType ().                GetProperties (); foreach (PropertyInfo pi in propertys) {result. Columns.Add (pi. Name, Pi.                PropertyType); } for (int i = 0; i < list. Count;                    i++) {ArrayList templist = new ArrayList (); foreach (PropertyInfo pi in propertys) {Object obj = Pi.                        GetValue (List[i], NULL);                    Templist.add (obj); } object[] Array = Templist.toarRay (); Result.                Loaddatarow (array, true);        }} return result; } #endregion

Of course, solve this problem can also use the above dynamic view of the method to solve ~

Nine. Summary

At present, the above questions have been asked more ~ Write an article after you do not have to answer similar questions ~

My solution is not the best to have a better solution welcome reply ~ Look forward to your wonderful reply!

If you still encounter any EF4.1 problems or MVC3 questions are welcome message ~ I try to help everyone

mvc3+ef4.1 Learning Series (11) Common problem solving in----EF4.1