Efcore: Mapping table valued function with entity framework code first

[jayanthakgjls]

I'm trying to execute a table valued function and get a list of entities .

[DbFunction("APIDBContext", "GetMyEntities")]
public virtual IQueryable<MyEntity> GetMyEntities(Nullable<int> userId)
            {
      return ((IObjectContextAdapter)this).ObjectContext.CreateQuery<MyEntity>(string.Format("[{0}].{1}", GetType().Name,
                        "[GetMyEntities](@userId)"), userIdParameter).AsQueryable();

    }

Seems like it I need to have same names for the columns i'm returning from the database even though I have specified column mappings in the model builder.

I'm getting the exception

Invalid column name 'MyColumn'

But I have mapped this column in the model builder ,
Property(e => e.MyColumn).HasColumnName("My_Column");

Is this not supported yet in entity framework code first ?

[bogatykh]

@jayanthakgjls Why is this closed? Is it fixed, duplicate? I got same issue.

[smitpatel]

@bogatykh - This repo is for EF Core. The original issue posted was for EF6. If you are having issue with EF6 then please file a bug report at https://github.com/aspnet/EntityFramework6

[bogatykh]

@smitpatel Is this issue fixed in EF Core?

[smitpatel]

Code posted above doesn't compile in EF Core. If you are using EF Core, then please file a new issue with the code details and error you are getting.

[divega]

@bogatykh the only way to use TVFs currently in EF Core is to write a SQL query that performs a SELECT and pass it to the FromSql() method:

C# var q = context.Products.FromSql("SELECT * FROM MyTVF({0}), argument);

There is no first class support for mapping a TVF in the model. That said, SELECT queries passed to FromSql() are composable using LINQ and at execution they go through the same mapping as any other queries, e.g. the TVF needs to have the same column names as the corresponding table in the model.

So, I guess you can say that this issue does not exist in EF Core.

[aaronhudon]

@divega Can you expand on your example a bit more? Is Products a DbQuery?

• also, are WHERE clauses not possible on a TVF unless it's transcribed in the string provided to FromSql ?

[vikumarsing]

=>In Context File
public DbQuery Products{ get; set; }

var productList = context.Products.FromSql("SELECT * FROM MyTVF({0}), argument).ToList();