存储过程中的表值参数和实体框架4.0

本文介绍了存储过程中的表值参数和实体框架4.0的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

这是我为此创建的类型，在SQL Server 2008中有一个称为GetPrices的存储过程，名为StoreIDs TVP：

CREATE TYPE integer_list_tbltype AS TABLE（n int）

我想从我的实体框架调用SP。但是，当我尝试将存储过程添加到EDM时，我会收到以下错误：

函数GetPrices在参数索引2处有一个参数StoreIDs具有不支持的数据类型表类型。该功能被排除。

有没有任何解决方法？任何想法？

Fabio

解决方案

我同意，在这种情况下，CSV sting是最好的解决方案。 我想提出更简单的方法来拆分csv字符串，而不需要使用CTE创建表和函数：

声明@separator char（1）; set @separator ='，'; ;将baseCte作为（select left（@ValueList，charindex（@separator，@ValueList） - 1））作为Value， substring（@ValueList，charindex（@separator ，@ValueList）+ 1，len（@ValueList）） as rest union all select left（rest，charindex（@separator，rest） - 1）as Value， substring（rest，charindex（@separator，rest）+ 1，len（rest））from baseCte 其中len（rest）> 1 ）从baseCte的值选项（MAXRECURATION 0）;

I have a stored procedure in SQL Server 2008 called 'GetPrices' with a Table-Valued Parameter called 'StoreIDs'.

This is the type i created for this TVP:

CREATE TYPE integer_list_tbltype AS TABLE (n int)

I would like to call the SP from my Entity Framework. But when I try to add the Stored Procedure to the EDM, i get the following error:

The function 'GetPrices' has a parameter 'StoreIDs' at parameter index 2 that has a data type 'table type' which is not supported. The function was excluded.

Is there any workaround this? Any thoughts?

Fabio

解决方案

I agree that passing in a CSV sting is the best solution in this case. I would like to propose simpler way to split csv string, without creating tables and functions, by using CTE:

declare @separator char(1); set @separator = ','; ;with baseCte as (select left(@ValueList, charindex(@separator, @ValueList) - 1) as Value, substring(@ValueList, charindex(@separator, @ValueList) + 1, len(@ValueList)) as rest union all select left(rest, charindex(@separator, rest) - 1) as Value, substring(rest, charindex(@separator, rest) + 1, len(rest)) from baseCte where len(rest) > 1 ) select Value from baseCte OPTION (MAXRECURSION 0);