在 Linq 和 Entity Framework Core 中使用内联表值函数

问题描述

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

I created an Inline Table-Valued Functions (ITVF) in SQL Server that returns a table of values (query simplified for discussion purposes):

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

我可以在查询中引用的内容:

Which I can reference in a query:

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

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

I'd like to be able to use it in Linq query:

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

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

At some point, I may change the ITVF to include a parameter:

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();

这两种方法都可行吗?

推荐答案

是的，可以通过利用引入的 EF Core 2.1 查询类型(从 EF Core 3.0 开始，与实体类型合并，现在称为无键实体类型).以下是必需的步骤:

Yes, it's possible by utilizing the EF Core 2.1 introduced query types (starting from EF Core 3.0, consolidated with entity types and now called keyless entity types). Following are the required steps:

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

First, create a class to hold the TVF record (update it with the correct data types):

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

然后在您的 OnModelCreating 中注册它:

Then register it in your OnModelCreating:

EF 核心 2.x:

modelBuilder.Query<VehicleRepairStatus>();

EF 核心 3.x:

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

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

Then expose it from your db context using a combination of Query and FromSql methods (EF Core 2.x):

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

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

or Set and FromSqlInterpolated (EF Core 3.x):

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

仅此而已.

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

Now you can use it inside your LINQ queries like any other IQueryable<T> returning method, for instance:

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

选择"FromSql 方法内部使其可组合，因此整个查询被转换为 SQL 并在服务器端执行.

The "select" inside FromSql method makes it composable, so the whole query is translated to SQL and executed server side.

更新:实际上，当用作上述示例的相关子查询时，这不起作用(参见 对 ITVF 的引用引发了在上一个操作完成之前在此上下文中启动的第二个操作"异常).仅当传递常量/变量参数(如

Update: Actually this doesn't work when used as correlated subquery like the above example (see Reference to an ITVF raises a "second operation started on this context before a previous operation completed" exception). It could be used only if passing constant/variable parameters like

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

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

See the answer to the follow up post from the link for correct implementation for correlated query scenarios.

这篇关于在 Linq 和 Entity Framework Core 中使用内联表值函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！