How to set timeout for NHibernate LINQ statement

Question

I am using Fluent NHibernate for my ORM. In doing so I am trying to use the NHibernate LINQ syntax to fetch a set of data with the power of LINQ. The code I have works and executes correctly with the exception being that a timeout is thrown if it takes longer than roughly 30 seconds to run. The question I have is how do I extend the default 30 second timeout for LINQ statements via NHibernate?

I have already seen the posts here, here, and here but the first two refer to setting the DataContext's Timeout property, which does not apply here, and the third refers to setting the timeout in XML, which also does not apply because I am using Fluent NHibernate to generate the XML on the fly. Not only that but the post is 2 years old and Fluent NHibernate has changed since.

With the ICriteria objects and even HQL I can specify the timeout, however that is not the goal here. I would like to know how to set that same timeout and use LINQ.

Example code:

    using (var session = SessionFactory.OpenSession())
    using (var transaction = session.BeginTransaction())
    {
        var query = (from mem in session.Query<Member>()
                     select mem);
        query = query.Where({where statement});
        int start = (currentPage - 1) * max);
        if (start > 0)
            query = query.Skip(start).Take(max);
        else
            query = query.Take(max);

        var list = query.ToList();
        transaction.Commit();
        return list;
    }

This code (where statement does not matter) works for all purposes except where a timeout occurs.

Any help is appreciated. Thanks in advance!

Kepner

Check out the answer by kingpin2k. Worked great for me. – Chancey

Answer 1

Nhibernate has extended the IQueryable and added a few methods https://github.com/nhibernate/nhibernate-core/blob/master/src/NHibernate/Linq/LinqExtensionMethods.cs

var query = (from c in Session.Query<Puppy>()).Timeout(12);

or

var query = (from c in Session.Query<Puppy>());

query.Timeout(456);

Walford

Answer 2

I ended up setting the command timeout for the Configuration for Fluent NHibernate. The downside to this is that it sets the timeout for ALL of my data access calls and not just the one.

Example code:

.ExposeConfiguration(c => c.SetProperty("command_timeout", (TimeSpan.FromMinutes(10).TotalSeconds).ToString()))

I found this suggestion from this website.

Kepner

You should use the NHibernate.Cfg.Environment namespace when setting NHibernate properties. That way if the key changes, you're not stuck replacing the text. For command timeout you can do the following as Brandon suggested but using the constant: .ExposeConfiguration(c => c.SetProperty(NHibernate.Cfg.Environment.CommandTimeout, TimeSpan.FromMinutes(5).TotalSeconds.ToString()) – Anthroposophy

I tried doing the same. Did not work. It still takes the default value of 30s – Eclair

Answer 3

I've just spent fair amount of time fighting with this and hopefully this will save someone else some time.

You should use the .Timeout(120) method call at the very last moment to make sure it is used. TBH I'm not 100% sure on why this is but here are some examples:

WILL WORK

query = query.Where(x => x.Id = 123);

var result = query.Timeout(120).ToList();

DOESN'T WORK

query.Timeout(120);

query = query.Where(x => x.Id = 123);

var result = query.ToList();

If done like the second (DOESN'T WORK) example, it seems to fall back to the default System.Transaction.TransactionManager.DefaultTimeout.

Rafter

Happy for anyone to prove me wrong with a proper test, but I did this manually many times and reproduced it every time. I agree it will work after the query, as long as you don't add the additional Where clause and then do the .ToList(); – Rafter

i think you need reassign variable as query = query.Timeout(120); – Fanchie

Answer 4

Just in case anyone is still looking for this and finds this old thread too... Query.Timeout is deprecated. You should use WithOptions instead:

.WithOptions(o => o.SetTimeout(databaseTimeoutInSeconds))

Takahashi