c# - 将内联表值函数与 Linq 和 Entity Framework Core 结合使用

Question

我在 SQL Server 中创建了一个内联表值函数 (ITVF)，它返回值表(出于讨论目的简化了查询):

CREATE FUNCTION dbo.VehicleRepairStatus()
RETURNS TABLE
AS
   RETURN
       SELECT VehicleID, CurrentStatus 
       FROM VehicleRepairHistory
       ...

我可以在查询中引用:

SELECT   
    v.ID, v.Name,
    r.CurrentStatus
FROM  
    Vehicle v
LEFT OUTER JOIN 
    dbo.VehicleRepairStatus() r on v.ID = r.VehicleID

我希望能够在 Linq 查询中使用它:

var vehicles = await _databaseContext.Vehicles
    .Join() // join ITVF here?
    .Where(v => v.Type == 'Bus' )
    .OrderBy(v => v.Name)
    .ToAsyncList();

在某些时候，我可能会更改 ITVF 以包含一个参数:

CREATE FUNCTION dbo.VehicleRepairStatus(@id AS INT)
RETURNS TABLE
AS
RETURN

  SELECT VehicleID, CurrentStatus 
  FROM   VehicleRepairHistory
  ...
  WHERE  VehicleID = @id

并像标量一样调用:

SELECT   v.ID, v.Name
        ,(SELECT val FROM dbo.VehicleRepairStatus(v.ID)) AS CurrentStatus
FROM  Vehicle v

Linq 查询:

var vehicles = await _databaseContext.Vehicles
    .Select( )  // call ITVF here?
    .Where(v => v.Type == 'Bus' )
    .OrderBy(v => v.Name)
    .ToAsyncList();

这两种方法都可行吗？

Answer 1

是的，可以利用引入的 EF Core 2.1 query types (从 EF Core 3.0 开始，与实体类型合并，现在称为无 key 实体类型)。以下是所需的步骤:

首先，创建一个类来保存 TVF 记录(使用正确的数据类型更新它):

public class VehicleRepairStatus
{
    public int VehicleID { get; set; }
    public int CurrentStatus { get; set; }
}

然后将其注册到您的OnModelCreating中:

EF Core 2.x:

modelBuilder.Query<VehicleRepairStatus>();

EF Core 3.x:

modelBuilder.Entity<VehicleRepairStatus>().HasNoKey().ToView(null);

然后使用 Query 的组合从数据库上下文中公开它和FromSql方法(EF Core 2.x):

public IQueryable<VehicleRepairStatus> VehicleRepairStatus(int id) => 
    Query<VehicleRepairStatus>().FromSql($"select * from VehicleRepairStatus({id})");

或Set和FromSqlInterpolated (EF Core 3.x):

public IQueryable<VehicleRepairStatus> VehicleRepairStatus(int id) => 
    Set<VehicleRepairStatus>().FromSqlInterpolated($"select * from VehicleRepairStatus({id})");

仅此而已。

现在您可以像任何其他查询一样在 LINQ 查询中使用它 IQueryable<T>返回方法，例如:

from v in db.Vehicles
from r in db.VehicleRepairStatus(v.ID)
select new { v.ID, v.Name, r.CurrentStatus }

FromSql里面的“选择”方法使其可组合，因此整个查询被转换为 SQL 并在服务器端执行。

更新:实际上，当像上面的示例一样用作相关子查询时，这不起作用(请参阅 Reference to an ITVF raises a "second operation started on this context before a previous operation completed" exception )。仅当传递常量/变量参数时才可以使用它，例如

from r in db.VehicleRepairStatus(123)
...

请参阅链接中后续帖子的答案，以正确实现相关查询场景。