I want to do some work in SQL, populating a temp table, and then JOIN to that temp table using an NHibernate CreateSQLQuery to get my final results. We are on version 1.2.0.4000 of NHibernate and I seem to be having problems accessing a temp table in a later query, even though I'm in the same session (I believe this means I'm in the same SQL Session/Connection as well). Below is a simplified version of my code
public void Work()
{
SqlConnection connection = (SqlConnection)Session.Connection;
SqlCommand command = new SqlCommand
{
CommandType = CommandType.Text,
CommandText = "SELECT ID = 1 INTO #TempTable",
Connection = connection,
};
if ( Session.Transaction != null && Session.Transaction.IsActive )
{
Session.Transaction.Enlist( command );
}
command.ExecuteNonQuery();
// Simplified example, I should have a temp table #TempTable with 1 row containing the values ID = 1
// trying to fetch a list of Account objects where ID exists in #TempTable.
// At this point, I get an error "Invalid object name '#TempTable'."
IList<Account> accounts = Session.CreateSQLQuery(@"
SELECT *
FROM Account a
JOIN #TempTable tt
ON a.ID = tt.ID")
.AddEntity("a", typeof(Account))
.List<Account>();
// Do some work on accounts list
}
sessions get connections on demand, it is not guaranteed to get the same connection back. There are two possibilities to get the same connection every time:
sessionFactory.OpenSession(myConnection); which ties the session to the provided connection.Option 1 is definitly easier
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments