Go to ADO. NET Entity Framework for in-depth analysis, Part 4 (provides sample program download)

The previous articles in Part 1-3 introduced Entity Data Model, Entity SQL, ObjectQuery, EntityCommand, and LINQ to Entities and their code demonstration. This article mainly demonstrates how to view the generated T-SQL script through related technologies or Debug tools such as SQL Server Profiler, ToTraceString method, eSqlBlast tool, LINQPad tool and so on.

Links to the previous sections of this series:

Entity Data Model (EDM) in-depth analysis, Part 1

Entity Data Model (EDM) in-depth analysis, Part 2

Entity Data Model (EDM) in-depth analysis, Part 3

Entity Data Model is a conceptual Model where all Entity SQL and LINQ to Entities queries will eventually convert scripts into T-SQL to query Data from the database. Here we demonstrate several ways to view the generated T-SQL that helps Debug or analyze the problem.

1.Use SQL Server ProfilerTools

Compared with LINQ to SQL, The ObjectContext class does not provide Log properties or a common log Mechanism, so it cannot trace all T-SQL statements in Visual Studio.

If you want to view all the executed T-SQL statements, you need to use the SQL Server Profiler tool. For details about how to use the SQL Server Profiler tool, see the following article:

SQL Profiler: Features, functions and setup in SQL Server 2005

Http://blog.entlib.com/EntLib/archive/2008/10/27/sql-profiler-features-functions-and-setup-in-sql-server-2005.aspx

2. ToTraceStringMethod

Another way is to view the generated T-SQL statement methods, including the EntityCommand and ObjectQuery classes both have a ToTraceString () method. In some cases, it can be used to view the SQL scripts generated internally, rather than using the SQL Server Profiler tool. Note that the ToTraceString () method does not actually perform the query operation, but only converts the query to an SQL script.

By adding a breakpoint, you can easily view the SQL script. Remember that you need to open the database connection beforehand. Otherwise, an InvalidOperationException (Execution of the command requires an open and available connection) will be thrown. the connection's current state is closed .)

(1) Entity SQL: EntityCommand. ToTraceString ()Sample script

Public IList <Category> GetParentCategory ()

{

IList <Category> result = null;

EntityDataReader rdr;

EntityCommand cmd;

String esqlQuery;

Using (EntityConnection conn = new EntityConnection ("name = AdventureWorksLTEntities "))

{

Conn. Open ();

EsqlQuery = @ "Select VALUE c from AdventureWorksLTEntities. Category AS c

Where c. ParentCategory is null ";

Result = new List <Category> ();

Cmd = conn. CreateCommand ();

Cmd. CommandText = esqlQuery;

Console. WriteLine (cmd. CommandText );

Console. WriteLine (cmd. ToTraceString ());

Rdr = cmd. ExecuteReader (CommandBehavior. SequentialAccess );

While (rdr. Read ())

{

Result. Add (this. productGateway. MaterializeCategory (rdr ));

}

Conn. Close ();

}

Return result;

}

The sample interface is as follows:

(2) Entity SQL: ObjectQuery. ToTraceString ()The sample script is as follows:

NorthwindEntities context = new NorthwindEntities ();

Var SQL = "SELECT VALUE emp FROM NorthwindEntities. Employees AS emp" +

"WHERE emp. Country = @ country ";

Var query = context. CreateQuery <Employee> (SQL );

Query. Parameters. Add (new ObjectParameter ("country", "USA "));

If (context. Connection. State! = ConnectionState. Open)

Context. Connection. Open ();

Console. WriteLine (query. ToTraceString ());

(3) LINQ to Entities: (query as ObjectQuery). ToTraceString ()Sample script

You need to use type conversion to convert the LINQ to Entities (IQueryable) query to ObjectQuery, so that you can call the ToTraceString () method.

NorthwindEntities context = new NorthwindEntities ();

String country = "USA ";

Var query = from e in context. Employees

Where e. Country = country

Select e;

If (context. Connection. State! = ConnectionState. Open)

Context. Connection. Open ();

Console. WriteLine (query as ObjectQuery <Employee>). ToTraceString ());

You can also use the Reflection and Invoke () methods to get the same results:

Console. WriteLine (query. GetType (). GetMethod ("ToTraceString"). Invoke (query, null ));

3.Use eSqlBlastTools

Microsoft also provides a free tool to help you learn Entity SQL.

ESqlBlast (with source code, you need to compile it yourself) and related introduction (eSqlBlast for VS 2008 SP1 ):

Http://code.msdn.microsoft.com/esql/Release/ProjectReleases.aspx? ReleaseId = 991

If you are too lazy to compile, you can click the download link at the bottom of this article to download the compiled executable file.

The running interface is as follows:

The Connection page is used to specify three metadata files (CSDL/SSDL/MSL) and database Connection strings. The database connection string can be copied directly from App. config. Click Connect to Connect.

The Model page is used to display all EntitySets and EntityTypes.

You can enter the Entity SQL script on the Query page. You will notice that eSqlBlast supports intelligent prompt (intelliisense). Cool !!! Currently, Visual Studio 2008 sp1 does not support Entity SQL function prompts.

Click Execute to display the execution result in HTML format on the Results page, as shown in. Includes four parts: Enttiy Command (that is, the CommandText property value), Store Command (the generated T-SQL script, that is, the script generated by the ToTraceString () method), Record Count (number of records in the result set), and Data (actual Record results ).

4.Use free LINQPadTools

LINQPad is an excellent LINQ expression test tool. It was originally designed to execute a query by using the LINQ to Objects and LINQ to SQL statements, but can also be used to execute a query by using the LINQ to Entities function.

As shown in, execute the LINQ to SQL query and call the extension method Dump () to output the result.

The free LINQPad Tool can be downloaded at the following address:

Http://www.linqpad.net/