How to create a temporary table and use it in the same connection with Entity Framework?

Question

I am trying to execute a three raw queries with Entity Framework.

The first query will basically create a temporary table. The second query will add an index on the temporary table. Finally, the second query will join to the temporary table to other table to get a final dataset.

But every time I run my code, get the following error

invalid #allRecords object.

Here is what I have done

using (BaseContextdb = new BaseContext())
{
    using (var dbContextTransaction = db.Database.BeginTransaction())
    {
        try
        {
            db.Database.ExecuteSqlCommand("SELECT col1, col2, col3 " +
                                          "INTO #allRecords " +
                                          "FROM someTable " +
                                          "WHERE col5 = 'blab' " +
                                          "CREATE INDEX d ON #allRecords(col1, col2); ");

            var results = db.Database.SqlQuery<ResuleModel>(this.GetQuery()).ToList();

            db.SaveChanges();

            dbContextTransaction.Commit();
        }
        catch (Exception)
        {
            dbContextTransaction.Rollback();
        }
    }
}

how can I correctly create temporary table with Entity Framework?

UPDATED

Here is the query that is returned by this.GetQuery()

SELECT b.*, c.* 
FROM b
INNER JOIN #allRecords AS a ON a.col1 = v.col1 AND a.col2 = b.col2
INNER JOIN c ON c.Id= b.Id
...
...
...

Try using the Table attribute on the class so [Table("#allRecords")] public class ResuleModel{}... — Alex Krupka
– Alex Krupka, Commented Jul 25, 2016 at 23:51
On the ResuleModule class see stackoverflow.com/questions/20184644/… for an example --Second answer. And no I don't have a specific reason why I chose that link prob cause when I googled an example of Table Attribute usage it came up first — Alex Krupka
– Alex Krupka, Commented Jul 25, 2016 at 23:53
That would work if I wasn't using joins. the method this.GetQuery() return a complex query. I will update my question which what this.GetQuery() returns — Jaylen
– Jaylen, Commented Jul 25, 2016 at 23:55
Why not just place the temp table and query in one statement? (had a similar issue while using dapper and just combined the statement) — Alex Krupka
– Alex Krupka, Commented Jul 26, 2016 at 0:05

noelicus · Accepted Answer · 2017-05-19 08:23:55Z

4

Entity Framework doesn't work well with temporary tables.

Instead, you might want to look at Dapper. It is much cleaner; besides, you can use EF and Dapper in same project side-by-side. For example,

using (IDbConnection conn = new SqlConnection(DataBaseConnectionString))
{
   conn.Open();

   // If you want transaction, place it inside the query. 
   var entities = conn.Query<ResuleModel>(@"SELECT col1, col2, col3 ...");

   result = entities.ToList();
}

FYI: Make sure you execute the query in SSMS before using it in Dapper.

edited May 19, 2017 at 8:23

noelicus

15.1k4 gold badges96 silver badges116 bronze badges

answered Jul 26, 2016 at 0:11

Win

62.4k13 gold badges106 silver badges190 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Win Over a year ago

https://www.google.com/search?q=what%20is%20SSMS

Peter Csala · Accepted Answer · 2020-07-30 15:47:06Z

1

Need to Open a new connection before you create, insert or query the temp table.
The opened connection will not be automatically closed until context disposal.

db.Database.Connection.Open();

edited Jul 30, 2020 at 15:47

Peter Csala

23.7k16 gold badges51 silver badges96 bronze badges

answered Jul 30, 2020 at 14:06

Hongguang Li

111 bronze badge

1 Comment

AndriiL Over a year ago

db.Database.OpenConnection() works in EF Core 2.2. It is better to wrap this code inside IDisposable and do db.Database.CloseConnection() in the end

Collectives™ on Stack Overflow

How to create a temporary table and use it in the same connection with Entity Framework?

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related