CODEWITHSUNDEEP
c#sql-serverentity-frameworksql-server-2012entity-framework-6
zpul
zpul

Reputation: 1595

EF 6.x and function with table-valued parameter

I have a DB function requiring a table-valued parameter as argument (@c).

CREATE TABLE Test
(
    CD varchar(10) not null
)
GO

INSERT INTO Test VALUES ('TEST')
GO

CREATE TYPE [CdTable] AS TABLE (CD varchar(10));
GO

CREATE FUNCTION TestTbl ( @x varchar(10), @c CdTable READONLY )
RETURNS TABLE
AS
RETURN
    SELECT t.CD
    FROM test t
    JOIN @c c ON t.CD = c.CD OR c.CD IS NULL
    WHERE t.CD = @x
GO

DECLARE @tt AS CdTable;
INSERT INTO @tt VALUES ('TEST');
SELECT * FROM TestTbl('TEST', @tt);

DELETE FROM @tt;
INSERT INTO @tt VALUES (NULL);
SELECT * FROM TestTbl('TEST', @tt);
GO

The function is built from the EF Designer (Database First) as this in the DbContext:

    [DbFunction("MyDbContext", "TestTbl")]
    public virtual IQueryable<TestTbl_Result> TestTbl(Nullable<System.String> x)
    {
        var xParameter = user.HasValue ?
            new ObjectParameter("x", x) :
            new ObjectParameter("x", typeof(System.String));

        return ((IObjectContextAdapter)this).ObjectContext.CreateQuery<TestTbl_Result>("[MyDbContext].[TestTbl](@x)", xParameter);
    }

If I call this function passing only the available x/@x parameter I get this exception:

ex  {"An error occurred while executing the command definition. See the inner exception for details."}  System.Exception {System.Data.Entity.Core.EntityCommandExecutionException}
ex.InnerException   {"An insufficient number of arguments were supplied for the procedure or function TestTbl."}    System.Exception {System.Data.SqlClient.SqlException}

I don't know how to pass the @c parameter to the function. Anyone can help?

Thanks in advance.

p.s.: I am using MS SQL 2012 (or newer)

Upvotes: 1

Views: 2972

Answers (1)

Sergey L
Sergey L

Reputation: 1492

You should use another method ExecuteStoreQuery that allows specifying table valued parameters (SqlDbType.Structured). 

    using (var table = new DataTable ())
    {
        table.Columns.Add("cs", typeof(string));
        foreach (var item in ITEMS)
            table.Rows.Add(item.CD.ToString());

        var param1 = new SqlParameter("@x", SqlDbType.NVarChar)
        {
            Value = myValue
        };
        var param2 = new SqlParameter("@c", SqlDbType.Structured)
        {
            Value = table
        };

        ((IObjectContextAdapter)this).ObjectContext.ExecuteStoreQuery<TestTbl_Result>(
            "select * from [TestTbl](@x, @c)", param1, param2);

    }

Upvotes: 1

Related Questions