Support for Table Valued Functions in EF6 Code First?

Question

Is it possible to call a TVF in EF6 Code First?

I started a new project using EF6 Database first and EF was able to import a TVF into the model and call it just fine.

But updating the model became very time consuming and problematic with the large read-only db with no RI that I'm stuck dealing with.

So I tried to convert to EF6 code first using the Power Tools Reverse Engineering tool to generate a context and model classes.

Unfortunately the Reverse Engineering tool didn't import the TVFs.

Next I tried to copy the DBFunctions from my old Database First DbContext to the new Code First DbContext, but that gave me an error that my TVF: "cannot be resolved into a valid type or function".

Is it possible to create a code first Fluent mapping for TVFs?

If not, is there a work-around?

I guess I could use SPs instead of TVFs, but was hoping I could use mostly TVFs to deal with the problematic DB I'm stuck with.

Thanks for any work-around ideas

Answer 1

[Tested] using:

Install-Package EntityFramework.CodeFirstStoreFunctions

Declare a class for output result:

    public class MyCustomObject
        {
            [Key]
            public int Id { get; set; }
            public int Rank { get; set; }
        }

Create a method in your DbContext class

[DbFunction("MyContextType", "SearchSomething")]
public virtual IQueryable<MyCustomObject> SearchSomething(string keywords)
{
   var keywordsParam = new ObjectParameter("keywords", typeof(string)) 
                           { 
                              Value = keywords 
                            };
    return (this as IObjectContextAdapter).ObjectContext
    .CreateQuery<MyCustomObject>(
     "MyContextType.SearchSomething(@keywords)", keywordsParam);
}

Add

public DbSet<MyCustomObject> SearchResults { get; set; }

to your DbContext class

Add in the overriden OnModelCreating method:

 modelBuilder.Conventions.Add(new FunctionsConvention<MyContextType>("dbo"));

And now you can call/join with a table values function like this:

CREATE FUNCTION SearchSomething
(   
    @keywords nvarchar(4000)
)
RETURNS TABLE 
AS
RETURN 
(SELECT KEY_TBL.RANK AS Rank, Id
FROM MyTable 
LEFT JOIN freetexttable(MyTable , ([MyColumn1],[MyColumn2]), @keywords) AS KEY_TBL      
ON MyTable.Id = KEY_TBL.[KEY]  
WHERE KEY_TBL.RANK > 0   
)
GO