T-SQL Table Valued Functions and Linq-to-SQL

I noticed that calling a T-SQL table value function that does not explicitly define the return table type causes the Linq-To-SQL code generation engine to create an incorrectly formed SQL request.

When querying the SQL functions listed at the bottom of the post with these statements, the first will not work and the second will work correctly.


foo(1).Dump();
foo2(2).Dump();


The first function (from the two listed below) will have SQL generated that looks like

-- Region Parameters
DECLARE @p0 Int = 1
-- EndRegion
SELECT [t0].@Max AS [Max]
FROM [dbo].[foo](@p0) AS [t0]

which is incorrect. If you inspect the SELECT statement closely you will see [t0].@Max and the @ symbol should not be there.

The following SQL function definitions are referenced above:

	// This version will fail with an SqlException
	CREATE FUNCTION [dbo].[foo] (
	@Max int = 5
	)
	RETURNS table
	AS
	Return (SELECT 1 [Number] UNION SELECT 2 UNION SELECT 3 UNION SELECT 4)
	GO
	// This version will work correctly
	CREATE FUNCTION [dbo].[foo2] (
	@Max int = 5
	)
	RETURNS @Result TABLE (Number int not null UNIQUE)
	AS
	BEGIN
	INSERT INTO @Result
	SELECT 1 [Number] UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
	RETURN
	END
	GO

view raw gistfile1.sql hosted with ❤ by GitHub