Ado.net Entity Framework In-depth analysis, Part 4

The Entity data model is a concept that all Entity SQL and LINQ to Entities queries will eventually be translated into T-SQL scripts to query data from the database. There are several ways to view the generated T-SQL to help debug or analyze problems.

1. Using the SQL Server Profiler tool

Unlike a LINQ to SQL comparison, the ObjectContext class does not provide a log property or a common log mechanism, so all T-SQL statements cannot be tracked in visual Studio.

If you want to see all of the executed T-SQL statements, you need to use SQL Server's Profiler tool, for specific use of the SQL Server Profiler tool, refer to 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.ToTraceString method

Another way to view the methods of generating T-SQL statements, including the EntityCommand and ObjectQuery classes, is to have a totracestring () method. In some cases, you can use to see what SQL scripts are generated internally, without necessarily using the SQL Server Profiler tool. It should be noted that the ToTraceString () method does not actually perform query operations, but simply transforms the query into a SQL script.

By adding a breakpoint, you can easily look at the SQL script and remember that you need to open the database connection beforehand, or you will throw a InvalidOperationException exception (Execution of the command requires an open and available connection. The connection ' s 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;
}