Get the SQL query issued by a LINQ command in Linq to SQL

Question

I grab the SQL that will be issued by a LINQ to SQL command and store it in a string in my C# code. I do not want to have it sent to console out, not do I want to analyze it separately in SQL Studio or LinqPad. I want to have that string so that my program can do what it wants with the string. (In reality, I will send it to log4net, but that's incidental).

Does anyone know how to get this? I know that the SQL is not actually issued to the database until you start walking through the iterators, but I am hoping that perhaps there is a way to get the SQL beforehand.

Is this LINQ to SQL, or Entity Framework? – Jon Skeet Jan 04 '12 at 15:43 — Jon Skeet, Jan 04 '12 at 15:43
ok, I updated the title and content to specify linq 2 sql. – Daniel Williams Jan 04 '12 at 17:36 — Daniel Williams, Jan 04 '12 at 17:36

Wouter de Kort · Accepted Answer · 2012-01-04T17:23:17.730

6

If you are using the Entity Framework you can use the ToTraceString() method on an ObjectQuery like this:

ObjectQuery<Product> productQuery =
  from p in context.Products
  select p;

string sql = productQuery.ToTraceString();

If you are using Linq to Sql, you're DataContext has a Log property that you can use to get the Sql output.

    StringBuilder logBuilder = new StringBuilder();
    db.Log = new StringWriter(logBuilder);
    var custQuery =
        (from cust in db.Customers
        where cust.City == "London"
        select cust).ToList();

   string sql = logBuilder.ToString();

edited Jan 04 '12 at 17:23

answered Jan 04 '12 at 15:53

Wouter de Kort

39,090
12
84
103

this is Linq to SQL. But I don't want it in the console, I want to have the actual string value to use. – Daniel Williams Jan 04 '12 at 17:19
@DanielWilliams I've updated my answer. Console was just an example. You can insert any TextWriter you want. – Wouter de Kort Jan 04 '12 at 17:23

score 2 · Answer 2 · answered Jan 04 '12 at 15:49

You don't say if you are using Linq to SQL or EF, but with L2S, the database context object has a Log property that can be used for this purpose. You can learn more about this property here. Keep in mind you won't be able to execute the generated SQL without some modifications.

Get the SQL query issued by a LINQ command in Linq to SQL

2 Answers2

Linked