Passing an array of integers as a parameter of an (inline) user defined function (SQL Server)

Question

I have TBV function . Like this

ALTER FUNCTION [dbo].[fn_Functiont]
(
 @accessibleIds ListAccesableIds READONLY
)
RETURNS TABLE 
AS
RETURN 
( 
SELECT d.*, b.Name AS Name, ps.Name AS PaymentSystemName, c.UserName AS UserName, c.FirstName AS ClientFirstName, c.LastName AS LastName, c.Number AS DocumentNumber, c.Id
FROM Document AS d
JOIN System AS ps ON d.SystemId = ps.Id
JOIN Client AS c ON c.Id = d.ClientId
LEFT JOIN Shop AS b ON b.Id = d.ShopId
WHERE d.OperationTypeId IN (2, 4, 5) AND c.Type = 1
)

I want to pass to this function List containing integers.I was bale to create table valued type but here is the question how could i pass List input parameter to the function?I know that i could pass lists via ADO.Net to the SP very easly but my problem regards to the Entity Framework and TBF functions

I am calling this function through Entity Framework 6 the approach which we used was database first.

Answer 1

On SQL-Server 2014 you can take advantatge of TYPE (alias data type), and pass it as a parameter of an Inline UDF simulating an array of integer, or any other tabular data.

Keep in mind you must use READONLY.

IF OBJECT_ID('dbo.MyTest') IS NOT NULL
 DROP TABLE dbo.MyTest;
GO
IF OBJECT_ID('dbo.MyFunction') IS NOT NULL
 DROP FUNCTION dbo.MyFunction;
GO
IF TYPE_ID ('dbo.Mytype') IS NOT NULL
 DROP TYPE dbo.Mytype;
GO
CREATE TYPE dbo.Mytype AS TABLE (Id int);
GO
CREATE TABLE dbo.MyTest (Id int, Val char(3));
INSERT INTO dbo.MyTest VALUES (1,'V1'),(2,'V2'),(10,'V10'),(11,'V11');
GO
CREATE FUNCTION dbo.MyFunction(@Table MyType READONLY)
RETURNS TABLE
AS
RETURN
(
 SELECT *
 FROM MyTest
 WHERE Id IN (SELECT Id FROM @Table)
);
GO

DECLARE @myTable Mytype;
INSERT INTO @myTable(Id) VALUES(1),(2),(3),(4),(5);
SELECT * FROM MyFunction(@myTable);
| Id | Status |
|:--:|:------:|
| 1 | V1 |
| 2 | V2 |

dbfiddle here