EntityFrameWork and TableValued Parameter

bulkinsert entity-framework stored-procedures table-valued-parameters


I'm trying to call a stored procedure from EntityFramework which uses Table-value parameter.

But when I try to do function import I keep getting a warning message saying -

The function 'InsertPerson' has a parameter 'InsertPerson_TVP' at parameter index 0 that has a data type 'table type' which is currently not supported for the target .NET Framework version. The function was excluded.

I did a initial search here and found few posts which says It's possible in EntityFrameWork with some work arounds and few saying it's not supported in current versions.

Does any one know a better approach or solution for this problem?

10/18/2017 4:52:29 AM

Accepted Answer

I ended up doing this, Please note we are working on EF DataContext(not ObjectContext)

Executing a Stored procedure with output parameter

       using (DataContext context = new DataContext())
          ////Create table value parameter
          DataTable dt = new DataTable();

          DataRow dr = dt.NewRow();
          dr["Displayname"] = "DisplayName";
          dr["FirstName"] = "FirstName";
          dr["LastName"] ="LastName";
          dr["TimeStamp"] = "TimeStamp";             

          ////Use DbType.Structured for TVP
          var userdetails = new SqlParameter("UserDetails", SqlDbType.Structured);
          userdetails.Value = dt;
          userdetails.TypeName = "UserType";

          ////Parameter for SP output
          var result = new SqlParameter("ResultList", SqlDbType.NVarChar, 4000);
          result.Direction = ParameterDirection.Output;

          context.Database.ExecuteSqlCommand("EXEC UserImport @UserDetails, @ResultList OUTPUT", userdetails, result);

          return result == null ? string.Empty : result.Value.ToString();

My Table-Value-Parameter (UDT Table) script looks like this:

CREATE TYPE [dbo].[UserType] AS TABLE (
    [DisplayName]      NVARCHAR (256)   NULL,
    [FirstName]        NVARCHAR (256)   NULL,
    [LastName]         NVARCHAR (256)   NULL,
    [TimeStamp]        DATETIME         NULL

And my store procedure begins like

-- Add the parameters for the stored procedure here
@UserDetails UserType Readonly,
@ResultList NVARCHAR(MAX) output  

For Stored procedure without output parameter we don't need any ouput parameter added/passed to SP.

Hope it helps some one.

8/30/2015 3:17:27 PM

Popular Answer

Perhaps we could also consider the SqlQuery method:

public SomeResultType GetResult(List<int> someIdList)
    var idTbl = new DataTable();

    someIdList.ForEach(id => idTbl.Rows.Add(id));

    var idParam = new SqlParamter("SomeParamName", SqlDbType.Structured);
    idParam.TypeName = "SomeTBVType";
    idParam.Value = idTbl;

    // Return type will be IEnumerable<T>
    var result = DbContext.Database.SqlQuery<SomeResultType>("EXEC SomeSPName, @SomeParamName", idParam);

    // We can enumerate the result...

    var enu = result.GetEnumerator();
    if (!enu.MoveNext()) return null;
    return enu.Current;

Related Questions


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow