实体框架中的OPENQUERY存储过程

I am trying to create a stored procedure that is getting values from a linked to our Sql Server DB2 server.In the stored procedure I have this query:

我正在尝试创建一个存储过程，从链接到我们的Sql Server DB2服务器获取值。在存储过程中，我有这样的查询：

DECLARE @CarID nvarchar(10)
DECLARE @TSQL varchar(8000)
SET @CarID = '1111'
  SELECT  @TSQL = 'SELECT * FROM OPENQUERY(LINKEDSERVER,''SELECT * FROM TestTable WHERE Column LIKE ''''' + @CarID + '%' + ''''''')'
  EXEC (@TSQL)

Everithing is working fine but when I add the stored procedure to the Entity model the signature of the procedure is:

Everithing工作正常但是当我将存储过程添加到Entity模型时，过程的签名是：

GetUsers(string):int

But when I run the procedure returns data rows. How can I modify the procedure to return a data set not an integer?

但是当我运行该过程返回数据行时。如何修改过程以返回不是整数的数据集？

1 个解决方案

#1

EF handles a stored procedure somewhat similar as a scalar function. EF doesn't know how many datasets and which columns will be selected in your stored procedure, therefore cannot generate the classes.

EF处理与标量函数有些类似的存储过程。 EF不知道将在存储过程中选择多少数据集和哪些列，因此无法生成类。

Best way to select something on a linked server is with a view. Simply create the view with the four part name and add it to your EF datamodel. Then EF will be able the generate the class.

在链接服务器上选择内容的最佳方法是使用视图。只需使用四个部件名称创建视图，然后将其添加到EF数据模型中。然后EF将能够生成类。

CREATE VIEW [dbo].[vTestTable]
AS
    Select * from [LINKEDSERVER].[DatabaseName].[Schema].[TestTable]
GO

Then in .NET

然后在.NET中

var result = db.vTestTable.Where(t=> t.Column.StartsWith(CarId)).ToList();

#1