EF LINQ to SQL 从数据库获取整个表而不是选择 - where 子句包含带有 Any 语句的列表

Question

我有 EF DataContext 并且希望从 SQL 获取数据而不从数据库获取完整表：

        List<(int width, int height)> dimensions = new List<(int width, int height)>();

        var res = context.DimensionData.Where(d => 
            dimensions.Any(dim => 
                dim.width == d.Width && dim.height == d.Height))
                .ToList();

使用 SQL Profiler 我可以看到它将获取完整表并且内存搜索速度较慢。

如何编写查询以便在数据库中完成处理？

我假设用 SQL 代码回答问题并返回 Linq 可以给出如何做到这一点的答案。 似乎需要为内部联接生成一个临时表，我不确定 Linq 如何或是否能够做到这一点。

更新1： 现在我已经设法找到一个在内存集合中加入数据库的包： https:/ /www.nuget.org/packages/EntityFrameworkCore.MemoryJoin

不幸的是，项目仍在 .Net Core 2.2 上，无法升级到 .Net Core 3.x，因为基础包发生了重大更改我需要使用。

更新2：

0.5.7版本的MemoryJoin包对我有用，正如@Ivan Stoev建议的那样

SELECT [d].[Id], [d].[Width], [d].[Height]
FROM [DimensionData] AS [d]
INNER JOIN (
    SELECT [x].[int1] AS [Width], [x].[int2] AS [Height]
    FROM (
        SELECT * FROM (VALUES (1, 55, 66), (2, 88, 99)) AS __gen_query_data__ (id, int1, int2)
    ) AS [x]
) AS [t] ON ([d].[Width] = [t].[Width]) AND ([d].[Height] = [t].[Height])

Answer 1

该维度块。Any(...) 是本地的，不会在 Linq To SQL 和 EF 中转换为有效的 SQL。对于您的情况，您的实际代码应该首先执行 .AsEnumerable()、.ToList() 之类的操作以使数据成为本地数据，以便可以在其上使用Dimensions.Any()。首先将其本地化会导致首先驱动整个数据，而不进行任何过滤。

现在介绍我建议的解决方案：

• 创建维度列表的 XML 表示形式。
• 创建一个 SQL 命令，该命令将获取此 XML 作为参数，将其扩展为表，对原始表执行查询。所有这些都将在服务器上完成。

接下来对解决方案进行建模，出于测试目的，假设我们在 SQL Server（Linq To SQL - 所以这是 SQL Server，对吧？）中创建了 DimensionData 表，如下所示：

 CREATE TABLE DimensionData
  (
    id     INT IDENTITY NOT NULL PRIMARY KEY
  , Width  INT NOT NULL
  , Height INT NOT NULL
  );

WITH
  base AS (SELECT x FROM(VALUES(1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) AS t(x) )
INSERT INTO dbo.DimensionData(Width, Height)
SELECT t1.x, t2.x FROM base t1 CROSS JOIN base t2;

CREATE INDEX ix_DimWH ON dbo.DimensionData(Width, Height);

一个包含 100 行的简单测试表，仅用于采样。

现在添加一些维度示例的代码（假设您在 LinqPad 中运行此示例，因此为简单起见添加了 .Dump()）：

void Main()
{
    List<(int width, int height)> dimensions = new List<(int width, int height)>();
    dimensions.Add((1, 1));
    dimensions.Add((2, 1));
    dimensions.Add((3, 1));

    string xml = new XDocument(
        new XElement("Data",
            from wh in dimensions
            select
                new XElement("row", 
                new XAttribute("Width", wh.width), 
                new XAttribute("Height", wh.height))
        )
      ).ToString();
      
    string sqlCommand = @"DECLARE @hDoc int;
EXECUTE sp_xml_preparedocument @hDoc OUTPUT, {0};
with source as
(
  SELECT width, height FROM OPENXML(@hDoc, '/Data/row', 1) with ( Width int, Height int )
)
Select * 
from DimensionData d
where exists (
   select * 
   from source s
   where d.Width = s.Width and d.Height = s.Height
); 
EXECUTE sp_xml_removedocument @hDoc;";

string cs = @"server=.\SQL2017;Database=TestDb;Trusted_Connection=yes";

// Linq To SQL
    var context = new DataContext(cs);
    var res = context.ExecuteQuery<DimensionData>(sqlCommand, xml);

// EF
//  var context = new MyContext(cs);
//  var res = context.Database.SqlQuery<DimensionData>(sqlCommand, xml);
    
    res.ToList().Dump();
}
public class DimensionData
{
    [Key]
    public int Id { get; set; }
    public int Width { get; set; }
    public int Height { get; set; }
}

public class MyContext : DbContext
{
    public MyContext(string connectionString)
       : base(connectionString)
    { }
    public DbSet<DimensionData> DimensionData { get; set; }
}

如果您在探查器中检查生成的 SQL，Linq To SQL 和 EF 都会将与此相同的 SQL 发送到服务器：

exec sp_executesql N'DECLARE @hDoc int;
EXECUTE sp_xml_preparedocument @hDoc OUTPUT, @p0;
with source as
(
  SELECT width, height FROM OPENXML(@hDoc, ''/Data/row'', 1) with ( Width int, Height int )
)
Select * 
from DimensionData d
where exists (
   select * 
   from source s
   where d.Width = s.Width and d.Height = s.Height
); 
EXECUTE sp_xml_removedocument @hDoc;',N'@p0 nvarchar(111)',@p0=N'<Data>
  <row Width="1" Height="1" />
  <row Width="2" Height="1" />
  <row Width="3" Height="1" />
</Data>'
go

根据使用 sp_xml_* 在 SQL Server 端进行 XML 解析，这些 SP 从非常旧的版本开始就可以在 SQL Server 中使用，并且工作得非常好。如果您愿意，您可以将其替换为较新的 xml 函数（恕我直言，不值得）。

Answer 2

我不认为 LINQ 可以将这样的复合条件传递给 SQL，但是您可以使用几个更简单的条件来缩小 SQL 返回的行数。

尝试：

List<(int width, int height)> dimensions = new List<(int width, int height)>();
// (Insert values inserted into the above list)
List<int> widths = dimensions.Select(d => d.width).ToList();
List<int> heights = dimensions.Select(d => d.height).ToList();

var res = context.DimensionData
    .Where(d =>
         widths.Contains(d.Width)
         && heights.Contains(d.Height)
    )
    // (Might need .ToList() or something here to force evaluation)
    .Where(d => dimensions.Any(dim =>
         dim.width == d.Width
         && dim.height == d.Height
    ))
    .ToList();

第一个Where应该翻译成SQL，如下所示：（

SELECT *
FROM DimensionData d
WHERE d.Width IN (@p1, @p2, @p3, ...)
AND d.Height IN (@p4, @p5, @p6, ...)

只是在生成的SQL中更难阅读）

第二个.Where（）将对初始检索结果进行操作以过滤掉交叉情况，例如（width1，height2）， （宽度3，高度1）。