LINQ 作為一種強大的新資料操作語言進入 .NET。 LINQ to SQL 作為其一部分，讓您可以使用實體框架等工具非常方便地與 DBMS 進行通訊。 然而，在經常使用它時，開發人員忘記查看可查詢提供者（在您的例子中為實體框架）將產生哪種 SQL 查詢。
讓我們透過一個例子來看看兩個要點。
為此，請在 SQL Server 中建立測試資料庫，並使用下列查詢在其中建立兩個表：
創建表
USE [TEST]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Ref](
[ID] [int] NOT NULL,
[ID2] [int] NOT NULL,
[Name] [nvarchar](255) NOT NULL,
[InsertUTCDate] [datetime] NOT NULL,
CONSTRAINT [PK_Ref] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Ref] ADD CONSTRAINT [DF_Ref_InsertUTCDate] DEFAULT (getutcdate()) FOR [InsertUTCDate]
GO
USE [TEST]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Customer](
[ID] [int] NOT NULL,
[Name] [nvarchar](255) NOT NULL,
[Ref_ID] [int] NOT NULL,
[InsertUTCDate] [datetime] NOT NULL,
[Ref_ID2] [int] NOT NULL,
CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Customer] ADD CONSTRAINT [DF_Customer_Ref_ID] DEFAULT ((0)) FOR [Ref_ID]
GO
ALTER TABLE [dbo].[Customer] ADD CONSTRAINT [DF_Customer_InsertUTCDate] DEFAULT (getutcdate()) FOR [InsertUTCDate]
GO
現在讓我們透過執行以下腳本來填充 Ref 表：
填寫參考表
USE [TEST]
GO
DECLARE @ind INT=1;
WHILE(@ind<1200000)
BEGIN
INSERT INTO [dbo].[Ref]
([ID]
,[ID2]
,[Name])
SELECT
@ind
,@ind
,CAST(@ind AS NVARCHAR(255));
SET @ind=@ind+1;
END
GO
讓我們使用以下腳本類似地填充 Customer 表：
填入客戶表
USE [TEST]
GO
DECLARE @ind INT=1;
DECLARE @ind_ref INT=1;
WHILE(@ind<=12000000)
BEGIN
IF(@ind%3=0) SET @ind_ref=1;
ELSE IF (@ind%5=0) SET @ind_ref=2;
ELSE IF (@ind%7=0) SET @ind_ref=3;
ELSE IF (@ind%11=0) SET @ind_ref=4;
ELSE IF (@ind%13=0) SET @ind_ref=5;
ELSE IF (@ind%17=0) SET @ind_ref=6;
ELSE IF (@ind%19=0) SET @ind_ref=7;
ELSE IF (@ind%23=0) SET @ind_ref=8;
ELSE IF (@ind%29=0) SET @ind_ref=9;
ELSE IF (@ind%31=0) SET @ind_ref=10;
ELSE IF (@ind%37=0) SET @ind_ref=11;
ELSE SET @ind_ref=@ind%1190000;
INSERT INTO [dbo].[Customer]
([ID]
,[Name]
,[Ref_ID]
,[Ref_ID2])
SELECT
@ind,
CAST(@ind AS NVARCHAR(255)),
@ind_ref,
@ind_ref;
SET @ind=@ind+1;
END
GO
這樣，我們就收到了兩張表，一張表的資料量超過1萬行，另外一張表的資料量超過10萬行。
現在，在 Visual Studio 中，您需要建立一個測試 Visual C# 控制台應用程式 (.NET Framework) 專案：
接下來，您需要為實體框架新增一個函式庫來與資料庫互動。
要新增它，請右鍵單擊該項目，然後從上下文功能表中選擇「管理 NuGet 套件」：
然後，在出現的 NuGet 套件管理視窗中，在搜尋視窗中輸入「Entity Framework」一詞，然後選擇 Entity Framework 套件並安裝它：
接下來，在 App.config 檔案中，關閉 configSections 元素後，您需要新增以下區塊：
<connectionStrings>
<add name="DBConnection" connectionString="data source=ИМЯ_ЭКЗЕМПЛЯРА_MSSQL;Initial Catalog=TEST;Integrated Security=True;" providerName="System.Data.SqlClient" />
</connectionStrings>
在connectionString 中，您需要輸入連接字串。
現在讓我們在不同的檔案中建立 3 個介面：
- 實作 IBaseEntityID 介面
namespace TestLINQ { public interface IBaseEntityID { int ID { get; set; } } } - IBaseEntityName 介面的實現
namespace TestLINQ { public interface IBaseEntityName { string Name { get; set; } } } - IBaseNameInsertUTCDate 介面的實現
namespace TestLINQ { public interface IBaseNameInsertUTCDate { DateTime InsertUTCDate { get; set; } } }
在一個單獨的檔案中，我們將為兩個實體建立一個基底類別 BaseEntity，其中包括公共欄位：
基類BaseEntity的實現
namespace TestLINQ
{
public class BaseEntity : IBaseEntityID, IBaseEntityName, IBaseNameInsertUTCDate
{
public int ID { get; set; }
public string Name { get; set; }
public DateTime InsertUTCDate { get; set; }
}
}
接下來，我們將在單獨的文件中建立兩個實體：
- Ref 類別的實現
using System.ComponentModel.DataAnnotations.Schema; namespace TestLINQ { [Table("Ref")] public class Ref : BaseEntity { public int ID2 { get; set; } } } - Customer 類別的實現
using System.ComponentModel.DataAnnotations.Schema; namespace TestLINQ { [Table("Customer")] public class Customer: BaseEntity { public int Ref_ID { get; set; } public int Ref_ID2 { get; set; } } }
現在讓我們在單獨的檔案中建立一個 UserContext 上下文：
UserContext 類別的實現
using System.Data.Entity;
namespace TestLINQ
{
public class UserContext : DbContext
{
public UserContext()
: base("DbConnection")
{
Database.SetInitializer<UserContext>(null);
}
public DbSet<Customer> Customer { get; set; }
public DbSet<Ref> Ref { get; set; }
}
}
我們收到了一個現成的解決方案，用於透過 EF for MS SQL Server 使用 LINQ to SQL 進行最佳化測試：
現在將以下程式碼輸入到 Program.cs 檔案：
程序.cs文件
using System;
using System.Collections.Generic;
using System.Linq;
namespace TestLINQ
{
class Program
{
static void Main(string[] args)
{
using (UserContext db = new UserContext())
{
var dblog = new List<string>();
db.Database.Log = dblog.Add;
var query = from e1 in db.Customer
from e2 in db.Ref
where (e1.Ref_ID == e2.ID)
&& (e1.Ref_ID2 == e2.ID2)
select new { Data1 = e1.Name, Data2 = e2.Name };
var result = query.Take(1000).ToList();
Console.WriteLine(dblog[1]);
Console.ReadKey();
}
}
}
}
接下來，讓我們啟動我們的專案。
工作結束後，控制台上會顯示以下內容：
產生的 SQL 查詢
SELECT TOP (1000)
[Extent1].[Ref_ID] AS [Ref_ID],
[Extent1].[Name] AS [Name],
[Extent2].[Name] AS [Name1]
FROM [dbo].[Customer] AS [Extent1]
INNER JOIN [dbo].[Ref] AS [Extent2] ON ([Extent1].[Ref_ID] = [Extent2].[ID]) AND ([Extent1].[Ref_ID2] = [Extent2].[ID2])
也就是說，一般來說，LINQ 查詢可以很好地產生對 MS SQL Server DBMS 的 SQL 查詢。
現在讓我們將 LINQ 查詢中的 AND 條件改為 OR：
LINQ查詢
var query = from e1 in db.Customer
from e2 in db.Ref
where (e1.Ref_ID == e2.ID)
|| (e1.Ref_ID2 == e2.ID2)
select new { Data1 = e1.Name, Data2 = e2.Name };
讓我們再次啟動我們的應用程式。
由於指令執行時間超過30秒，執行會報錯崩潰：
如果您查看 LINQ 產生的查詢：
，那麼您可以確保透過兩個集合（表）的笛卡爾積進行選擇：
產生的 SQL 查詢
SELECT TOP (1000)
[Extent1].[Ref_ID] AS [Ref_ID],
[Extent1].[Name] AS [Name],
[Extent2].[Name] AS [Name1]
FROM [dbo].[Customer] AS [Extent1]
CROSS JOIN [dbo].[Ref] AS [Extent2]
WHERE [Extent1].[Ref_ID] = [Extent2].[ID] OR [Extent1].[Ref_ID2] = [Extent2].[ID2]
讓我們重寫 LINQ 查詢，如下所示：
最佳化 LINQ 查詢
var query = (from e1 in db.Customer
join e2 in db.Ref
on e1.Ref_ID equals e2.ID
select new { Data1 = e1.Name, Data2 = e2.Name }).Union(
from e1 in db.Customer
join e2 in db.Ref
on e1.Ref_ID2 equals e2.ID2
select new { Data1 = e1.Name, Data2 = e2.Name });
然後我們得到以下 SQL 查詢：
SQL查詢
SELECT
[Limit1].[C1] AS [C1],
[Limit1].[C2] AS [C2],
[Limit1].[C3] AS [C3]
FROM ( SELECT DISTINCT TOP (1000)
[UnionAll1].[C1] AS [C1],
[UnionAll1].[Name] AS [C2],
[UnionAll1].[Name1] AS [C3]
FROM (SELECT
1 AS [C1],
[Extent1].[Name] AS [Name],
[Extent2].[Name] AS [Name1]
FROM [dbo].[Customer] AS [Extent1]
INNER JOIN [dbo].[Ref] AS [Extent2] ON [Extent1].[Ref_ID] = [Extent2].[ID]
UNION ALL
SELECT
1 AS [C1],
[Extent3].[Name] AS [Name],
[Extent4].[Name] AS [Name1]
FROM [dbo].[Customer] AS [Extent3]
INNER JOIN [dbo].[Ref] AS [Extent4] ON [Extent3].[Ref_ID2] = [Extent4].[ID2]) AS [UnionAll1]
) AS [Limit1]
遺憾的是，在 LINQ 查詢中只能有一個聯結條件，因此這裡可以對每個條件使用兩個查詢來進行等效查詢，然後透過 Union 將它們組合起來以刪除行之間的重複項。
是的，考慮到可能傳回完整的重複行，查詢通常是不等效的。 然而，在現實生活中，完全重複的線條是不需要的，人們試圖擺脫它們。
現在我們來比較一下這兩個查詢的執行計劃：
- 對於 CROSS JOIN，平均執行時間為 195 秒：
- 對於 INNER JOIN-UNION，平均執行時間小於 24 秒：
從結果中可以看出，對於兩個擁有數百萬筆記錄的表，優化後的 LINQ 查詢比未優化的快很多倍。
對於條件中帶有 AND 的選項，採用以下形式的 LINQ 查詢：
LINQ查詢
var query = from e1 in db.Customer
from e2 in db.Ref
where (e1.Ref_ID == e2.ID)
&& (e1.Ref_ID2 == e2.ID2)
select new { Data1 = e1.Name, Data2 = e2.Name };
幾乎總是會產生正確的 SQL 查詢，平均運行時間約為 1 秒：
也適用於 LINQ to Objects 操作而非查詢，例如：
LINQ 查詢（第一個選項）
var query = from e1 in seq1
from e2 in seq2
where (e1.Key1==e2.Key1)
&& (e1.Key2==e2.Key2)
select new { Data1 = e1.Data, Data2 = e2.Data };
您可以使用以下查詢：
LINQ 查詢（第一個選項）
var query = from e1 in seq1
join e2 in seq2
on new { e1.Key1, e1.Key2 } equals new { e2.Key1, e2.Key2 }
select new { Data1 = e1.Data, Data2 = e2.Data };
其中：
定義兩個數組
Para[] seq1 = new[] { new Para { Key1 = 1, Key2 = 2, Data = "777" }, new Para { Key1 = 2, Key2 = 3, Data = "888" }, new Para { Key1 = 3, Key2 = 4, Data = "999" } };
Para[] seq2 = new[] { new Para { Key1 = 1, Key2 = 2, Data = "777" }, new Para { Key1 = 2, Key2 = 3, Data = "888" }, new Para { Key1 = 3, Key2 = 5, Data = "999" } };
，Para類型定義如下：
參數類型定義
class Para
{
public int Key1, Key2;
public string Data;
}
因此，我們研究了最佳化 MS SQL Server 的 LINQ 查詢的一些方面。
不幸的是，即使是經驗豐富且領先的 .NET 開發人員也忘記了他們需要了解他們使用的指令在幕後的作用。 否則，他們就會成為配置者，並可能在未來擴展軟體解決方案時以及外部環境條件發生微小變化時埋下定時炸彈。
也進行了簡短的審查 .
測試的來源 - 項目本身、TEST 資料庫中表的建立以及用資料填充這些表的位置 .
此外，在此儲存庫的 Plans 資料夾中，還有使用 OR 條件執行查詢的計畫。
來源： www.habr.com