Get SQL query from LINQ to SQL?
C#.NetLinq to-SqlC# Problem Overview
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);
C# Solutions
Solution 1 - C#
In debugger hover mouse over commiteeMember
variable - it will show generated SQL query:
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.
Solution 2 - C#
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.
Solution 3 - C#
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