Tracing Queries in Entity Framework 4

As you aware of Entity Framework 4 feature translates LINQ queries to SQL to execute in the backend.  However, what Entity Framework may render a SQL query that looks much different.  Entity Framework’s SQL query building process may involve the use of sub queries, nested joins, or any other construct.  

var ctx = new AdventureWorksObjectContext();

var results = from a in ctx.Addresses
     where a.StateProvince.StateProvinceCode == "PA"
     && a.CustomerAddresses.Count > 0
    select a;

This query, a LINQ query against the AdventureWorks database, queries a collection of Address objects.  Even though the signature of the query is IQueryable<Address>, the underlying constructs actually are an ObjectQuery<Address> instance.  The ObjectQuery is useful for many things; one of those is the ToTraceString method.  This method returns the underlying SQL query that was executed against the database.  It is very easy to call, as shown below:

var query = results as ObjectQuery<Address>;
this.lblSQL.Text = query.ToTraceString();

The underlying SQL query appears below.  Notice how simple the LINQ query structure is setup above.  In the query above, the LINQ query makes it very easy to drill through two primary key references, from Address to the StateProvince and StateProvinceCode tables, as well as a CustomerAddresses subquery.  The query renders as the following:

SELECT [Project1].[AddressID] AS [AddressID], 
[Project1].[AddressLine1] AS [AddressLine1], 
[Project1].[AddressLine2] AS [AddressLine2], 
[Project1].[City] AS [City], 
[Project1].[StateProvinceID] AS [StateProvinceID], 
[Project1].[PostalCode] AS [PostalCode], 
[Project1].[rowguid] AS [rowguid], 
[Project1].[ModifiedDate] AS [ModifiedDate] 
FROM 
( 
  SELECT [Extent1].[AddressID] AS [AddressID], 
  [Extent1].[AddressLine1] AS [AddressLine1], 
  [Extent1].[AddressLine2] AS [AddressLine2], 
  [Extent1].[City] AS [City], 
  [Extent1].[StateProvinceID] AS [StateProvinceID], 
  [Extent1].[PostalCode] AS [PostalCode], 
  [Extent1].[rowguid] AS [rowguid], 
  [Extent1].[ModifiedDate] AS [ModifiedDate], 
  [Extent2].[StateProvinceCode] AS [StateProvinceCode], 
  (
    SELECT COUNT(1) AS [A1] 
    FROM [Sales].[CustomerAddress] AS [Extent3] 
    WHERE [Extent1].[AddressID] = [Extent3].[AddressID]
  ) AS [C1] 
  FROM [Person].[Address] AS [Extent1] 
  INNER JOIN [Person].[StateProvince] AS [Extent2] 
  ON [Extent1].[StateProvinceID] = [Extent2].[StateProvinceID] 
) AS [Project1] 
WHERE (N'PA' = [Project1].[StateProvinceCode]) 
AND ([Project1].[C1] > 0) 

If you want to know how exactly your Linq query generate the SQL query then ToTraceString very handy to verifying and underlying LINQ and SQL. Even you can use the database profiling tool also for verifying your query.

Similar Topics:

• SQL CE standalone database
• WCF Interview Questions and Answers
• ASP.net Entityframework POCO Model
• ASP.NET Page Life Cycle explained
• Asp.net file compression using opensource library
• Concept of OOPS in C#
• Singleton pattern – Design pattern
• Microsoft Sharepoint persisting webpart property values
• Microsoft Sharepoint SystemUpdate and Update using object model
• Javascript Treeview Checkbox Check All function

Tags: