I've read many posts and think I understand the concepts, but my small array of ints fails to pass from a C#/EF module to a SQL Server stored proc. Hoping other eyes can spot the problem.
I'm using EF6, 4.5 .Net Framework, SQL Server 2014
In the database I've created these types/procs:
CREATE TYPE [dbo].[IntsTTV] AS TABLE(
[Id] [int] NOT NULL
)
Note that a table named 'Person' exists with columns 'Id' (int) and 'LastName' (nvarchar), and has data.
CREATE PROCEDURE [dbo].[GetUsers]
@UserIds dbo.IntsTTV READONLY
AS
BEGIN
SELECT p.LastName
FROM [dbo].[Person] p
INNER JOIN @UserIds ids On p.Id = ids.Id;
END
// C# code
SqlMetaData[] columns = new SqlMetaData[1];
columns[0] = new SqlMetaData("Id", SqlDbType.Int);
SqlDataRecord row = new SqlDataRecord(columns);
row.SetInt32(0, 1); // Id of '1' is valid for the Person table
SqlDataRecord[] table = new SqlDataRecord[1];
table[0] = row;
SqlParameter parameter = new SqlParameter();
parameter.SqlDbType = SqlDbType.Structured;
parameter.ParameterName = "@UserIds";
parameter.TypeName = "dbo.IntsTTV";
parameter.Direction = ParameterDirection.Input;
parameter.Value = table;
SqlParameter[] parameters = new SqlParameter[1];
parameters[0] = parameter;
var res = _db.Database.SqlQuery<string>("GetUsers", parameters).ToList();
The code does successfully call the proc, and if I hard code the proc to simply return a select of LastName's then the C# code does receive that. This tells me what is working.
If I call the proc from other T-SQL code, passing in a prepared table-valued parameter (IntsTTV) of ints, it works.
In the proc, if I select count of rows of the passed parameter table I get zero when calling from the C# code, but I get a correct count when calling from T-SQL code.
What am I missing, please?
See Question&Answers more detail:
os 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…