Entity Framework and SQL Server temp tables

c# entity-framework sqlbulkcopy sql-server tempdb

Question

I want to utilize Entity Framework (EF) for data access in a legacy application that we are converting to C#. The database is SQL Server 2012, and I'm presently using EF 5.0. The original program processed a great deal of data. The original program used "oetemp" tables extensive as part of that processing. The majority of these "temp" tables were really genuine tables that were created and deleted in their own "temp" database in SQL Server rather than SQL Server temporary tables (example: #sometable in tempdb).

I want to be able to utilize temporary tables (of any kind) as part of my data processing now that I am working with C# EF. I've been researching how to use temporary tables with EF, and what I've learned so far is that although you can construct tempdb temporary tables using SQLBulkCopy, EF Linq cannot be used to query against them since they are not a standard data structure. The second method I've heard about is to give a table-valued argument to a stored procedure that will do the processing. As a result, we would be forced to have a large number of stored procedures (sprocs) and a lot of the business logic.

Exists an alternative method for creating and using temporary SQL Server tables? Are there any new capabilities in this area in EF 6?

1
3
2/3/2014 9:43:12 PM

Popular Answer

In EF, I've never seen the usage of temp tables.

I'm guessing that this is how the historical code functioned, however if the data process in issue is so intensive, it's probably best to leave it as a stored procedure. With EF, running stored procedures is simple and you can even get the output as a model object.

You could also imitate a temp table in EF by using a normal table and only filtering it to the current session if you really don't want to utilize a stored method (through the use of a GUID or some other throw-away synthetic key). Although this method works rather well, it has the drawback of requiring table cleanup when your operation is complete.

3
2/3/2014 10:01:22 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow