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 条件执行查询的计划。
来源： habr.com