3

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?

Clinemi
  • 906
  • 6
  • 20
  • 33
  • You could setup a different EF context for the temp db. Though you couldn't destroy them after use and would have to rely on clearing those tables, the fastest way through sprocs. – jamesSampica Feb 03 '14 at 21:55
  • If you pull a bunch of records into memory using EF it is essentially a temp table, except without all the goodness of a database engine at your disposal. The question is how much data are we talking about that typically goes into one of these temp tables? – Eric Scherrer Feb 03 '14 at 22:07
  • Yes, EF can give me a sort of in memory temp table, but what often happens for us is (for example)- we need to get a menu of items associated with a particular customer. And then off of that "temp table" we need to lookup prices, availablity, last invoice dates, etc - for each of those items in the menu. With EF, I have to do individual look-ups for each of those items against the SQL Server tables. Instead, if I can use a SQL Server temp table to run the queries against all these items at once, the performance is way, way, better. – Clinemi Feb 03 '14 at 23:53

1 Answers1

3

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.

Ash8087
  • 643
  • 6
  • 16
  • Actually, the legacy application used both in-memory temp tables, sql server temp tables, and (sometimes) stored procedures. The business logic was mostly in the code - but used temp tables to do intermediate processing - much like you would do with a sub-query or common table expression. At some point though, the queries become to complex, and need to be broken apart into separate queries (e.g. temp tables) – Clinemi Feb 04 '14 at 00:02
  • Filtering on an existing table does not fit our needs because our temp tables rarely have the same structure as the permanent tables. – Clinemi Feb 04 '14 at 00:07
  • From your response it looks like I will either have to abandon EF, or put a lot of the business logic in the stored procedures. – Clinemi Feb 04 '14 at 00:09