calling stored procedures with EF 4.0 and using output parameters

Say I have a stored procedure GetAddressInfo(addressId int, errorCode int output). The stored procedure returns AddressLine1 and State if addressId matches a row in the database. If row is not found, then the error code is set to non-zero value AND no select statement is fired. So it would look like this:

if (valid address id) begin
    Set errorCode = 1
    return
end

select AddressLine1, State from ....

When I use EF 4.0 to call such stored procedure, I get an Entity Framework exception when address id is invalid. The exception mentions that column AddressLine1 was not part of the data reader.

I was expecting no results (no records) and output error code parameter set to 1. Instead, I'm getting exceptions. I simply want to stop processing the rest of the stored procedure and return immediately. Seems like this is not supported very well by EF 4.0.

Any ideas on this?

Thanks in ad开发者_如何学JAVAvance!

---

This is one of the prime arguments against stored procs - business logic starts to end up in them. Im not saying Im voting either way - just note what you are experiencing is a problem and makes it a bit more difficult to unit test.

EF generally needs to know the fields that are being returned (depending how you have it mapped). How specifically are you calling this proc? directly on the context, or is it mapped to an entity operation (a select-read operation for example)

If its mapped to a read operation, you need to change how you are doing this. Your column output list should be consistent.

---

Why don't you make a check for checking the existence of the valid address before calling the SP by this SP will always return the valid value of address and that wont return any exception.

Might be it is happening because it always matched the record from the table and in case if it doesn't get the record it throws exception..

---

Well, your approach is a bit odd - at least to EF.

In certain cases, you do return AddressLine1 and State (so it was set up to expect those two), and in others, you don't... so depending on the input, the shape of your output is different. EF can't really deal with that...

One easy way to solve this would be to always return these two columns - if addressId is invalid, then just return NULL for each column.

---

If the exception message states that "column 'AddressLine1' was not part of the data reader" then it seems that the function returned without executing the select statement. But on the other hand you are trying to read the data reader values, where there are no values at all!!

Take a look at this detailed article on how to call a stored procedure in EF with output parameters. http://weblogs.asp.net/dwahlin/archive/2011/09/23/using-entity-framework-code-first-with-stored-procedures-that-have-output-parameters.aspx