强制Entity Framework 6查询使用正确的索引

Question

我有一个C#应用程序，使用SQLite作为数据库，使用SQLite Entity Framework 6提供程序根据用户输入生成查询语句。

数据库包含以下表和索引：

CREATE TABLE Lists (
    ListRowId INTEGER NOT NULL PRIMARY KEY,
    ListId GUID NOT NULL,
    ListName TEXT NOT NULL
);

CREATE UNIQUE INDEX [IX_Lists_ListId] ON [Lists] ( [ListId] );

-- One to many relationship: Lists => ListDetails
CREATE TABLE ListDetails (
    ListDetailRowId INTEGER NOT NULL PRIMARY KEY,
    ListDetailId GUID NOT NULL,
    ListId GUID NOT NULL,
    Plate TEXT
);

CREATE INDEX [IX_ListDetails_Plate] ON [ListDetails] ( [Plate]  ASC );

CREATE TABLE Reads (
    ReadRowId INTEGER NOT NULL PPRIMARY KEY,
    ReadId GUID NOT NULL,
    Plate TEXT
);

-- 1 To many relationship: Reads => Alarms.
-- There may be rows in Reads that have no related rows in Alarms.
CREATE TABLE Alarms (
    AlarmRowId INTEGER NOT NULL PPRIMARY KEY,
    AlarmId GUID NOT NULL,
    ListId GUID NOT NULL,
    ListDetailId GUID NOT NULL,
    ReadRowId INTEGER NOT NULL
);

CREATE INDEX [IX_Alarms_ListId_ListDetailId] ON [Alarms] ([ListId], [ListDetailId]);

CREATE INDEX [IX_Alarms_ReadId] ON [Alarms] ([ReadRowId]);

请注意，上面的DDL仅包括相关列和索引。由于ListDetails表中行数众多且为了提高速度，ListDetailId GUID列上没有索引，也无法创建。事实上，我不能更改数据库模式。

数据库中没有定义这些表之间的任何外键关系。原因是我们系统内部的问题。我重申，我不能更改模式。

使用SQLite EF6提供程序，我从数据库构建了一个实体模型。它是一个数据库优先模型，因为应用程序最初是使用不同的数据库和EF 4编写的。我们将其升级到EF 6并用SQLite替换了数据库。

在处理用户输入时，我必须组合一个查询，连接这些表。以下是我构建的基本EF表达式。

from read    in context.Reads
join alrm    in context.Alarms on read.ReadRowId equals alrm.ReadRowId into alarmJoin
from alarm   in alarmJoin.DefaultIfEmpty()
join e       in context.ListDetails on alarm.ListPlate equals e.Plate into entryJoin
from entry   in entryJoin.DefaultIfEmpty()
join l       in context.Lists on alarm.ListId equals l.ListId into listJoin
from list    in listJoin.DefaultIfEmpty()
where alarm.ListDetailId = entry.ListDetailId
select new  {
    alarm,
    list.ListName,
    read
};

我使用调试器获取了该表达式并生成了SQL语句。为了简洁起见，我已经减少了输出内容，因为我只关心在ListDetails表上的连接：

SELECT *
FROM    [Reads] AS [Extent1]
LEFT OUTER JOIN [Alarms] AS [Extent2] ON [Extent1].[ReadRowId] = [Extent2].[ReadRowId]
LEFT OUTER JOIN [ListDetails] AS [Extent3] ON ([Extent2].[ListPlate] = [Extent3].[Plate]) OR (([Extent2].[ListPlate] IS NULL) AND ([Extent3].[Plate] IS NULL))
LEFT OUTER JOIN [Lists] AS [Extent4] ON [Extent2].[ListId] = [Extent4].[ListId]
WHERE ([Extent2].[ListDetailId] = [Extent3].[ListDetailId]) OR (([Extent2].[ListDetailId] IS NULL) AND ([Extent3].[ListDetailId] IS NULL))

执行 EXPLAIN QUERY PLAN 后，结果显示此查询将对 ListDetails 表进行表扫描。我不希望这种情况发生；我希望查询使用 Plate 列上的索引。

如果我删除 where 子句，则生成的 SQL 不同：

SELECT *
FROM     [Reads] AS [Extent1]
LEFT OUTER JOIN [Alarms] AS [Extent2] ON [Extent1].[ReadRowId] = [Extent2].[ReadRowId]
LEFT OUTER JOIN [ListDetails] AS [Extent3] ON ([Extent2].[ListPlate] = [Extent3].[Plate]) OR (([Extent2].[ListPlate] IS NULL) AND ([Extent3].[Plate] IS NULL))
LEFT OUTER JOIN [Lists] AS [Extent4] ON [Extent2].[ListId] = [Extent4].[ListId]

这个查询的EXPLAIN QUERY PLAN显示数据库确实使用了ListDetails表的Plate列上的索引。 这正是我想要的结果。 但是，ListDetails表中可能会有多行具有相同的Plate；它不是唯一的字段。 我需要返回与Alarms表中提供的信息匹配的唯一一行。

如何使我的查询使用Plate列上的索引？

- Tony Vitabile

---

你确定使用索引可以提高性能，还是只是猜测？ - D Stanley

是的，我知道使用索引性能会更好。ListDetails表中有超过200万行。按企业标准来看并不算大的表，但是进行表扫描的查询需要花费一分钟以上的时间。手动编写带有索引的查询，则可以几乎瞬间返回结果。 - Tony Vitabile

我不明白如何从一个有重复值的表中获取“唯一”的内容。但除此之外，很难确定如何构造LINQ查询以使其始终产生所需的SQL语句。正如你所看到的，任何旧的修改，如添加谓词或投影，都会完全改变情况。这需要深入了解查询提供程序的工作原理。也许使用内部连接（如果可能的话）会有所帮助（而不是加入into）。否则，我认为在这种情况下必须诉诸于原始SQL。 - Gert Arnold

尝试执行ANALYZE命令。 - CL.

@GertArnold：使用包含重复项的索引获取唯一记录的方法是指定一个条件，使数据库引擎可以使用索引查找所有匹配项，然后指定另一个条件，将您想要检索的唯一记录单独出来。换句话说，应用返回匹配记录子集的条件，然后指定一个过滤器，将该子集缩小到一个记录。子集要比搜索整个表格快得多，因此扫描速度更快。 - Tony Vitabile

@CL：我已经做过了。没有任何区别。 - Tony Vitabile

Answer 1

指定索引需要使用查询提示。SqlLite使用INDEXED BY命令。例如：

LEFT OUTER JOIN ListDetails as Extent3 INDEXED BY IX_ListDetails_Plate ON Extent2.ListPlate = Extent3.Plate 

LINQ没有提供一种方法来向数据库传递查询提示。LINQ的设计哲学是开发人员不应该担心SQL：这是数据库管理员的工作。

因此，可能不会很快推出.With() LINQ扩展。

但是，有几个选项/解决方法：

1. "正确"的方法

根据LINQ的设计哲学，“正确”的方法是由DBA创建使用查询提示的存储过程。

开发人员将使用实体调用存储过程，并获得强类型的存储过程结果。

using(applicationDbContext db = new applicationDbContext())
{
   var myStronglyTypedResult = db.Database.MySprocMethod();
}

最简单的方法是使用实体（Entity）处理翻译和类创建。但是，您需要获得创建 sproc 的权限。看起来您没有这个选项。

2. 传统方法

如果 LINQ 不想使用查询提示，则不要在查询中使用 LINQ。很简单。回到 DataAdapters 和硬编码的 SQL 查询中。您已经设计了查询，那就使用它吧。

3. DataContext 的 SQL 接口

DataContext 内置了一个 SQL 接口： SqlQuery<T>(string sql, object[] params) 方法。 Database.SqlQuery

public class ListDetail
{
   public int ListDetailRowId {get; set;}

   public Guid ListDetialId {get; set;}

   public Guid ListId {get; set;}

   public string Plate {get; set;}
}    

using(ApplicationDbContext db = new ApplicationDbContext())
    {
       List<ListDetail> results = db.Database.SqlQuery<ListDetail>("SELECT * FROM ListDetails INDEXED BY IX_my_index WHERE ListDetailRowId = @p0", new object[] {50}).ToList();

       return results;
    }

这个功能让你拥有使用纯SQL的灵活性，无需处理连接字符串或DataAdapters，Entity/DataContext会为你处理DataTable到Entity的翻译。

然而，你需要手动创建Entity类。它和其他Entity类一样，只是不像sproc方法那样自动创建。

这可能是最好的选择。

- Guest

---

实体模型已经包含了一个名为 ListDetail 的类，其中包含了所有这些属性，因此我认为我不必创建该类。此外，在我的SQLite文档阅读中，我还没有找到任何关于 CREATE PROCEDURE 命令的文档。是否可以创建一个存储过程？ - Tony Vitabile

Answer 2

虽然这是一段时间之前的事情，而且我已经不在那份工作中了，但我想花点时间描述一下我们如何解决这个问题。问题如下：

• SQLite不支持存储过程，因此无法从数据库端解决问题，
• 你不能将INDEXED BY提示嵌入到LINQ查询中。

我们最终解决这个问题的方法是在实体模型中实现自定义用户函数，将所需的INDEXED BY提示添加到EF生成的SQL中。我们还为SQLite支持的其他一些SQL提示实现了几个其他的用户函数。这使得我们可以将需要提示的连接条件放在我们的用户函数内部，然后EF就会完成其余的工作。

正如我所说，我已经不在那个职位上了，所以我不能包含任何代码，但只需要在实体模型文件中添加一些XML来定义用户函数，并定义一个具有占位符函数的类即可。这些都在EF文档中有详细说明。

- Tony Vitabile