Sql Server async problem?

[Andrew]

Hi

Recently I update my test solution from Nhibernate 4 to Nhibernate 5. This webapplication request data from a table on database (Sql Server 2010) and show in page results.

I update nhibenrate from version 4 to 5 and all works fine: my web application show the same result.

This is a query used:

session.Query<MyTable>().Where(t => t.Visible == true).OrderBy(t => t.Name).Select(t => t.Name).ToList()

So I wanted to try new async method. I tried:

session.Query<MyTable>().Where(t => t.Visible == true).OrderBy(t => t.Name).Select(t => t.Name).ToListAsync()

But now I have error (below). Why? What is wrong?Thanks

Invalid operation. The connection is closed.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

Exception Details: System.InvalidOperationException: Invalid operation. The connection is closed.

Stack Trace: 

[InvalidOperationException: Invalid operation. The connection is closed.]
   System.Data.SqlClient.<>c.<ExecuteDbDataReaderAsync>b__174_0(Task`1 result) +870930
   System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke() +77
   System.Threading.Tasks.Task.Execute() +47
   System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +99
   System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +61
   NHibernate.AdoNet.<ExecuteReaderAsync>d__68.MoveNext() +1121
   System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +99
   System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +61
   NHibernate.Loader.<GetResultSetAsync>d__21.MoveNext() +691
   System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +99
   System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +61
   NHibernate.Loader.<DoQueryAsync>d__6.MoveNext() +910
   System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +99
   System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +61
   NHibernate.Loader.<DoQueryAndInitializeNonLazyCollectionsAsync>d__1.MoveNext() +467
   System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +99
   System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +61
   NHibernate.Loader.<DoListAsync>d__34.MoveNext() +383


[GenericADOException: could not execute query
[ select MyTable0_1_.Name as col_0_0_ from [MyTable]... itinerari0_1_.Name asc ]
  Name:p1 - Value:True
[...]
   NHibernate.Loader.<DoListAsync>d__34.MoveNext() +494
   System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +99
   System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +61
   NHibernate.Loader.<ListIgnoreQueryCacheAsync>d__29.MoveNext() +308

Query on stack trace, if copied on sql server management studio works fine.

[Gunnar Liljas]

Did you await the result?

---

From: nhu...@googlegroups.com <nhu...@googlegroups.com> on behalf of Andrew <sbr...@gmail.com>
Sent: Saturday, February 17, 2018 6:12:00 PM
To: nhusers
Subject: [nhusers] Sql Server async problem?

 

--
You received this message because you are subscribed to the Google Groups "nhusers" group.
To unsubscribe from this group and stop receiving emails from it, send an email to nhusers+u...@googlegroups.com.
To post to this group, send email to nhu...@googlegroups.com.
Visit this group at https://groups.google.com/group/nhusers.
For more options, visit https://groups.google.com/d/optout.

[Andrew]

Ok I found the problem. It's on my code. Maybe can help somebody this solution.

My wrong code:

        public async Task<List<string>> GetList()
        {
            using (var session = _sessionFactory.OpenSession())
            {
                return session.Query<MyTable>().Where(t => t.Visible == true).OrderBy(t => t.Name).Select(t => t.Name).ToListAsync()
            }
        }

Problem in this cose is in ToListAsync method that return Task that is not yet processed, but session is closed immediately.

This is correct code:

        public async Task<List<string>> GetList()
        {
            using (var session = _sessionFactory.OpenSession())
            {
                var result = await session.Query<MyTable>().Where(t => t.Visible == true).OrderBy(t => t.Name).Select(t => t.Name).ToListAsync()
                return result.ToList();
            }
        }

[Gunnar Liljas]

You can ”return await .....ToListAsync”

[Andrew]

You're right. Thanks