• EF Core supports mapping a table-valued SQL FUNCTION to an existing Entity class type that’s already associated with a TABLE. This is done with the DbFunctionBuilder type.
• I have a FUNCTION in my MSSQL database that returns an entity, but it also returns other entities via INNER JOIN (and LEFT OUTER JOIN). However it’s unclear how to configure EF Core to map those additional columns to those entities as though I was using Include( e => ... ) to build an IQueryable<T>.

For example, consider your typical Customers, Orders, Products, and OrderItems database – though for the sake of this demonstration, note that an OrderItem has two separate FKs to Products (expressed in EF by two navigation properties: PreferredProduct and BackupProduct).

So the EFCore Linq-to-Entities query would look like this:

List<OrderItems> items == await dbContext.OrderItems
    .Include( oi => oi.PreferredProduct )
    .Include( oi => oi.BackupProduct )
    .Include( oi => oi.Order )
        .ThenInclude( o => o.Customer )
    .Where( oi => oi.LineTotal >= 500 )
    .ToListAsync();

…I want to do this:

List<OrderItems> items == await dbContext.GetLineItemsCosting500()
    .ToListAsync();

My DbContext has the GetLineItemsCosting500 configured like so:

modelBuilder.Entity<OrderItem>().ToTable("OrderItems");
modelBuilder.HasDbFunction(typeof(MyDbContext).GetMethod(nameof(GetLineItemsCosting500)));

public IQueryable<OrderItem> GetLineItemsCosting500()
{
    return this.FromExpression(() => GetLineItemsCosting500());
}

…and my UDF looks like this:

CREATE FUNCTION dbo.GetLineItemsCosting500)
RETURNS TABLE WITH SCHEMABINDING
AS
RETURN
(
    /* The `SELECT  <cols>` part here was directly copy+pasted from the `SELECT <cols>` generated by EF's SQL when Include() was used; in this case, EF appended "1" and "2" to those columns to disambiguate PreferredProduct and BackupProduct. */
    SELECT
        oi.OrderItemId,
        oi.OrderId,
        oi.etc
        oi.PreferredProductId AS ProductId1,
        pp.Name               AS Name1,
        oi.BackupProductId    AS ProductId2,
        bp.Name               AS Name2,
        o.OrderStatus,
        o.CustomerId,
        c.Name                AS Name3
    FROM
        dbo.OrderItems AS oi
        INNER JOIN      dbo.Products  AS pp ON oi.PreferredProductId = pp.ProductId
        INNER JOIN      dbo.Products  AS bp ON oi.BackupProductId    = bp.ProductId
        INNER JOIN      dbo.Orders    AS o  ON oi.OrderId            = o.OrderId
        LEFT OUTER JOIN dbo.Customers AS c  ON oi.CustomerId         = c.CustomerId
    WHERE
        oi.LineTotal >= 500
)

…but presently, when I attempt to call the function from EF it disregards the columns from other tables so the OrderItem objects in the List<OrderItem> have their navigation-properties all null because EF doesn’t know those extra columns should be mapped to child/navigation entities as though I used Include() – but I don’t see any way to do this.

When I use Include() on the Queryable<OrderItem> returned from FromExpression that causes EF to wrap the UDF call with an INNER JOIN on its own, instead of using the columns already in the UDF results table – which defeats the point of my FUNCTION returning those extra columns.