nHibernate - Populate a mapped function with namedQuery

Question

I'm using Fluent nHibernate for my data layer, and I have a class that is mostly populated through nHibernate/LINQ but in a few advanced usages, needs to be populated by a stored procedure.

The problem I have is the class mapping includes a Formula. When I call a nHibernate/LINQ function, the underlying variable is populated as expected; when I call the GetNamedQuery() function it throws an error:
Value cannot be null. Parameter name: fieldname

It's completely logical that for a NamedQuery, the Formula field isn't populated (obviously I want a subquery here rather than a SQL statement run for every single record returned!), but I'd like to be able to populate the Formula value from the stored procedure - or at least the query not to throw an error.

Is this possible?

Map

public class QuoteMap : ClassMap<Quote>
{
    public QuoteMap()
    {
        Id(x => x.Id).GeneratedBy.GuidComb();
        ...
        Map(x => x.ResponseCount).Formula("(SELECT COUNT(*) FROM QuoteResponse WHERE QuoteResponse.QuoteId = Id and QuoteResponse.Status = " + (int)QuoteResponseRepository.Status.Live + ")");
    }
}

Repository

// Works fine
public ICollection<Quote> GetAllByStatus(Status status)
{
    using (ISession session = NHibernateHelper.OpenSession())
    {
        var quoteQuery = (from quote in session.Query<Quote>()
                            where quote.Status == (int)status
                            select quote).ToList();

        return quoteQuery;
    }
}

// Dies horribly
public ICollection<Quote> GetListPendingByCompany(Guid companyId, Status status)
{
    using (ISession session = NHibernateHelper.OpenSession())
        return session.GetNamedQuery("QuoteGetListPendingByCompany")
                .SetGuid("Company_Id", companyId)
                .SetInt32("QuoteStatus", (int)status)
                .List<Quote>();
}

SQL

CREATE PROCEDURE [dbo].[QuoteGetListPendingByCompany] 
    @CompanyId uniqueidentifier,
    @QuoteStatus int
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
            Quote.*,
            (
                SELECT 
                        COUNT(*) 
                FROM    QuoteResponse
                WHERE   QuoteResponse.QuoteId = Quote.Id
            ) AS ResponseCount  -- Needs to populate what is currently a formula field
    FROM    Quote
    -- ... code removed
END

StoredProcsMap.hbm.xml

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" auto-import="true" assembly="QMP.Data" namespace="QMP.Data.Model">
        <sql-query name="QuoteGetListPendingByCompany" callable="true">
        <return class="QMP.Data.Model.Quote, QMP.Data" />
        <![CDATA[ 
        exec dbo.QuoteGetListPendingByCompany @CompanyId=:Company_Id,@QuoteStatus=:QuoteStatus
        ]]>
    </sql-query>
</hibernate-mapping>

Where is the code for the named query? Also there seems a mismatch between `"Company_Id"` and your SP param `@CompanyId`. — Rippo, Mar 26 '13 at 10:57
Edited to add the code for the named query though that part works fine in other places and when I remark out the formula line. You're right about the mismatch, but that's sorted out in the named query definition. — Dunc, Mar 26 '13 at 11:34
Ok I see now what you are trying to do, I am not sure its possible, you want to ignore the `FORMULA` column if using a `NamedQuery` ? — Rippo, Mar 26 '13 at 12:03
In an ideal world, yes - I want to populate what is a `FORMULA` when it's coming from a `NamedQuery`, given the `FORMULA` field is not possible in this situation. Failing that, just a `NamedQuery` that worked without having to remove the `FORMULA` would be adequate. — Dunc, Mar 26 '13 at 12:44

score 0 · Answer 1 · answered Mar 26 '13 at 11:00

0

You should not put the formula in the fluent mapping, this because you have it in your namedquery:

 Map(x => x.ResponseCount).Formula("(SELECT COUNT(*) FROM QuoteResponse WHERE QuoteResponse.QuoteId = Quote.Id and QuoteResponse.Status = " + (int)QuoteResponseRepository.Status.Live + ")");

Just map the field as your do with any other field and you will be fine:

Map(x => x.ResponseCount);

answered Mar 26 '13 at 11:00

Peter

27,590
8
64
84

Yes, but I need the formula for the GetAllByStatus() function. It's looking like my only option is to set that as a Map field and get ALL data population functions to run NamedQuery() calls, though this seems to defeat the purpose of using nHibernate. – Dunc Mar 26 '13 at 11:22

score 0 · Answer 2 · answered Mar 26 '13 at 12:05

Ok after your edit I can now see what you are trying to do, I am not sure its possible, you want to ignore the FORMULA column if using a NamedQuery that calls a SP?

What happens if you specify a ResultTransformer?

return session.GetNamedQuery("QuoteGetListPendingByCompany")
                .SetGuid("Company_Id", companyId)
                .SetInt32("QuoteStatus", (int)status)
                .SetResultTransformer(new AliasToBeanResultTransformer(typeof(Quote)))
                .List<Quote>();
}

Although I suspect it will still barf.

Then you will need to create a `QuoteDTO` that is NOT using a `Quote` and transform into this. — Rippo, Mar 26 '13 at 13:01

nHibernate - Populate a mapped function with namedQuery

2 Answers2

Linked