c# - 对 ITVF 的引用引发 "second operation started on this context before a previous operation completed"异常

我试图在 Linq 查询中引用内联表值函数 (ITVF):

var results = await (
    from v in _context.Vehicles
    from r in _context.UnitRepairStatus(v.VehicleNumber) <-- ITVF reference
    orderby v.VehicleNumber
    select new FooViewModel {
            ID = v.ID,
            VehicleNumber = v.VehicleNumber,
            InRepair = Convert.ToBoolean(r.InRepair) <-- ITFV field
        }
    ).ToListAsync();

当查询运行时，它会生成一个错误:



提到代码:



如果我删除 ITFV 引用，查询将按预期工作

var results = await (
    from v in _context.Vehicles
    orderby v.VehicleNumber
    select new FooViewModel {
            ID = v.ID,
            VehicleNumber = v.VehicleNumber,
            InRepair = False <-- dummy value
        }
    ).ToListAsync();

为什么当我添加 ITVF 引用时会发生这种情况？我该如何解决？

代码
UnitRepairStatus ITVF:

CREATE FUNCTION dbo.UnitRepairStatus(
  @unit_number varchar(18)
)
RETURNS TABLE
AS

RETURN

  SELECT  h.InRepair
  -- connects to a second database on same server
  --  shouldn't be an issue, but mentioning it in case it might be
  FROM    Schema2..Unit u
  INNER JOIN Schema2..History h on u.ID = h.UnitID
  WHERE   u.UnitNumber = @unit_number

UnitRepairStatus 模型:

public class UnitRepairStatus
{
    public string UnitNumber { get; set; }
    public int? InRepair { get; set; }
}

MyDatabaseDbContext 数据库上下文:

public class MyDatabaseDbContext : DbContext
{

    public MyDatabaseDbContext(DbContextOptions<MyDatabaseDbContext> options) : base(options) {}
...

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        ...
        modelBuilder.Query<UnitRepairStatus>();
    }

    public IQueryable<UnitRepairStatus> UnitRepairStatus(string unitNumber) =>
        Query<UnitRepairStatus>().FromSql($"SELECT * FROM UnitRepairStatus({unitNumber})");

}

FooViewModel View 模型:

public class FooViewModel
{
    public int ID { get; set; }
    public string VehicleNumber { get; set; }
    public bool InRepair { get; set; }
}

VehiclesController 构造函数:

public VehiclesController(
    ILogger<VehiclesController> logger,
    MyDatabaseDbContext context
)
{
    _logger = logger;
    _context = context;
}

VehiclesController Foo 方法:

public async Task<IActionResult> Foo()
{

    List<FooViewModel> model = null;

    try
    {
        var results = await (  <-- line referenced in error message
            from v in _context.Vehicles
            from r in _context.UnitRepairStatus(v.VehicleNumber)
            orderby v.VehicleNumber
            select new FooViewModel {
                    ID = v.ID,
                    VehicleNumber = v.VehicleNumber,
                    InRepair = Convert.ToBoolean(r.InRepair)
                }
            ).ToListAsync();

    }
    catch (Exception e)
    {
        _logger.LogError(e.Message);
        throw;
    }

    return View(model);

}

引用:

Use a Inline Table-Valued Functions with Linq and Entity Framework

最佳答案

对不起，我的错。上一个问题的答案中的技术适用于使用常量/变量参数调用 ITVF，但不适用于像您的情况(以及我的错误示​​例)中的相关子查询。
解决方案是删除 ITVF 参数并将结果扩展为包括该列(有效地将其转换为无参数 View ):

CREATE FUNCTION dbo.UnitRepairStatus()
RETURNS TABLE
AS
RETURN
  SELECT u.UnitNumber, h.InRepair
  FROM    Schema2.Unit u
  INNER JOIN Schema2.History h on u.ID = h.UnitID

同时从上下文方法中删除参数
EF 核心 2.x:

public IQueryable<UnitRepairStatus> UnitRepairStatus() =>
    Query<UnitRepairStatus>().FromSql("SELECT * FROM UnitRepairStatus()");

EF 核心 3.x:

public IQueryable<UnitRepairStatus> UnitRepairStatus() =>
    Set<UnitRepairStatus>().FromSqlRaw("SELECT * FROM UnitRepairStatus()");

并将 LINQ 查询更改为使用 join:

var results = await (
    from v in _context.Vehicles
    join r in _context.UnitRepairStatus() on v.VehicleNumber equals r.UnitNumber // <---
    orderby v.VehicleNumber
    select new FooViewModel {
        ID = v.ID,
        VehicleNumber = v.VehicleNumber,
        InRepair = Convert.ToBoolean(r.InRepair)
    }
).ToListAsync();

现在它应该翻译并执行服务器端，并在客户端成功实现。
原始方法的问题在于 EF Core 默默地将查询执行切换到客户端评估(讨厌那样)，然后保护在同一个上下文上执行多个异步操作。

关于c# - 对 ITVF 的引用引发 "second operation started on this context before a previous operation completed"异常，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/52994531/