Getting TSQL Query With ToTraceString() in EF

Setup for this post

(if you want to run the code as displayed)

• Building an EF 4 Model Assembly for Pubs DB – To setup my Pubs model.
• Create Entity Framework Data Context for LINQPad 4 – To set up EF in LINQPad.
• Querying EF ObjectContext from LINQPad – Why am I using EF_CONNECTION_STRING

I use ObjectQuery.ToTraceString() to figure out what is going on behind the scenes. ToTraceString() returns the TSQL (assuming you are using the SQL Server provider) for that ObjectQuery.

Since I can see the TSQL code that EF generates I can poke around and figure what is going around behind the curtain.

For example if you ran this in LINQPad:

void Main()
{
    PubsModel.pubsEntities context = new PubsModel.pubsEntities(EF_CONNECTION_STRING);

    // Entity SQL:
    // Returns ObjectQuery<T> directly
    ObjectQuery<author> esqlq = context.CreateQuery<author>(
        "SELECT VALUE a FROM authors AS a");
    esqlq.ToTraceString().Dump("--CreateQuery");
    
    // Linq to Entities
    // ObjectQuery<T> implements IQueryable<T>
    // This statement returns an ObjectQuery<T>
    // casted as IQueryable<T>
    IQueryable<author> linqq = from a in context.authors select a;    
    // So it must be cast back to execute ToTraceString()
    ((ObjectQuery)linqq).ToTraceString().Dump("--Linq Query");
}

You would get:

--CreateQuery

SELECT 
[Extent1].[au_id] AS [au_id], 
[Extent1].[au_lname] AS [au_lname], 
[Extent1].[au_fname] AS [au_fname], 
[Extent1].[phone] AS [phone], 
[Extent1].[address] AS [address], 
[Extent1].[city] AS [city], 
[Extent1].[state] AS [state], 
[Extent1].[zip] AS [zip], 
[Extent1].[contract] AS [contract]
FROM [dbo].[authors] AS [Extent1]  

--Linq Query

SELECT 
[Extent1].[au_id] AS [au_id], 
[Extent1].[au_lname] AS [au_lname], 
[Extent1].[au_fname] AS [au_fname], 
[Extent1].[phone] AS [phone], 
[Extent1].[address] AS [address], 
[Extent1].[city] AS [city], 
[Extent1].[state] AS [state], 
[Extent1].[zip] AS [zip], 
[Extent1].[contract] AS [contract]
FROM [dbo].[authors] AS [Extent1]