CODEWITHSUNDEEP
c#linqentity-framework-coreasp.net-core-2.0entity-framework-core-2.1
craig
craig

Reputation: 26262

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

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

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

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

And call like a scalar:

SELECT   v.ID, v.Name
        ,(SELECT val FROM dbo.VehicleRepairStatus(v.ID)) AS CurrentStatus
FROM  Vehicle v

Linq query:

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

Is either approach possible?

Upvotes: 15

Views: 13794

Answers (1)

Ivan Stoev
Ivan Stoev

Reputation: 205749

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:

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

Then register it in your OnModelCreating:

EF Core 2.x:

modelBuilder.Query<VehicleRepairStatus>();

EF Core 3.x:

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

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

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

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

And that's all.

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 }

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

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.

Upvotes: 28

Related Questions