Entity Framework View Generated SQL Discover How to View SQL Created
How to view the SQL Generated by Entity Framework
Entity Framework follows the following three steps.
- Translate C# code into SQL statements
- Execute SQL on a target database
- Return values back to C# objects.
Now the question is how the generated SQL statements look like and how we can view the SQL executed on the database.
StackOverflow Related Questions
Answer
The simple way of viewing the SQL generated is to use reflection to create an ObjectQuery object and then call the ToTraceString() method to actually store the query results.
using (var context = new EntityContext()) { var query = context.Customers.Where(c => c.Id == 1); var sql = ((System.Data.Objects.ObjectQuery)query).ToTraceString(); }
EF SQL Logging
Entity Framework team added support for interception and logging of generated SQL in EF6. The DbContext.Database.Log property can be set to a delegate for any method that takes a string.
Log SQL to the Console.
using (var context = new EntityContext()) { context.Database.Log = Console.Write; // query here .... }
Log SQL to Visual Studio Output panel.
using (var context = new EntityContext()) { context.Database.Log = s => System.Diagnostics.Debug.WriteLine(s); // query here .... }
Log SQL to an External File
using (var context = new EntityContext()) { using (var sqlLogFile = new StreamWriter("C:\\temp\\LogFile.txt")) { context.Database.Log = sqlLogFile.Write; // query here .... } }
Let's execute some examples and see the output on the console window.
using (var context = new EntityContext()) { context.Database.Log = Console.Write; var customers = context.Customers.ToList(); }
Output
Opened connection at 12/21/2017 6:01:00 PM +05:00 SELECT [Extent1].[Id] AS [Id], [Extent1].[Name] AS [Name] FROM [dbo].[Customers] AS [Extent1] -- Executing at 12/21/2017 6:01:00 PM +05:00 -- Completed in 0 ms with result: SqlDataReader Closed connection at 12/21/2017 6:01:00 PM +05:00
Use some condition.
using (var context = new EntityContext()) { context.Database.Log = Console.Write; var customers = context.Customers .Include(x => x.Invoices) .Where(c => "c.Invoices.Count > 0") .OrderByDescendingDynamic(c => "c.Invoices.Count") .ToList(); }
Output
Opened connection at 12/21/2017 6:02:44 PM +05:00 SELECT [Project3].[Id] AS [Id], [Project3].[Name] AS [Name], [Project3].[C1] AS [C1], [Project3].[Id1] AS [Id1], [Project3].[Date] AS [Date], [Project3].[CustomerId] AS [CustomerId] FROM ( SELECT [Project2].[Id] AS [Id], [Project2].[Name] AS [Name], [Extent4].[Id] AS [Id1], [Extent4].[Date] AS [Date], [Extent4].[CustomerId] AS [CustomerId], CASE WHEN ([Extent4].[Id] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1], [Project2].[C1] AS [C2] FROM (SELECT [Project1].[Id] AS [Id], [Project1].[Name] AS [Name], (SELECT COUNT(1) AS [A1] FROM [dbo].[Invoices] AS [Extent3] WHERE [Project1].[Id] = [Extent3].[CustomerId]) AS [C1] FROM ( SELECT [Extent1].[Id] AS [Id], [Extent1].[Name] AS [Name], (SELECT COUNT(1) AS [A1] FROM [dbo].[Invoices] AS [Extent2] WHERE [Extent1].[Id] = [Extent2].[CustomerId]) AS [C1] FROM [dbo].[Customers] AS [Extent1] ) AS [Project1] WHERE [Project1].[C1] > 0 ) AS [Project2] LEFT OUTER JOIN [dbo].[Invoices] AS [Extent4] ON [Project2].[Id] = [Extent4].[CustomerId] ) AS [Project3] ORDER BY [Project3].[C2] DESC, [Project3].[Id] ASC, [Project3].[C1] ASC -- Executing at 12/21/2017 6:02:44 PM +05:00 -- Completed in 5 ms with result: SqlDataReader Closed connection at 12/21/2017 6:02:44 PM +05:00
ZZZ Projects
