LINQ to SQL, accessing the generated SQL statement

For I project I’m working on, I found I needed to access the SQL Statement that the LINQ To SQL Provider was generating. Not for logging purposes, but I needed to get the SQL statement so that I could “fool around” with it.

Logging the SQL Statement

For logging purposes, this works well;

public static void TestDbmlLogging()
{
    using (NorthwindDataContext context = new NorthwindDataContext())
    {
        context.Log = Console.Out;
        context.Orders.First();
    }
}

And produces this output;

SELECT TOP (1) [t0].[OrderID], [t0].[CustomerID], [t0].[EmployeeID], [t0].[Order

Date], [t0].[RequiredDate], [t0].[ShippedDate], [t0].[ShipVia], [t0].[Freight],

[t0].[ShipName], [t0].[ShipAddress], [t0].[ShipCity], [t0].[ShipRegion], [t0].[S

hipPostalCode], [t0].[ShipCountry]

FROM [dbo].[Orders] AS [t0]

— Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.30729.1

Retrieving the SQL Statement without executing the query

For my needs, I don’t want to execute the SQL Statement, I just want to retrieve it. After some googling around I found this solution;

context.GetCommand(query).CommandText

Using it in code looks like this;

public static void RetrievLinqToSQLStatement()
{
    using (NorthwindDataContext context = new NorthwindDataContext())
    {
        Console.WriteLine("Starting...");
        // I'm enabling logging to demonstrate that the query isn't executed
        context.Log = Console.Out;
        IQueryable query =
            context
                .Orders
                .Where(order => order.ShipCity.StartsWith("new"));

        string sql = context.GetCommand(query).CommandText;
        Console.WriteLine("This would be executed:\r\n" + sql);
        Console.WriteLine("Done!");
    }
}

And the output looks like this;

Starting…

This would be executed:

SELECT [t0].[OrderID], [t0].[CustomerID], [t0].[EmployeeID], [t0].[OrderDate], [

t0].[RequiredDate], [t0].[ShippedDate], [t0].[ShipVia], [t0].[Freight], [t0].[Sh

ipName], [t0].[ShipAddress], [t0].[ShipCity], [t0].[ShipRegion], [t0].[ShipPosta

lCode], [t0].[ShipCountry]

FROM [dbo].[Orders] AS [t0]

WHERE [t0].[ShipCity] LIKE @p0

Done!

About mfagerlund
Writes code in my sleep - and sometimes it even compiles!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: