CODEWITHSUNDEEP
c#.netlinq-to-sql
James123
James123

Reputation: 11662

Get SQL query from LINQ to SQL?

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

Upvotes: 56

Views: 127713

Answers (4)

Isaiah Ryman
Isaiah Ryman

Reputation: 67

I know this is old, but for anyone using VS2022: In debug mode...

Step 1: Set a breakpoint at the statement that comes after the query.

Step 2: When your debug mode hits the breakpoint, hover over the variable which the query inserts values to. A gray box will show up with the object data. You can click the little triangle for more options.

Step 3: Look at the dropdown for Debug view and you can simply just view the query.

That's it! You can copy and paste that query into SSMS and run it on your database to see what the result should be, just be sure to refresh your database first if you are editing data in your test or method when you hit the breakpoint.

Upvotes: 2

Petro Kolosov
Petro Kolosov

Reputation: 199

See https://learn.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

Upvotes: 1

Mark Sowul
Mark Sowul

Reputation: 10610

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.

Upvotes: 16

Sergey Berezovskiy
Sergey Berezovskiy

Reputation: 236318

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.

Upvotes: 89

Related Questions