Reference to an ITVF raises a “second operation st

问题:

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: A second operation started on this context before a previous operation completed. Any instance members are not guaranteed to be thread safe.

with a mention of code:

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

                var results = await (

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

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

Code

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

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

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

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

VehiclesController constructor:

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

VehiclesController Foo method:

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

}

Reference:

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

回答1:

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

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:

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

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.

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.