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

Solution 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

Solution 2

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/

Solution 3

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();

Solution 4

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.

Solution 5

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

View more solutions

Comments

[peter]

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

[ESG]

@Patrick Sorry, read too quickly. It's currently not possible, it's been postponed for after EF6: data.uservoice.com/forums/…

[EricTheRed]

I just tried using CodeFirstFunctions with the official release of EF 6.1 and I can attest that it works great. There seem to be a few caveats, but I was able to create a composable function that returns a complex type without any trouble. The end to end unit test provided in the project is a perfect example of how to get it done.

[Pawel]

Thanks for the information - great to hear this. I am preparing a NuGet package so that you can use it "out of the box" (i.e. without having to compile on your own) and also a blog post on how to use it. Can you share more about the caveats? Were they bugs or more about the lack of documentation?

[EricTheRed]

The only issue I ran into was that my TVF had nullable parameters. That led to some error that I cannot remember, but it led me into the code where I noticed a few TODOs about things like that. That's really all I was referring to. As soon as I changed the C# definition of the function to use regular scalar parameters everything was fine.

[Pawel]

Thanks you. I need to add support for nullable parameters indeed. I did not plan to do this for alpha but looks like I should.

[Pawel]

@EricTheRed - Yesterday I shipped the beta version of the convention. The issue with nullable parameter types is now fixed. There is also a few new features like support for stored procedures returning multiple resultsets or support for scalar UDFs - take a look here: blog.3d-logic.com/2014/08/11/…

[Dunc]

Thanks! I had to add base.OnModelCreating(modelBuilder) to OnModelCreating, or Add-Migrations found loads of changes.

[Ian Warburton]

@Pawel Is there a reason why this wouldn't work with Npgsql?

[Suamere]

The code you posted isn't even close to Entity Framework related.

[Suamere]

I could be constructive. One of the main reasons to use an ORM is to not put SQL directly into the code. Any question related to "How do I do this SQL in EF" could be answered with "Use SqlQuery". You can do Anything that way. But that is never the correct answer, because you are now no longer doing EF. As an ORM, it is expected to have a catch-all SQL function like SqlQuery, but nobody expects to actually use it. If this answer is acceptable, then why not drop EF and use the System.Data SqlConnection? The answer is: It isn't Acceptable. It works, but its bad practice.

[user3829854]

Yes anything can be done using SQL query, but there is generally an EF way to do it and an SQL query way to do it. In cases when there is an EF way to do then obviously use EF way of doing, for cases when EF does not support it the creators of EF created SQL query. My point was at the time of writing the post there was not a way to do it using EF and this was one way to do it. If you disagree with that please post an answer how you would do this strictly using EF. By using EF to do this you get automatic object mapping, the O and the M of ORM.

[Suamere]

Right now your answer is wrong and two other answers are right. So I downvoted yours and upvoted the other two. I don't have to provide my own answer in order to downvote somebody else's. That's the flux of the world. And as you elude; An answer that was correct at one time can now be incorrect. Also; At the time you answered it, CodeFirstStoreFunctions was already available. So it was even wrong when you posted your answer. Don't take it so personal. It's just bad when somebody asks how to use a screwdriver and you hand them a hammer.

[user3829854]

CodeFirstStoreFunctions are a 3rd party NuGet package, not a standard EF feature. Looking at sample code below it's just a wrapper around SqlQuery with some bells and whistles. A lot of places don't allow downloading 3rd party code into the code base, so it's not really not an option for those people until MSFT bundles it into the next EF release.

[Suamere]

Technically every call in Entity Framework is just a wrapper which evaluates to a SQL Statement. The more you push that work onto the EF Context, the closer you are to a true "Code-First" ORM, and the further you are from getting into a habit of writing SQL. The example below defines the SQL Function, but tells the Context to build a query from that. That is sufficient distance until the same thing is built into the next EF Version.

[buckley]

This answer is the best one for me as I don't want to relay on 3th party solutions too much and every ORM falls short some time. This approach is just find especially since it prevents sql injections and opens up the leaky abstraction and makes the developer aware that he can always use a little sql here and there if required.

[Richard]

NB if the return type isn't a table (so adding it as a DbSet will try and create it in migrations) add it as a complex type. The [ComplexType] attribute doesn't work, but the fluent API does: in your context type's OnModelBuilding add modelBuilder.ComplexType<MyCustomerObject>();

[Alex Zhukovskiy]

@Nina I did all listed things but still getting cannot be resolved into a valid type or function. Near member access expression. How can it be fixed? I expect it's because I added function into my IRepostory interface, instead of adding to DbContext, because I don't want to add local function to global context.

[Chinh Vo Wili]

you can follow this guideline, it works blog.3d-logic.com/2014/04/09/…