Pipelined function as entity in EF/MVC

Question

I have a .Net MVC app using entity framework, and normally I'd use a table or a view in a data entity... eg.

[Table("company_details", Shema = "abd")]
public class CompanyDetails
{
    [Key]
    [Column("cd_id_pk")]
    public int CompanyDetailsId { get; set; }
    etc ...
    etc ...

...where company_details is an oracle table.

However I need to try to utilise a pipelined function.... eg the sql would be:

SELECT * FROM TABLE(abd.company_pck.f_single_rprt('1A122F', '01-Feb-2020','Y'));

This had been used in a report used in Oracle forms, but now it's to be included in an .Net MVC app.

How can I include a pipelined function in my entity?

thanks in advance

Answer 1

I just tried this and it seems to work. First create a class as you would to be able to map the return from your DbContext. In your case you just call the Pipelined table function from Oracle. I used a TVF in SQL to demonstrate. The TVF returned 3 columns of data, 2 INT and 1 NVarChar.

public class ReturnThreeColumnTableFunction
{
    public int ColumnOne { get; set; }
    public int ColumnTwo { get; set; }
    public string ColumnThree { get; set; }
}

Then based on your Oracle Pipelined function, (see my MSSQL TVF below)

/* SQL TableValuedFunction */
ALTER FUNCTION [dbo].[ReturnThreeColumnTableFunction]
(   
    @ColumnOne INT,
    @ColumnTwo INT,
    @ColumnThree NVARCHAR(10)
)
RETURNS TABLE 
AS
RETURN 
(
    SELECT @ColumnOne AS ColumnOne, @ColumnTwo AS ColumnTwo, @ColumnThree AS ColumnThree
)

Then in your DbContext class you setup your CodeFirst entities, be sure to add the complex type in the OnModelCreating method.

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.ComplexType<ReturnThreeColumnTableFunction>();
    modelBuilder.ComplexType<ReturnThreeColumnTableFunction>().Property(x => x.ColumnOne).HasColumnName("ColumnOne");
    modelBuilder.ComplexType<ReturnThreeColumnTableFunction>().Property(x => x.ColumnTwo).HasColumnName("ColumnTwo");
    modelBuilder.ComplexType<ReturnThreeColumnTableFunction>().Property(x => x.ColumnThree).HasColumnName("ColumnThree");
}

Then you return this easily using the SqlQuery

var items = context.Database.SqlQuery<ReturnThreeColumnTableFunction>("SELECT * FROM dbo.ReturnThreeColumnTableFunction(1,2,'3')")