Use a table-valued function in EF Core 5.0

Question

I'm in the midst of migrating a Asp.Net MVC project from .Net 2.2 to .Net 5.0.

The last stumbling block is getting a table-valued function to work with EF Core 5.

In EF Core 2.2, the syntax was:

public IQueryable<OutOfService> UnitOutOfService(DateTime startingDate, DateTime endingDate) =>
Query<OutOfService>().FromSql($"SELECT * FROM UnitOutofServiceView(NULL, 1, '1,2,3,4,6', 0, {startingDate}, {endingDate}, 0, 0, 0)");

I'm only interested in providing the startingDate and endingDate parameters.

I was able to join the TVF with a table in the controller:

model = await (
    from v in context.Vehicles
        join r in context.UnitOutOfService(DateTime.Now.Date, DateTime.Now.Date) on v.ID equals r.ID into loj
        from rs in loj.DefaultIfEmpty()
    where v.Schedule == true && v.Suspend == false
    orderby v.Name
    select new IndexViewModel
    {
        ID = v.ID,
        Name = v.Name,
        ...
        ServiceDescription = rs.Description
    }
).ToListAsync();

Unfortunately, I'm not able to get this to work in EF Core 5.0.

I've defined the TVF in the ApplicationDbContext class:

public IQueryable<OutOfService> UnitOutOfService(string sns, int expectedversion, string Site, bool UnitHistory, DateTime HistoryStartDate, DateTime HistoryDateEnd, int DaysOutMin, bool InServiceUnits, bool NotSowFromRO)
            => FromExpression(() => UnitOutOfService(null, 1, "1,2,3,4,6", false, HistoryStartDate, HistoryDateEnd, 0, false, false));

The "default", parameter values don't seem to have any affect in the controller, so I provide them again:

return await (
    from v in _context.Vehicles
    join r in _context.UnitOutOfService(DateTime.Now.Date, DateTime.Now.Date) on v.VehicleID equals r.UnitNumber into loj
    join r in _context.UnitOutOfService(null, 1, "1,2,3,4,6", false, today, today, 0, false, false) on v.VehicleID equals r.UnitNumber into loj
    from rs in loj.DefaultIfEmpty()
    orderby v.Name
    select new IndexViewModel
    {
        ID = v.ID,
        Name = v.Name,
        ...
        ServiceDescription = rs.Description
    }
).ToArrayAsync();

Running the controller's code produces numerous errors.

What am I missing?

Answer 1

THIS CODE IS TRUE:

public IQueryable<OutOfService> UnitOutOfService(string sns, int expectedversion, string Site, bool UnitHistory, DateTime HistoryStartDate, DateTime HistoryDateEnd, int DaysOutMin, bool InServiceUnits, bool NotSowFromRO)
        => FromExpression(() => UnitOutOfService(sns, expectedversion, Site, UnitHistory, HistoryStartDate, HistoryDateEnd, DaysOutMin, InServiceUnits, NotSowFromRO);