对 ITVF 的引用引发了“在前一个操作完成之前在此上下文上开始的第二个操作".例外 [英] Reference to an ITVF raises a "second operation started on this context before a previous operation completed" exception

问题描述

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

I am attempting to reference an Inline Table-Valued Function (ITVF) in a Linq query:

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

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

When the query runs, it generates an error:

InvalidOperationException:在此上下文中启动了第二个操作在上一个操作完成之前.任何实例成员都不是保证线程安全.

InvalidOperationException: A second operation started on this context before a previous operation completed. Any instance members are not guaranteed to be thread safe.

提及代码:

System.Linq.AsyncEnumerable.Aggregate_(IAsyncEnumerable 源，TAccumulate 种子，Func 累加器、Func resultSelector、CancellationToken 取消令牌)MyApplication.Controllers.VehiclesController.Foo() 中VehiclesController.cs

System.Linq.AsyncEnumerable.Aggregate_(IAsyncEnumerable source, TAccumulate seed, Func accumulator, Func resultSelector, CancellationToken cancellationToken) MyApplication.Controllers.VehiclesController.Foo() in VehiclesController.cs

                var results = await (

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

If I remove the ITFV reference, the query works as expected

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 引用时会发生这种情况?我该如何解决?

Why does this happen when I add the ITVF reference? How do I resolve this?

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 DbContext:

MyDatabaseDbContext DbContext:

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 视图模型:

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

}

参考:

• 在 Linq 和实体框架中使用内联表值函数

推荐答案

对不起，我的错.您上一个问题的答案中的技术适用于使用常量/可变参数调用 ITVF，但不适用于您的情况(以及我的错误示​​例)中的相关子查询.

Sorry, my fault. The technique from the answer to your previous question is applicable for calling ITVF with constant/variable parameters, but not with correlated subqueries like in your case (and my wrong example).

解决方案是移除 ITVF 参数并将结果扩展为包括该列(有效地将其转换为无参数视图):

The solution is to remove the ITVF parameter and extend the result to include that column as well (effectively turning it into parameterless 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

同时从上下文方法中删除参数

Also remove the parameter from the context method

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:

and change the LINQ query to use 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();

现在它应该翻译并执行服务器端，并在客户端成功实现.

Now it should translate and execute server side, and successfully get materialized at the client.

原始方法的问题是 EF Core 默默地将查询执行切换到客户端评估(讨厌那样)，然后保护在同一个上下文上执行多个异步操作.

The problem with the original approach was that EF Core silently switched the query execution to client evaluation (hate that), and then hit its protection for executing multiple async operations on one and the same context.

这篇关于对 ITVF 的引用引发了“在前一个操作完成之前在此上下文上开始的第二个操作".例外的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！