5

I want to create a temp table in sql server by using Entity Framework. Is there any way I can do this? If I can create a temp table, my next question is, how I can read it?

Thanks in advance.

André

Andre
  • 1,044
  • 1
  • 11
  • 23
  • There may be other ways, but you could do what you require by creating stored procedures and linking them to your model. – Andrew Jun 21 '13 at 09:38
  • 1
    Yes I know that way but I dont like it much. – Andre Jun 21 '13 at 09:40
  • The way I dealt with a situation like this was to use a stored proc with a SQL Server Table variable. Constructing a view might also be an option depending on your needs/situation. But, basically, temp tables and table variables work best in the DB rather than trying to force a square peg into a round hole. – jfrankcarr Jun 21 '13 at 13:30
  • Why do you have CSLA tagged here? CSLA is not an ORM. – Joe Jul 03 '13 at 16:41

1 Answers1

2

Ok, so you don't like the stored procedures route....nor do I to be honest, but it's the quickest way I could think of doing it.

Based on that, I don't know of any easy way in EDM to create temp tables so my next suggestion would be to create local objects, which mimic the temporary table you wish to create. The name temporary obviously indicates you don't want them to live on the database indefinitely, so using in memory objects has the benefit of being much more controllable and (depending on your latency) quicker than making multiple calls to a database.

public class MyTempTable
{
    public string ID { get; set; }
    public string Column1 { get; set; }
    // your other columns here
}

List<MyTempTable> tempTable = new List<MyTempTable>();

Once you've created your list of objects you can do basically anything you'd normally do on the database table using Linq.

Andrew
  • 2,315
  • 3
  • 27
  • 42
  • 2
    I want to use the temp table to join it in some other sql statements, so in memory objects are no option. – Andre Jun 21 '13 at 09:47
  • You can do it with a little hack....http://stackoverflow.com/questions/9165288/trying-to-join-a-list-and-a-sql-table-using-linq-contains-method-still-gives-lo – Andrew Jun 21 '13 at 09:49
  • 1
    @Andrew - The Contains method works quite well, especially for smaller amounts of data, such as a lookup table or a date limited range. I do have some concerns with it building a huge IN clause for large amounts of data. – jfrankcarr Jun 21 '13 at 13:33