Jaylen Jaylen -4 years ago 333
C# Question

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

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
...
...
...

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download