Table-Valued Functions in Entity Framework?

Question

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.

Answer 1

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