I'm trying to create a function that will take a TVP and check if another table has matching entries for every value in the TVP and return a simple bit to indicate if they all match or not. So I have something like this:
CREATE PROCEDURE [dbo].[fCheckAccess]
@userId int,
@items dbo.TypeCodeInteger READONLY
AS
BEGIN
SET NOCOUNT ON;
DECLARE @count int
DECLARE @rCount int
DECLARE @bit BIT
SET @count = (SELECT count(vr.code)
from @items r left join dbo.vAccess vr
on r.Code = vr.code and r.Type = vr.type
where vr.uid = @userId)
SET @rCount = (SELECT count(*) from @items)
set @bit = IIF(@count = @rCount, 1, 0)
return @bit
END
So the idea is to join the TVP with the vAccess
table and count the number of matching rows. Then compare this with the number of rows in the TVP and return 1 or 0. I'm thinking there is probably a more concise way to do this.
Now my problem is trying to actually access this using entity framework. I tried something like this:
using (var ctx = new MyEntities())
{
var r = new DataTable();
r.Columns.Add("Type",typeof(byte));
r.Columns.Add("Code", typeof(int));
r.Rows.Add(2, 5);
r.Rows.Add(1, 760);
r.Rows.Add(4, 39);
r.Rows.Add(2, 1746);
r.Rows.Add(2, 1);
r.Rows.Add(2, 2);
var b = ctx.Database.SqlQuery<int>("EXEC dbo.fCheckAccess @userId, @items",
new SqlParameter("@userId", SqlDbType.Int) { Value = 1893 },
new SqlParameter("@regions", r) { TypeName = "TypeCodeInteger" });
Console.WriteLine(b.First());
}
But this doesn't work. On the Console.WriteLine
line, I get an EntityCommandExecutionException
with the message:
The data reader has more than one field. Multiple fields are not valid for
EDM primitive or enumeration types.
So what's the best way to make this work?