Fluent NHibernate and Stored Procedures

Question

I have a basic Customer/Order/OrderItem/Product object graph. Customer has Many Orders, Order has Many Order Items, Product has many Order Items. These are successfully mapped using FNH.

I've hit a snag with configuring a stored procedure & fluent-nhibernate. There is not a native way to map stored procedures in fluent-hibernate FNH (version 1.0 RTM). There was a solution here about adding parts to class mappings but the AddPart call has been taken out of the release of FNH.

The stored procedure is simple:

CREATE PROCEDURE [dbo].[OrderCountByCustomer] 
AS
BEGIN
    SET NOCOUNT ON;

    SELECT 
     c.name as [Customer.Name],
     CAST(count(o.id) as NVARCHAR) as [Customer.OrderCount]
    FROM customer c
     LEFT OUTER JOIN [order] o
     ON o.customer_id = c.id
    GROUP BY c.name

END

There's a CustomerOrderSummary.hbm.xml in

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="NVAble.Orders.Core" namespace="NVAble.Orders.Core">
    <sql-query name="OrderSummary">
     <return class="CustomerOrderSummary">
      <return-property column="Customer.Name" name="CustomerName" />
      <return-property column="Customer.OrderCount" name="OrderCount" /> 
     </return>
     EXEC [OrderCountByCustomer]
    </sql-query>
</hibernate-mapping>

Here is the CustomerOrderSummary class def:

namespace NVAble.Orders.Core
{
    public class CustomerOrderSummary
    {
     virtual public string CustomerName { get; set; }
     virtual public string OrderCount { get; set; }

     public override string ToString()
     {
      return string.Format("{0} {1}", CustomerName, OrderCount);
     }
    }
}

However, when try to start a NH session i get error in named query OrderSummary with no other details.

I'm probably missing something really simple that maps the CustomerOrderSummary class to the procedure, I don't know. That domain object obviously doesn't map directly to a table in the data base so I'm unsure if having a normal <class /> HBM mapping would work?

Thanks in advance!

Answer 1

Ok, so after a bit more investigation. I needed a mapping for the Domain Class as well as a named query hbm.xml file.

In my configure class i have

  config.Mappings(x =>
  {
     x.FluentMappings.AddFromAssemblyOf<CustomerMapping>().ExportTo(schemaPath);
     x.HbmMappings.AddFromAssemblyOf<CustomerOrderSummary>();
   });

Only downside is that I need to manually create the xml mapping for the stored procedure, I can't use FNH at the current time

Answer 2

i did a write-up on mapping SPs with nhibernate:

http://me.isit.gd/post/NHibernate-stored-p.aspx

does that help at all?

w://