As you probably know, the 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 subqueries, 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;s 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) 

It’s very handy to verify the underlying SQL, using either this construct, or a database profiling tool.