How do you map a multi-statement table valued function in Entity Framework Core 9?

Question

As the question says, exiting documentation on the Microsoft Learn website (Mapping a queryable function to a table-valued function) doesn't describe how to configure these types of functions.

How do you map these types of function in EF Core given that the returned table doesn't exist?

Test function

CREATE FUNCTION [dbo].[fn_multi_statement_test]
(
    @p1 INT
)
RETURNS @OutputTable TABLE
(
    [Id] INT,
    [Name] NVARCHAR(50)
)
AS

BEGIN

    DECLARE @Apple AS TABLE
    (
        [Name] NVARCHAR(50)
    )

    INSERT INTO @Apple ([Name]) SELECT 'Apple'

    DECLARE @Pear AS TABLE
    (
        [Name] NVARCHAR(50)
    )

    INSERT INTO @Pear ([Name]) SELECT 'Pear'

    INSERT INTO @OutputTable ([Id], [Name]) SELECT @p1, [Name] FROM @Apple
    INSERT INTO @OutputTable ([Id], [Name]) SELECT @p1, [Name] FROM @Pear
RETURN
END;

EF Configuration

// MultiStatementTest.cs

public class MultiStatementTest
{
    public int Id { get; set; }
    public string Name { get; set; }
}

// Context.OnModelCreatingPartial.cs

public partial class Context
{
    public IQueryable<MultiStatementTest> fn_multi_statement_test(int p1)
    {
        return FromExpression(() => fn_multi_statement_test(p1));
    }

    partial void OnModelCreatingPartial(ModelBuilder modelBuilder)
    {
        // What table name should be used here?
        modelBuilder.Entity<MultiStatementTest>().ToTable(?);
        
        modelBuilder
        .HasDbFunction(typeof(Context).GetMethod(nameof(fn_multi_statement_test), new[] { typeof(int) }))
        .HasName("fn_multi_statement_test")
        .HasSchema("dbo");
    }
}

Any help/guidance is appreciated.

Thanks.

Answer 1

Your example may not work as you expect since you are inserting the provided value in as the ID of both rows and returning them in a combined set. You would need to define your entity as Keyless:

[Keyless]
public class MultiStatementTest
{
    public int Id { get; set; }
    public string Name { get; set; }
}

Otherwise you would get back 2 rows, with a single reference reporting as "Apple" if "Id" is interpreted as the Key. (default behaviour with that naming convention)

For the declaration you would need a method in your DbContext to both map to the DbFunction and serve as the access point:

public IQueryable<MultiStatementTest> MultiStatements(int p1) => FromExpression(() => MultiStatements(p1));

Where the registration looks like:

modelBuilder.HasDbFunction(typeof(AltDbContext).GetMethod(nameof(MultiStatements), new[] { typeof(int) }))
    .HasName("fn_multi_statement_test");

Then when accessing the values:

    using var context = new AltDbContext();
    var items = context.MultiStatements(2).ToList();

This would return the two multi-statement objects from the results of the function, both with the Id of "2".

Note that there is no DbSet for these function provided entities, they are merely a return type of the defined function.