Entity Framework v4 - Simple Stored Procedure Select Statement from Linked Server (Open Query) Returning -1

Question

In SQL Server, I have created a stored procedure which fires a simple select statement (with SET NOCOUNT OFF) using Open Query to query data from a linked server. When querying this data in SQL Server, the correct results are brought back, e.g:

SELECT * FROM OPENQUERY(SERVER, ''SELECT * FROM db.table WHERE field = ' + '''' + '''' + @var+ '''' + ''''')'

I now have a C# WinForms application that uses Entity Framework v4 to access the data, and want to access this stored procedure in code.

I did the usual 'Update Model from Database' and added the stored procedure, then chose to add a function import (called getData for example). I then noticed after clicking 'Get Column Information', I received the following message:

'The selected stored procedure returns no columns'

At this point I clicked OK and then wrote some simple code to access the SP (as follows):

using(var context = new MyContext())
{
    var result = context.getData('paramdata');
}

When stepping through the code, result is set to '-1'. After doing some reading someone suggested to set NOCOUNT to OFF in the stored procedure which I did but made no difference.

I am not sure why this works from a query in SQL Server but not Entity Framework?

Is there a way I can get this to work?

Thanks in advance.

Answer 1

Because you're using OpenQuery the column list is not known to the SQL server.

Try creating a table variable, inserting the results of your openquery into that, and selecting from the table variable.

declare @t table(ID int, value nvarchar(50))
insert @t (ID, value)
select Id,Value FROM OPENQUERY(SERVER, 'SELECT * FROM db.table')

select ID, Value from @t