Entity Framework Pitfalls: Table Valued Functions
var customers3 = ctx.Database.SqlQuery(typeof(Customer), "SELECT * FROM [dbo].[GetCustomers]()");
var customers3 = ctx.Database.SqlQuery(typeof(Customer), "SELECT * FROM [dbo].[GetCustomers]()");
As of Entity Framework 6.x, Table Valued Functions are not supported, which is really a pity.
For example, the following does not work:
CREATE FUNCTION [dbo].[GetCustomers]()
RETURNS @ReturnTable TABLE
(
Id INT,
Email NVARCHAR(50),
Phone NVARCHAR(50),
Name NVARCHAR(50)
)
AS
BEGIN
INSERT @ReturnTable
SELECT Id, Email, Phone, Name
FROM dbo.Customer
RETURN
END
And:
//throws an exception complaining about TVF
var customers1 = ctx.Database.SqlQuery(typeof(Customer), "[dbo].[GetCustomers]");
//likewise
var customers2 = ctx.Database.Customers.SqlQuery("[dbo].[GetCustomers]");
However, you can do this:
var customers3 = ctx.Database.SqlQuery(typeof(Customer), "SELECT * FROM [dbo].[GetCustomers]()");
And it works!