提高EF查询性能 - 检查预取List<string>中是否存在行

Question

我有以下（概括的）查询：

var listOfPossibleCars= new List<string>();
var listOfCars = db.Cars.Where(s => listOfPossibleCars.Contains(s.CarName)).ToList();

但是listOfPossibleCars很大，包含数十万条记录。这个查询导致了延迟，我想知道如何改进它。

- SB2055

---

listOfPossibleCars 是否总是相同的，还是经常变化（每次调用）？ - Evk

@Evk 每次都不同，而且每次都很大。 - SB2055

整个Cars表有多大？将其放入内存中是否可行？否则，我建议使用临时表，您可以使用SqlBulkInsert进行插入，然后像@Evik所回答的那样进行连接，尽管我建议使用两个表，一个带有查询ID，然后将其插入到Filter表中，如（int FilterID，varchar Value）。这样，您可以唯一地识别每个过滤器，而不需要锁定它，以便没有人尝试同时更新它。 - Michal Ciechan

@MichalCiechan 数百万行数据，所以不幸的是内存不是一个选项。 - SB2055

可能是重复问题：https://dev59.com/W2Af5IYBdhLWcg3wizME - Gert Arnold

@GertArnold，这就是我在寻找的。 - SB2055

Answer 1

不要使用内存集合来完成此操作，而应将 listOfPossibleCars 数据插入到数据库中的临时表中，该表需要有适当定义的索引和分区，并在数据库中完成此操作。使用适当定义的表，数据库将执行哈希连接。

数据放入数据库后，查询将如下所示：

//Instead data to database
from car in db.Cars
join possibleCar in db.PossibleCars on car.CarName equals possibleCar.CarName
select car;

- Gilad Green

---

问题在于我需要使用EF对这些实体进行更改跟踪...你有什么办法可以通过EF查询来实现这一点吗？ - SB2055

将列表插入数据库只是为了让数据库更有效地进行过滤。无论您以后想对“cars”做什么，您仍然可以执行。 - Gilad Green

请注意OP进行的是Entity Framework查询而非LINQ to objects，因此将List更改为HashSet将没有任何影响。 - Evk

你可能会认为我是因为那个HashSet而给你点了踩，但事实并非如此，而真正的踩的人很可能根本不会看到你的评论... - Evk

@Evk - 没想到是你 :) 不用担心 - Gilad Green

我强烈建议使用SqlBulkInsert而不是标准的EF Insert（Add(...)）。我还建议添加一个StringFilter（int Id identity，varchar name）和一个StringFilterValue（int FilterId，varchar Value）。插入一行到StringFilter中，以获取AutoID，然后使用过滤器ID和所有汽车将其BCP（SqlBulkInsert）到FilterValue表中。这对于10万行只需要几秒钟（如果不是更快）。 - Michal Ciechan

Answer 2

如果listOfPossibleCars相对静态（根本不改变或至少不经常改变），您确实可以按照另一个答案中描述的方式将其放入数据库。当情况不是这样时，并且假设您使用SQL Server，则可以使用表值参数。请注意，Entity Framework在包含大量数据的情况下，Contains方法非常缓慢。

首先在数据库中创建自定义表类型：

CREATE TYPE MyType AS TABLE 
(
    CarName varchar(200) primary key    
)

然后像这样查询（请注意，这是我脑海中的代码，未经测试，如果有问题请问）：

var listOfPossibleCars = new List<string>();
var dt = new DataTable();
dt.Columns.Add("CarName");
foreach (var car in listOfPossibleCars) {
     dt.Rows.Add(car);
}
var possibleCars = new SqlParameter("possibleCars", SqlDbType.Structured);
possibleCars.Value = dt;
possibleCars.TypeName = "dbo.MyType";

var listOfCars = db.Cars.SqlQuery("select C.* from Cars C inner join @possibleCars P on C.CarName = P.CarName", possibleCars).ToList();

是的，你必须使用原始的SQL查询，但如果你的列表非常庞大，仅使用Entity Framework可能无法高效地执行这样的查询。

- Evk

---

使用 SqlQuery 检索是否保留更改跟踪？ - SB2055

Database.SqlQuery 返回非跟踪实体。但是您可以使用 DbSet.SqlQuery，它返回跟踪实体（例如，db.Cars.SqlQuery）。请参阅我的更新答案。 - Evk

@MichalCiechan，我们根本没有在这里插入任何东西，你在说什么？ - Evk