Support for Table Valued Functions in EF6 Code First?

17
votes

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

6
entity-frameworkentity-framework-6
I don't think so, at least that's the reason why i still use model first, but to work around the problem with huge models, just don't import everything into one model but have smaller domain specific proxies/clients – peter
Apparently, you can: msdn.microsoft.com/en-ca/data/hh859577.aspx – ESG
Thanks for the thoughts zahorak, I may switch back to that approach with DataBase first if I can't find a work-around for code first. My intention was to use the reverse engineering tools to import only the TVFs I need and only use those to access the big bad ugly db I can't change from a code first context. – Patrick
TheVedge, that article talks about Database First with an EDMX model, I need it from Code First, meaning no EDMX model, DbContext with Fluent API mappings instead. – Patrick
@Patrick Sorry, read too quickly. It's currently not possible, it's been postponed for after EF6: data.uservoice.com/forums/… – ESG

6 Answers

13
votes

[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
12
votes

This is now possible. I created a custom model convention which allows using store functions in CodeFirst in EF6.1. The convention is available on NuGet http://www.nuget.org/packages/EntityFramework.CodeFirstStoreFunctions. Here is the link to the blogpost containing all the details: http://blog.3d-logic.com/2014/04/09/support-for-store-functions-tvfs-and-stored-procs-in-entity-framework-6-1/

10
votes

I was able to access TVF with the code below. This works in EF6. The model property names have to match the database column names.

List<MyModel> data =
                db.Database.SqlQuery<MyModel>(
                "select * from dbo.my_function(@p1, @p2, @p3)",
                new SqlParameter("@p1", new System.DateTime(2015,1,1)),
                new SqlParameter("@p2", new System.DateTime(2015, 8, 1)),
                new SqlParameter("@p3", 12))
            .ToList();
2
votes

I actually started looking into it in EF6.1 and have something that is working on nightly builds. Check this and this out.

2
votes

I have developed a library for this functionality. You can review my article on UserTableFunctionCodeFirst. You can use your function without writing SQL query.

Update

First of all you have to add reference to the above mentioned library and then you have to create parameter class for your function. This class can contain any number and type of parameter

public class TestFunctionParams
    {
        [CodeFunctionAttributes.FunctionOrder(1)]
        [CodeFunctionAttributes.Name("id")]
        [CodeFunctionAttributes.ParameterType(System.Data.SqlDbType.Int)]
        public int Id { get; set; }
    }

Now you have to add following property in your DbContext to call function and map to the property.

[CodeFunctionAttributes.Schema("dbo")] // This is optional as it is set as dbo as default if not provided.
        [CodeFunctionAttributes.Name("ufn_MyFunction")] // Name of function in database.
        [CodeFunctionAttributes.ReturnTypes(typeof(Customer))]
        public TableValueFunction<TestFunctionParams> CustomerFunction { get; set; }

Then you can call your function as below.

using (var db = new DataContext())
            {
                var funcParams = new TestFunctionParams() { Id = 1 };
                var entity = db.CustomerFunction.ExecuteFunction(funcParams).ToList<Customer>();
            }

This will call your user defined function and map to the entity.

1
votes

A similar thing is now also provided by the EntityFramework.Functions package. This one seems to be more up-to-date.

The source code is available on GitHub, and the introductory article explains the basic principles.