51

I have a query which I am passing byte[] as a parameter. I am trying to get the SQL query out of it and run that query in management studio to debug. How can I extract the SQL statement from it?

 committeeMember =
           db.Committee_Member.FirstOrDefault(x => x.Customer_Number == activity.Contact.Number
           && x.Position_Start_Date.Value.Year == activity.EndDate
           && x.Committee_Id == activity.Committee.Id && x.Cancelled != 1);
Gert Arnold
  • 100,019
  • 29
  • 193
  • 278
James123
  • 10,572
  • 61
  • 180
  • 329

3 Answers3

87

In debugger hover mouse over commiteeMember variable - it will show generated SQL query:

enter image description here

This is what ToString() returns for query. You can get same generated SQL query manually by calling ToString:

string sql = committeeMember.ToString();

This overridden method internally calls ObjectQuery.ToTraceString() which returns commands that will run on data source.

Also you can use SQL Profiler or Entity Framework Profiler to see which SQL query was executed.

Sergey Berezovskiy
  • 224,436
  • 37
  • 411
  • 441
16

Incidentally, LINQ to SQL is not entity framework. If the former, you can set [yourDataContext].Log = Console.Out (or some other stream writer) and get the query.

In the query you have, consider writing it as follows to be able to do the ToString trick:

var committeeMemberQuery =
           db.Committee_Member.*WHERE*
           (x => 
              x.Customer_Number == activity.Contact.Number
                 && x.Position_Start_Date.Value.Year == activity.EndDate
                 && x.Committee_Id == activity.Committee.Id && x.Cancelled != 1
           );

var committeeMember = committeeMemberQuery.FirstOrDefault();

Now you can do committeeMemberQuery.ToString() however you will not get parameter info (you will with DataContext.Log = Console.Out but again, that's not Entity Framework, it's LINQ to SQL.

The Red Pea
  • 14,933
  • 15
  • 89
  • 118
Mark Sowul
  • 9,920
  • 1
  • 43
  • 49
  • 1
    I added a link to some other implementations. IMO, the "other streamwriter" option is better than the highest voted answer, because that option allows you to see the sql generated for insert/update operations that get generated when calling `DataContext.SubmitChanges`. – RubberDuck Oct 09 '15 at 16:37
  • 1
    [yourDataContext].Database.Log = , if you are using DbContext. – mhenry1384 Oct 06 '16 at 16:54
  • EF 6 has this: public Action Log { get; set; } – hakan Apr 06 '18 at 08:25
  • 1
    Why are there asterisks around "WHERE"? That's super confusing – reggaeguitar Jun 29 '20 at 21:55
  • I'm emphasizing that instead of putting the predicate inside the `FirstOrDefault` call, it's been pulled out into a `Where` call. There's no way to add additional formatting to code, AFAICT – Mark Sowul Jun 30 '20 at 13:10
0

See https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/linq/how-to-display-linq-to-sql-commands for another approach to extract SQL command from linq.

More precisely, the following snippet shows the use case

Northwnd db = new Northwnd(@"c:\northwnd.mdf");

var q =
    from cust in db.Customers
    where cust.City == "London"
    select cust;

DbCommand dc = db.GetCommand(q);

Console.WriteLine("\nCommand Text: \n{0}",dc.CommandText);
Console.WriteLine("\nCommand Type: {0}",dc.CommandType);
Console.WriteLine("\nConnection: {0}",dc.Connection);

Console.ReadLine();

Gives an ouput

Command Text:  
SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactT  
itle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Coun  
try], [t0].[Phone], [t0].[Fax]  
FROM [dbo].[Customers] AS [t0]  
WHERE [t0].[City] = @p0  

Command Type: Text  

Connection: System.Data.SqlClient.SqlConnection
PKK
  • 145
  • 8