0

Our application queries the database primarily using Linq queries backed by Entity Framework but we sometimes call legacy stored procedures. Sometimes we want to replace an existing stored procedure with a Linq / Entity Framework query. Is this possible to do even if the stored procedure uses temporary tables? If so, how?

3

Honestly, why force a square peg into a round hole? Entity Framework supports calling stored procedures for a reason; because Entity Framework cannot address every use case that the database itself can support.

I would just use the right tool for the job (which in some of these cases appears to be stored procedures) and not worry about trying to create a faux "pure" architecture by forcing everything into Entity Framework.

0

Entity Framework doesn't support temporary tables in this manner. The "temporary" table would have to be defined as a permanent one in the database, perhaps named with a tmp prefix. You could then remove and add records in the usual way using Entity Framework.

  • But that permanent table would have a "global" scope, unlike temporary tables, which are local to the particular connection. One solution to that would be to generate a name for the table that is guaranteed to be unique, perhaps by concatenating a GUID. Also you would need to bypass Enitity Frameword to send DDL statements to the database for creating and deleting the tables. And you would not be able to use Entity Framework to populate or query the new tables. – JoelFan Jun 23 '15 at 3:47
  • The fundamental problem here is that EF expects a stable database schema. You can't have temporary tables in EF in the way that you describe. – Robert Harvey Jun 23 '15 at 4:00
  • If you used a table with global scope this way, you would have to insert an identifier of some kind with all the records you insert into the temp table, so that you could delete only the correct records from the temp table after the operation. Sounds like a pain, to me. – Craig Jun 23 '15 at 4:08
  • @Craig: Such is life without scoped temporary tables. – Robert Harvey Jun 23 '15 at 4:35
  • Which sounds like a good reason to stick with stored procedures. – Craig Oct 21 '16 at 19:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.