Entity Framework 4.0 recipes Reading Notes 2 executestorequery ()

Before writing: I want to decompile system. Data. entity. dll (4.0 version) through refector, and find that only the attribute declaration and method declaration are decompiled.CodeNone of them. It's a big deal. I tried a few. the same is true for other net4.0 DLL files, and the refector on my machine is the latest version. Later I thought the dll path was incorrect and I referenced C: \ Program Files \ reference assemblies \ Microsoft \ framework \. netframework \ v4.0 \ profile \ Client \ system. data. entity. DLL. The correct path is c: \ windows \ Microsoft. net \ framework \ v4.0.30319 \ system. data. entity. DLL. However, it is strange that c: \ Program Files \ reference assemblies \ Microsoft \ framework \. netframework \ v3.5 \ profile \ Client \ system. data. entity. DLL decompilation is abnormal.

 

 

To query an Entity Data Model, you can also use SQL, store procedure (I have written a blog, with efextension and entityclient ). however, when SQL is used in ef1, It is troublesome for procedure to return entity. However, in EF4, it is much more convenient for store procedure to return entity because it does not need to call entityclient anymore, Because objectcontext provides executestorequery <telement> (), so let's talk about the specific situation:

1. Executing SQL statement: to directly query database tables, you can directly use the executestorecommand () method of object context.

Assume that the database table payment (paymentid, amount, vendor) has a payment object corresponding to the Entity Data Model,

The following code snippet uses object context insert payment. Remember to use entityclient to complete this operation in ef1.0.

Figure 1

Using (VAR context = new efrecipesentities ())
{
String SQL = @ "insert into payment (amount, vendor)
Values (@ amount, @ vendor )";
VaR ARGs = new dbparameter [] {
New sqlparameter {parametername = "amount", value = 99.97 m },
New sqlparameter {parametername = "vendor", value = "ace Plumbing "}
};
Int rowcount = context. executestorecommand (SQL, argS );

ARGs = new dbparameter [] {
New sqlparameter {parametername = "amount", value = 43.83 m },
New sqlparameter {parametername = "vendor", value = "Joe's Trash service "}
};
Rowcount + = context. executestorecommand (SQL, argS );
Console. writeline ("{0} rows inserted", rowcount. tostring ());
}

Executestorecommand () returns an int value that affects the number of rows.

2. Returning objects from a SQL statement: returns object objects through SQL queries. I have also done exercises in ef1.0. At that time, it was implemented through efextension:

The following example

Using (VAR context = new efrecipesentities ())
{
String SQL = "select * from payment where vendor = @ vendor ";
VaR ARGs = new dbparameter [] {
New sqlparameter {parametername = "vendor", value = "Ken "}};
VaR students = context. executestorequery <payment> (SQL, argS );
}

This is a very simple example. For executestorequery (), there are many things to note:

1. SQL = "select * from payment where vendor = @ vendor"; the reason why select * can be written is that the attributes of the payment object are exactly the same as those of the table fields. If they are different, you need to take the table field as an alias. the alias must be the property name of the object ing.

2. if the number of columns returned by the SQL statement is less than the number of attributes of the (materialized) object, EF will throw an exception during the concrete process, for example, adding meaningless values to the missing columns, to ensure that no error is reported during the specific process: EG 1. If SQL = "select paymentid, amount from payment", use context. executestorequery <payment> (SQL, argS); An exception is reported, so you need to add the vendor column.

Correct SQL = "select paymentid, amount, null as vendor from payment ".

3. If the number of extra entity attributes returned by the SQL statement is exclusive, EF will ignore the extra columns.

The code below can be debugged through: the extra test columns will be ignored during the specific (materialization ).

String SQL = "select paymentid, amount, vendor, null as test from chapter3.payment";

VaR students = context. executestorequery <payment> (SQL );;
Console. writeline ("payment ...");

4. if the table you returned is mapped to several object classes with inheritance relationships, the returned rows must be embodied in several objects, EF cannot embody the returned rows to the corresponding inheritance type based on the Recognition column. This is an exception thrown by EF during runtime.

Figure 1

Figure 2

Sing (VAR context = new efrecipesentities ())
{
String SQL = "select * from employee ";
VaR Employee = context. executestorequery <employee> (SQL );
}

For example, model and code, Figure 2 shows the exception information.

5. if the object has the complex type attribute, the instance of the object cannot be returned using executestorequery (), because excutestorequery () cannot return a set of complex types. it is supported to return a single complex type, but it is not supported to return an object containing complex type.

6. we can return a subset of object attributes, that is, if the payment table is queried, The paymentid and amount fields are returned. Then we define a subpayment object that contains the paymentid and amount attributes, then use excutestorequery <subpayment> ()

 

3. Returning objects from an Entity SQL statement: returns an object through esql. This is no different from ef1.0. You can download the latest EF query sample.

4. Setting a default value in a query:

1. The simplest way is to set the default value on the attributes of an object.

2. Use an anonymous object, just like var employees = from E in context. Employees select new {name = E. Name, yearsworked = E. yearsworked ?? 0 };

3. SQL statement, but this must be implemented using dbdatarecord. For example: String esql = @ "select e. name, case when. yearsworked is null then 0 else E. yearsworked end as yearsworked from employees as E "; var employees = context. createquery <dbdatarecord> (esql );

4. There is no corresponding method for LINQ to entity.