如何在EF Core中使用表值参数？

Question

我们在SQL Server数据库中有自定义类型。

CREATE TYPE dbo.UniqueIdentifiers AS TABLE (Identifier UNIQUEIDENTFIER)  

使用此类型的目的是用于查询，其中我们查找具有提供的标识符的记录。

SELECT * FROM MyRecords WHERE Id IN (@GivenIdentifiers)

我们有很多类似上面的查询，使用不同的标识符列表多次调用，通过将标识符列表作为SQL参数提供，我们可以提高查询的性能，因为SQL Server将使用已编译的SQL查询，而不是仅因为标识符列表发生变化而编译新的查询。

现在我们将使用EF Core作为我们应用程序的工作单元，并与SQL Server数据库一起使用，但我无法找到一种方法来使用EF Core查询语法消耗表值参数。

此时我们有：

public Order[] LoadOrders(params Guid[] identifiers)
{
    return context.Orders.Where(order => identifiers.Contains(order.Id)).ToArray();
}

该方法生成带有“硬编码”标识符的 SQL（请参见以下内容），当使用不同的标识符调用同一方法时，生成的查询将会不同，这将“强制”SQL Server重新编译查询，而我们正试图避免这种情况。

SELECT * FROM MyRecords WHERE Id IN ('1234abcd-...', '1234abce-...')

我们仍然可以通过手动构建查询来使用我们的原始方法，但是此时我们希望避免这样做，因为我们想能够通过连接其他表“即时”地构建不同的查询（使用EF Core查询扩展方法），而不需要为每个表显式引入单独的“构建查询”，其中我们使用类似的'WHERE'条件。

我们如何在EF Core中使用表值参数？

- Basin

---

请注意，Dapper直接支持此功能。 - undefined

这个回答解决了你的问题吗？Entity Framework存储过程表值参数 你基本上需要直接使用SQL查询（或者如果你有一个存储过程，可以使用exec）来使用Execute。 - undefined

@Charlieface，这个问题并不相似。你标记为相似的问题是关于Entity Framework，而不是EF Core，你标记为相似的问题是关于向存储过程传递值，而这个问题是关于在EF查询语法中使用它。 - undefined

100%对不起，我错过了那个。但是解决方案是一样的，你需要根据版本选择FromSqlRaw或FromSql，使用直接查询。 - undefined

@Charlieface，OP知道使用原始的SQL方法。我们仍然可以通过手动构建查询来使用我们最初的方法。 - undefined

Answer 1

对我们来说，能够将其他LINQ操作符链接到“Where”条件非常重要。

@IvanStoev感谢您提供的链接使用LINQ进行组合，基于可组合性，我们能够使用以下实现。

-- Create Table-Valued parameter type in database
CREATE TYPE Ids AS TABLE (Id INT);

// Extension method
public static IQueryable<Order> ContainsIds(this IQueryable<Order> source, params int[] ids)
{
    var parameter = ids.ToIdTypeSqlParameter();
    var sql = $"SELECT * FROM [Order] o WHERE EXISTS (SELECT 1 FROM {p.ParameterName} i WHERE i.Id = o.Id)";
    return source.FromSql(sql, parameter);
}

使用方法：

var result = await context.Orders
    .ContainsIds(1, 2, 3, 4) 
    .Where(order => order.CreatedAt > 20.January(2020))
    .Select(order => new { order.Id, order.Name, order.TotalPrice })
    .ToArrayAsync();

- Basin

---

使用当前的EF Core 6，代码将如下所示：.FromSqlInterpolated($"SELECT * FROM [Order] o WHERE EXISTS (SELECT 1 FROM {parameter} i WHERE i.Id = o.Id)") - undefined

ToIdTypeSqlParameter()方法是什么？它确切地返回什么？ - undefined

Answer 2

EFCore支持结构化参数，可用于将DataTable序列化为用户定义的表值。IN()语句支持的值有限制。为了绕过这个限制，有时我会将数据表作为用户定义的TVP进行传输。

  var myDataTable = FillDataTableWithData();
  var paramTable= new SqlParameter
  {
       ParameterName = '@MyIntIDTable',
       SqlDbType = SqlDbType.Structured,
       Value = myDataTable
  }
  await _dbContext.Database.ExecuteSqlRawAsync("BulkValidation {0}", paramTable);

- Ross Bush