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.
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.)
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;
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
