none
Is there a way to know the sql generated by entity framework in debugging time?

    Question

  • Well, these is one of those questions that do not need message, just the subject .

     

    The problem is that sql express does not have a profiler, so I would like to know the sql that will run against the DB, so I just have left the debugging time in VS. Linq have it, but I do not find it inside entity framework.

     

    Thanks in advance.

    Thursday, September 13, 2007 10:23 PM

Answers

  • Hi, there is a way to get the SQL generated by the EntityCommand at runtime. The following code illustrates the usage.

     

    Code Snippet

    public static void WriteGeneratedSql(EntityCommand cmd)

    {

    cmd.Prepare();

    IServiceProvider isp = (IServiceProvider)EntityProviderFactory.Instance;

    DbProviderServices mps = (DbProviderServices)isp.GetService(typeof(DbProviderServices));

    EntityCommandDefinition definition = (EntityCommandDefinition)mps.CreateCommandDefinition(cmd);

    int commandId = 1;

    foreach (string commandText in definition.MappedCommands)

    {

    Console.WriteLine("Generated Command {0}:", commandId);

    commandId++;

    Console.WriteLine(commandText);

    }

    }

     

    Hope this helps,

    Sushil.

     

    Friday, September 14, 2007 12:42 AM
  • Also, to get the command tree from an System.Data.Objects.ObjectQuery, you can use the following code snippet.

     

    Code Snippet

    public static void WriteGeneratedSql<T>(ObjectQuery<T> query)

    {

    IServiceProvider isp = (IServiceProvider)EntityProviderFactory.Instance;

    DbProviderServices mps = (DbProviderServices)isp.GetService(typeof(DbProviderServices));

    EntityCommandDefinition definition = (EntityCommandDefinition)mps.CreateCommandDefinition(query.Context.Connection, query.CreateCommandTree());

    int commandId = 1;

    foreach (string commandText in definition.MappedCommands)

    {

    Console.WriteLine("Generated Command {0}:", commandId);

    commandId++;

    Console.WriteLine(commandText);

    }

    }

     

     

    Friday, September 14, 2007 12:48 AM
  • There is now a ToTraceString method on the EntityCommand and ObjectQuery which should allow you to get the TSQL without all the other "fun" stuff you had to do before.

     

    Steve Starck

    ADO.NET EntityServices Development Lead.

     

    Friday, May 23, 2008 3:57 PM

All replies

  • Hi, there is a way to get the SQL generated by the EntityCommand at runtime. The following code illustrates the usage.

     

    Code Snippet

    public static void WriteGeneratedSql(EntityCommand cmd)

    {

    cmd.Prepare();

    IServiceProvider isp = (IServiceProvider)EntityProviderFactory.Instance;

    DbProviderServices mps = (DbProviderServices)isp.GetService(typeof(DbProviderServices));

    EntityCommandDefinition definition = (EntityCommandDefinition)mps.CreateCommandDefinition(cmd);

    int commandId = 1;

    foreach (string commandText in definition.MappedCommands)

    {

    Console.WriteLine("Generated Command {0}:", commandId);

    commandId++;

    Console.WriteLine(commandText);

    }

    }

     

    Hope this helps,

    Sushil.

     

    Friday, September 14, 2007 12:42 AM
  • Also, to get the command tree from an System.Data.Objects.ObjectQuery, you can use the following code snippet.

     

    Code Snippet

    public static void WriteGeneratedSql<T>(ObjectQuery<T> query)

    {

    IServiceProvider isp = (IServiceProvider)EntityProviderFactory.Instance;

    DbProviderServices mps = (DbProviderServices)isp.GetService(typeof(DbProviderServices));

    EntityCommandDefinition definition = (EntityCommandDefinition)mps.CreateCommandDefinition(query.Context.Connection, query.CreateCommandTree());

    int commandId = 1;

    foreach (string commandText in definition.MappedCommands)

    {

    Console.WriteLine("Generated Command {0}:", commandId);

    commandId++;

    Console.WriteLine(commandText);

    }

    }

     

     

    Friday, September 14, 2007 12:48 AM
  • But that sql is the entity sql, isn't it? I would like to know the sql that is executed in the db.

     

    Another thing is... asking for:

     var query = from p in northwindContext.Products where p.UnitsInStock>10 select p ;

     

    I get:

    Generated Command 1: SELECT
    CASE WHEN ((CASE WHEN ([Extent1].[Discontinued] = cast(0 as bit)) THEN cast(1 as bit) ELSE cast(0 as bit) END) = cast(1 as bit)) THEN '0X' ELSE '0X0X' END AS [C1],
    0 AS [C2],
    [Extent1].[ProductID] AS [ProductID],
    [Extent1].[ProductName] AS [ProductName],
    [Extent1].[SupplierID] AS [SupplierID],
    [Extent1].[CategoryID] AS [CategoryID],
    [Extent1].[QuantityPerUnit] AS [QuantityPerUnit],
    [Extent1].[UnitPrice] AS [UnitPrice],
    [Extent1].[UnitsInStock] AS [UnitsInStock],
    [Extent1].[UnitsOnOrder] AS [UnitsOnOrder],
    [Extent1].[ReorderLevel] AS [ReorderLevel],
    CASE WHEN ((CASE WHEN ([Extent1].[Discontinued] = cast(0 as bit)) THEN cast(1 as bit) ELSE cast(0 as bit) END) = cast(1 as bit)) THEN CAST(NULL AS datetime) ELSE [Extent1].[DiscontinuedDate] END AS [C3]
    FROM [dbo].[Products] AS [Extent1]
    WHERE ( CAST( [Extent1].[UnitsInStock] AS int)) > 10

    It is very very ugly for such a simply query.

     

    What's happening?

    Sunday, September 16, 2007 10:22 PM
  • Hi, The SQL included above is indeed the TSQL generated to be run on the DB. The case conditions you see are basically for the condition based hierarchy included in your CSDL. Basically it says that: if the Discontinued bit on the DB is set to false then use 0X0X as a type-marker to materialize a DiscontinuedProduct type on the value and object layer in EDM.

     

    Hope this helps,

    Sushil.

     

    Monday, September 17, 2007 8:33 PM
  • I have visual studio 2008 sp1 installed.

     

    The MappedCommands property is not public anymore

     

    The context of Linq to sql has a property .Log that you can associate in this way " context.Log = Console.Out" to display the sql sentences being executed.

     

    Is there something similar in the entity framework??.

    Please, do not suggest using the profiler, I'm talking about getting the result programatically.

     

    Thanks in advance

    Friday, May 23, 2008 2:50 PM
  • There is now a ToTraceString method on the EntityCommand and ObjectQuery which should allow you to get the TSQL without all the other "fun" stuff you had to do before.

     

    Steve Starck

    ADO.NET EntityServices Development Lead.

     

    Friday, May 23, 2008 3:57 PM
  • Thank you very much.

    Now, what I want to do is get the sql thata is executed by Linq to entities.

    The methods that you suggested are used when you build the sentence manually, aren't they?

     

    What I want is to know what was executed in the following kind of sentence using linq to entities

    using (NorthwindEntities1 context = new NorthwindEntities1())

    {

    var cust = (from c in context.Customers

    select c).ToArray();  

    }

     

    Thanks again for any suggestion.

    Friday, May 23, 2008 4:08 PM
  • You should be able to cast the L2E query to ObjectQuery and call ToTraceString().

     

     

    Code Snippet

    using (NorthwindEntities1 context = new NorthwindEntities1())

    {

    var cust = (from c in context.Customers select c);

    string sql = ((ObjectQuery)cust).ToTraceString();

    cust.ToArray();

    }

     

     

     

    Steve Starck

    ADO.NET EntityService Development Lead

    • Proposed as answer by Scap1 Friday, December 14, 2012 10:42 AM
    • Unproposed as answer by Scap1 Friday, December 14, 2012 10:43 AM
    Friday, May 23, 2008 4:14 PM
  • Thanks Steve, this is just what I was looking for.

    Friday, May 23, 2008 5:21 PM