Entity Framework 4 - Get generated SQL for Updates/Inserts

Question

With EF4, is it possible to get the generated SQL for Updates/Inserts rather than executing it... just like you can view the query SQL before it runs.

The reason is, I have a set of helper functions that execute SQL commands. For instance...

Decrement<Category>("ProductCount", categoryID);
SetNull<Product>("CategoryID", productID);

Which generates...

UPDATE Categories 
SET ProductCount = ProductCount - 1 
WHERE CategoryID = @CategoryID; 

UPDATE Products 
SET CategoryID = NULL 
WHERE CategoryID = @ProductID;

I usually run several commands per operation, so with each helper function call, the SQL is generated and stored. When I call SaveChanges(), all of the commands are run at ONE time.

The only problem is that EF runs its commands separately behind the scenes, then I run the others right afterward. It would be ideal to run everything as one single command.

Answer 1

You can get it at design time with Sql Profiler, but I think you're meaning that you want it at run-time. Here's an example I found on how to do that:

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);
     }
}

Found here.