CODEWITHSUNDEEP
c#.netentity-framework
Joshua Tompkins
Joshua Tompkins

Reputation: 1452

Table-Valued Functions in Entity Framework?

Is it possible to call a Table-Valued Function (TVF) using Entity Framework?

I have three TVFs defined in my database, and they do not show up in the Entity Framework's model, or in the "Update Model from Database" wizard.

It's easily possible to do this in Linq-to-SQL, you simply drag the TVF onto the design surface, but in L2E it doesn't seem like it's possible.

So far I haven't found anything that even mentions TVFs and Entity Framework together.

Upvotes: 15

Views: 6589

Answers (2)

Adam
Adam

Reputation: 2122

If you just need to get the results as a typed list from a TVF in Code-First 4.3 you can setup a helper on your DbContext e.g.

    public class ModelDbContext : DbContext
    {

        public IEnumerable<TOutput> FunctionTableValue<TOutput>(string functionName, SqlParameter[] parameters)
        {
                parameters = parameters ?? new SqlParameter[] { };

                string commandText = String.Format("SELECT * FROM dbo.{0}", String.Format("{0}({1})", functionName, String.Join(",", parameters.Select(x => x.ParameterName))));

                return  ObjectContext.ExecuteStoreQuery<TOutput>(commandText, parameters).ToArray();
        }

        private ObjectContext ObjectContext
        {
            get { return (this as IObjectContextAdapter).ObjectContext; }
        }
    }

The call it as

using (var db = new ModelDbContext())
{
    var parameters = new SqlParameter[]
    {
        new SqlParameter("@Id", SqlDbType.Int),
    };
    parameters[0].Value = 1234;

    var items = db.FunctionTableValue<Foo>("fn_GetFoos", parameters);
}

Upvotes: 6

Joshua Tompkins
Joshua Tompkins

Reputation: 1452

It appears that it's not, and it won't be possible in the next version, either.

http://social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework/thread/290b9254-4596-4106-964d-c42f7d23efd5

Upvotes: 6

Related Questions