Named query not known error trying to call a stored proc using Fluent NHibernate

Question

I'm working on setting up NHibernate for a project and I have a few queries that, due to their complexity, we will be leaving as stored procedures. I'd like to be able to use NHibernate to call the sprocs, but have run into an error I can't figure out. Since I'm using Fluent NHibernate I'm using mixed mode mapping as recommended here. However, when I run the app I get a "Named query not known: AccountsGetSingle" exception and I can't figure out why. I think I might have a problem with my HBM mapping since I'm not very familiar with using them but I'm not sure.

My NHibernate configuration code is:

private ISessionFactory CreateSessionFactory()
{
    return Fluently.Configure()
        .Database(MsSqlConfiguration.MsSql2005
            .ConnectionString((conn => conn.FromConnectionStringWithKey("CIDB")))
                .ShowSql())
        .Mappings(m => 
            {
                m.HbmMappings.AddFromAssemblyOf<Account>();
                m.FluentMappings.AddFromAssemblyOf<Account>();
            })
        .BuildSessionFactory();
}

My hbm.xml file is:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
    <sql-query name="AccountsGetSingle">
        <return alias="Account" class="Core, Account"></return>
        exec AccountsGetSingle
    </sql-query>
</hibernate-mapping>

And the code where I am calling the sproc looks like this:

public Account Get()
{
    return _conversation.Session
        .GetNamedQuery("AccountsGetSingle")
        .UniqueResult<Account>();
}

Any thoughts or ideas would be appreciated. Thanks.

Update: @kibbled_bits's suggestion get me the end result that I'm looking for (Being able to call a Stored Procedure from NHibernate), but I still don't know why the approach I have listed above doesn't work. I'm still curious as to why since it might provide valuable insight into future problems.

Pugging

Answer 1

When I have to use stored procedures (which only occurs when I'm forced to). I much rather use the following method to execute them:

var list = Session.CreateSQLQuery("exec GetCustomerByNaturalKey ?, ?")
.AddEntity(typeof(Customer))
.SetInt32(0, customerNo)
.SetDateTime(1, createdDate)
.List<Customer>();

The first parameter to .SetInt32/DateTime is just the ordinal position of the parameter.

Edaedacious

I don't think I knew that you could call a sproc using .CreateSQLQuery(), though it makes perfect sense. I'll give it a shot when I get to work in the morning. – Pugging

This worked perfectly. I was able to successfully call my stored procedure without any issues. Thanks. – Pugging

+1 awesome sauce. Other answers involve mapping an hbm snippet. This is much better. – Replace

Answer 2

I got the same error message and what solved it for me was to ensure that my hbm.xml file property "Build action" was set to "Embedded Resource", so you may want to give it another try.

Monopteros

I avoid the procs as much as possible, so the need to do this is infrequent and I keep forgetting to do this. – Socratic

Answer 3

I have been caught out by this error a number of times.

Two other issues can cause this.

Not adding the hbm mapping.

In fluent I have the following.

var config = Fluently.Configure()
              .Database(sqlConfig)
              .Mappings(c => c.AutoMappings.Add(AutoMap.AssemblyOf<AdvertView>(new QueryAutomappingConfiguration()).UseOverridesFromAssemblyOf<AdvertViewMappingOverride>()))
              .Mappings(c => c.HbmMappings.AddClasses(typeof(AdvertView), typeof(RelatedAdvertView), typeof(CompanyAtoZListingView)));

Where I miss the view class out (which pairs up with an hbm file with the mapping information). I get the error.

When I added the new view 'typeof(CompanyAtoZListingView)' it worked fine.

    .Mappings(c => c.HbmMappings.AddClasses(typeof(AdvertView), typeof(RelatedAdvertView), typeof(CompanyAtoZListingView)));

Also do check in the hbm file to make sure the parameters are correct.

Rodi