Entity Framework and SQL Server temp tables

c# entity-framework sqlbulkcopy sql-server tempdb

Question

We have a legacy application which we are moving to C#, and I would like to use Entity Framework (EF) for data access. SQL Server 2012 is the database, and currently I am using EF 5.0. The original application did a lot of data intensive processing. As part of that processing, the original application made extensive use of “temp” tables. Most of these “temp” tables were not actually SQL Server temp tables (e.g. #sometable in tempdb), but real tables created and destroyed in their own “temp” database in SQL Server.

Now, that I am working with C# EF I want to be able to use temp tables (of either type) as part of my data processing. I have done some googling on using temp tables with EF - and so far I have found that you can create the tempdb temp tables using the SQLBulkCopy, but there is no way to query against them using EF Linq, as they are not part of the normal data structure. The other option I have read about - is using a stored procedure to do the processing and passing it a table valued parameter. This would force us to have thousands of stored procedures - and put a great deal of the business logic in sprocs.

Is there any other way to create and use temporary SQL Server tables? Does EF 6 have any added capabilities in this area?

Popular Answer

I've never seen temp tables used in EF.

If the data process in question is that intense, it's probably better to leave it as a stored procedure (I'm assuming this is how the legacy code worked). EF can run stored procedures with no problem; you can even get the results back as a model entity.

If you really don't want to use a stored procedure, you could also simulate a temp table in EF by using a regular table and just filtering it to the current session (through the use of a GUID or some other throw-away synthetic key). This technique works fairly well, but has the disadvantage of needing to clean the garbage data out of the table when your procedure is done.



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why