Entity Framework Просмотр сгенерированного SQL
Как просмотреть SQL, сгенерированный Entity Framework
Entity Framework выполняет следующие три шага.
- Перевести код C # в операторы SQL
- Выполнить SQL в целевой базе данных
- Вернуть значения обратно в объекты C #.
Теперь вопрос в том, как выглядят сгенерированные операторы SQL и как мы можем просматривать SQL, выполненный в базе данных.
StackOverflow Вопросы, связанные с
Ответ
Простой способ просмотра сгенерированного SQL - использовать отражение для создания объекта ObjectQuery, а затем вызвать метод ToTraceString () для фактического сохранения результатов запроса.
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 добавила поддержку перехвата и регистрации сгенерированного SQL в EF6. Свойство DbContext.Database.Log может быть установлено для делегата для любого метода, который принимает строку.
Вход SQL в консоль.
using (var context = new EntityContext())
{
context.Database.Log = Console.Write;
// query here ....
}
Журнал SQL в панель вывода Visual Studio.
using (var context = new EntityContext())
{
context.Database.Log = s => System.Diagnostics.Debug.WriteLine(s);
// query here ....
}
Записать SQL во внешний файл
using (var context = new EntityContext())
{
using (var sqlLogFile = new StreamWriter("C:\\temp\\LogFile.txt"))
{
context.Database.Log = sqlLogFile.Write;
// query here ....
}
}
Давайте выполним несколько примеров и увидим вывод в окне консоли.
using (var context = new EntityContext())
{
context.Database.Log = Console.Write;
var customers = context.Customers.ToList();
}
Выход
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
Используйте какое-то условие.
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();
}
Выход
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