Entity Framework Pitfalls: Table Valued Functions

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!

Published by

Ricardo Peres

Team Leader at Dixons Carphone. Microsoft MVP.

Leave a Reply

Your email address will not be published. Required fields are marked *