Entity Framework generated representation of a sto

Question

My stored procedure implementation is simple:

create Procedure [dbo].[GetNegotiationInfo] (
    @negotiationId uniqueidentifier
) As

    select NegotiationId, SellerId from NegotiationMaster where negotiationId = @negotiationId

Then I expected to be to write the following code after updating my model.

   using (var db = new NegotiationContext())
            {
var query = db.GetNegotiationInfo(NegotiationId).FirstOrDefault();
}

But I am getting the error "int does not contain a definition for FirstOrDefault()". I know that Entity Framework can have trouble generating complex stored procedures with temp tables etc but obviously my stored procedure doesn't get any simpler than this. Please note my stored procedure is this basic for my StackOverflow question and is not the actual stored procedure I will be using.

Answer 1

Use SET NOCOUNT ON at the top of your procedure, the integer being returned is almost certainly the row count.

Like so:

ALTER Procedure [dbo].[GetNegotiationInfo] (
    @negotiationId uniqueidentifier
) As

SET NOCOUNT ON;

select  n.*, 
        s.firstname + ' ' + s.lastname sellername, 
        b.firstname + ' ' + b.lastname buyername,
        substring(s.firstname, 1, 1) + substring(s.lastname, 1, 1) as sellerinit,
        substring(b.firstname, 1, 1) + substring(b.lastname, 1, 1) as buyerinit,
        s.email selleremail, b.email buyeremail,
        a.ADDRESS1
from    negotiationMaster n
        inner join usermaster s on s.userid = n.sellerid
        inner join usermaster b on b.userid = n.buyerid
        inner join PropertyNegotiation p on p.NegotiationId = n.NegotiationId
        inner join MyOtherDb..PROPERTIES a on a.property_id = p.PropertyId
where   n.negotiationId = @negotiationId


return;

Answer 2

Stored procedures always return an integer. This is the status of the stored procedure call and will be set to NULL if not specified in the code. The general approach is that 0 indicates success and any other value indicates an error. Here is some documentation on the subject.

If you want to return a value from a stored procedure, use output parameters.

Alternatively, you might want a user defined function which returns a value to the caller. This can be a scalar or a table.

EDIT:

I would suggest that you look into user defined functions.

create function [dbo].[GetNegotiationInfo] (
    @negotiationId uniqueidentifier
)
returns table
As
    return(select NegotiationId, SellerId
           from NegotiationMaster
           where negotiationId = @negotiationId
          );

In SQL, you would call this as:

select NegotiationId, SellerId
from dbo.GetNegotiationInfo(NegotiationId);

EDIT (in response to Aaron's comment):

This discussion is about SQL-only stored procedures. Entity Framework wraps stuff around stored procedures. The documentation mentioned in the comment below strongly suggests that EF should be returning the data from the last select in the stored procedure, but that you should be using ExecuteFunction -- confusingly even though this is a stored procedure. (Search for "Importing Stored Procedures that Return Types Other than Entities" in the document.)

Answer 3

Its all about editing the "Function Import" in the Visual Studio Model Browser and creating a complex type for the return record set. Note, temp Tables in your stored procedure will be difficult for Entity Framework to inspect. see MSDN