iGod iGod - 2 months ago 12
C# Question

How to use temporary table in EF6

I need to read data from a excel file store it in temporary table (devices temp table) and after all data is copied in to this temporary table. These records are updated in actual table(userDevices) and previous enteries need to be removed.

I have used EF6 in my project. and accordingly I have mapped entity "Device" with the "userDevices" table. how will I leverage entity framework to insert data in temp table and then replace it in actual table.

Is stored proc is the only option? Or can I utilize EF for the same?

Answer

The only way to use temp tables is to have their creation, manipulation, insertion and ultimately data extraction all done in stored procedures. Once those are setup, then EF can call and utilize them.

As an aside EF does not browse the tempDb schema when it generates the table mappings; hence any temp table regardless of session (# vs ##) status cannot be used; due to the nature of temp tables themselves.