1

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?

Matt Burland
  • 44,552
  • 18
  • 99
  • 171
  • 1
    Is the error because you are `return`ing rather than `select`ing? I don't use EF but looking at [this question](http://stackoverflow.com/questions/14735477/get-return-value-from-stored-procedure) it looks like you might need to use an output parameter if you want to keep the return. – petelids May 08 '15 at 16:37
  • @petelids: Thanks. That actually did help quite a bit and I've managed to pull pieces from that and get it working. Not sure it's the right or best way to do it, but at least I have something working for right now. – Matt Burland May 08 '15 at 18:03

0 Answers0