Geek asks and answers

Entity Framework and SQL Server Temporary Tables

We have an outdated application that we are switching to C #, and I would like to use the Entity Framework (EF) to access the data. SQL Server 2012 is a database, and I'm currently using EF 5.0. The original application did a lot of data processing. As part of this processing, extensive use of temp tables was used in the original application . Most of these "temp" tables were not really SQL Server temporary tables (for example, #sometable in tempdb), but real tables were 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 temporary tables (of any type) as part of my data processing. I did some search queries on using temporary tables with EF - and so far I have found that you can create tempdb temp tables using SQLBulkCopy, but there is no way to query them using EF Linq, since they are not part of the normal data structure . Another option I read about is to use a stored procedure to process and pass its table-dependent parameter. This would make us have thousands of stored procedures - and put most of the business logic in sprocs.

Is there any other way to create and use SQL Server temporary tables? Are there any additional features in EF 6 in this area?

+3
source share
1 answer

I have never seen temporary tables used in EF.

If the data process in question is so intensive, it's probably best to leave it as a stored procedure (I assume the old code works this way). EF can run stored procedures without problems; You can even return the results as a model.

If you really do not want to use the stored procedure, you can also model the temporary table in EF using a regular table and simply filter it for the current session (using a GUID or some kind of synthetic key). This technique works quite well, but has the disadvantage of having to clear the garbage data from the table when executing your procedure.

+3
source

More articles:


All Articles