stored procedure dynamic sql in entity framework 4.1

Question

I have a problem while executing different resultset on the basis of parameter. I have a sp which exec two query on the basis of parameter, both queries executes once e.g.:

CREATE PROCEDURE [dbo].[ShowMessages]        
    @context  int,
AS
      BEGIN
      --CALENDAR ADMIN QUEUE
      if @context = 1 
            BEGIN
             Select CustomerName, CustomerAddress from Customer
            END
      ELSE if @queueContext = 2  OR @queueContext = 6
            BEGIN
             Select EmployeeName, EmployeeAddress from Employee
            END
END

=== I mapped it with my complex type: EF generates complex type with two fields CustomerName, CustomerAddress. EF throws error of IDataReader. but This error occurs rarely even i called up by first query.

How can i achieve this functionality?

Phillippe Francois · Accepted Answer · 2012-04-09 08:56:30Z

1

The column names in the table returned from your SP will be different depending on the 'context', so EF will only be able to map them to entity properties in one of those contexts. It's bad practice to return different column names from the same stored procedure, so what you need to do is make them consistent for all contexts/scenarios.

You can do this by using the AS keyword like so:

SELECT CustomerName AS Name, CustomerAddress AS Address FROM Customer

And

SELECT EmployeeName AS Name, EmployeeAddress AS Address FROM Employee

answered Apr 9, 2012 at 8:56

Phillippe Francois

563 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Yogesh Sharma Over a year ago

Thanks for your quick response, but in my case, there is architectural obligation, Is there any solid proof that it can't possible?

Ladislav Mrnka Over a year ago

There is no proof needed. EF maps column names from result set to property names of complex type at design time and only single set of mapping is possible. So you can either map first or second query but not both. Use same names for columns in query or use two different SPs and call them based on context value.

Phillippe Francois Over a year ago

I don't have solid proof, but I doubt that EF could deal with this scenario as it's a bit unusual. When you say it's an obligation, does that mean that you can't change the SP? If so, you may need to write another SP to wrap this one and normalise the names as I suggested, in that SP.

Collectives™ on Stack Overflow

stored procedure dynamic sql in entity framework 4.1

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related