locked
Stored procedure output parameters RRS feed

  • Question

  • The Entity Framework method of calling stored procedures (especially with output parameters) seems really broken.  Please tell me I'm missing something.  Specifically:

    1. You should not have to give the ObjectParameter passed into the SP method a name - it already has one.  This is "denormalized" in the worst way.
    2. Those ObjectParameter parameters should be typed since clearly the EF knows the type.  Why aren't they?
    3. And then on top of that, I can't use ExecuteCommand() to just execute a stored procedure - I have to import it.  Why?  Because of this, in theory I need to use a separate database connection to execute non-imported procs, which is bad for performance especially in transactions.
    Tuesday, March 23, 2010 3:11 PM

Answers

  • I realize now that the reason they are ObjectParameters is because they can't be simple ref parameters because EF hasn't consumed any data readers by the time the method returns.  In the DAL that we had designed, we modeled stored procs as classes not methods (inheriting from IDbCommand) and this solved that problem.  I would certainly suggest that EF gets a big fail on stored procedures at the moment, and given where MSFT are in the ship cycle, I'm not too hopeful that EF can work for us.  People claim that EF is for "large databases" over simpler options like LINQ to SQL or Subsonic, and yet it seems to fall down pretty miserably as soon as you want to do anything complex, like procs or partitioned models.
    Wednesday, March 24, 2010 1:55 PM

All replies

  • I realize now that the reason they are ObjectParameters is because they can't be simple ref parameters because EF hasn't consumed any data readers by the time the method returns.  In the DAL that we had designed, we modeled stored procs as classes not methods (inheriting from IDbCommand) and this solved that problem.  I would certainly suggest that EF gets a big fail on stored procedures at the moment, and given where MSFT are in the ship cycle, I'm not too hopeful that EF can work for us.  People claim that EF is for "large databases" over simpler options like LINQ to SQL or Subsonic, and yet it seems to fall down pretty miserably as soon as you want to do anything complex, like procs or partitioned models.
    Wednesday, March 24, 2010 1:55 PM
  • Thank you - we know that we need to make the sproc story more solid and this kind of feedback can be helpful. I've routed it to the team.
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Friday, April 9, 2010 12:28 AM
    Moderator