Nhibernate n+1 with ternary relationship. Want the

Question

I'm having a huge issue with nhibernate n+1 and nothing I try seems to fix the problem. Nhibernate profiler still shows n+1 selects hitting the database.

Here's my model:

public class CustomerGroup : CoreObjectBase
{
    public virtual long GroupId { get; set; }

    public virtual Site Site { get; set; }

    public virtual IList<Customer> Customers { get; set; }

    public virtual string Name { get; set; }
    public virtual string DisplayName { get; set; }
    public virtual CustomerGroupStatus Status { get; set; }

    public CustomerGroup()
    {
        Customers = new List<Customer>();
    }
}

And my Customer

public class Customer : CoreObjectBase
{
    public virtual int CustomerId { get; set; }
    public virtual Site Site { get; set; }
    public virtual CustomerType CustomerType { get; set; }
    public virtual CustomerName Name { get; set; }
    public virtual Address Address { get; set; }
    public virtual ContactInfo ContactInfo { get; set; }

    public virtual IList<Invoice.Invoice> Invoices { get; set; }

    public virtual IList<ItemBase> Payments { get; set; }

    public virtual CustomerOptions Options { get; set; }
}

And the options

public class CustomerOptions : CoreObjectBase
{
    public virtual int CustomerOptionsId { get; set; }

    private int CustomerId { get; set; }
    private Customer Customer { get; set; }

    public virtual bool PortalSignInDisabled { get; set; }
    public virtual CustomerGroup Group { get; set; }

    protected CustomerOptions()
    {

    }

    public CustomerOptions(Customer customer)
    {
        Customer = customer;
    }

    public virtual Customer GetCustomer()
    {
        return Customer;
    }
}

And finally, my invoices

public class Invoice : CoreObjectBase
{
    public virtual long InvoiceId { get; set; }

    private string SiteId { get; set; }
    private string CustomerId { get; set; }

    [Required]
    [StringLength(50)]
    public virtual string InvoiceNumber { get; set; }

    public virtual decimal Amount { get; set; }
    public virtual decimal OpenAmount { get; set; }
    public virtual decimal ClosedAmount { get; set; }

    public virtual InvoiceStatus Status { get; set; }
    public virtual DateTime? DateDue { get; set; }
    public virtual DateTime? InvoiceDate { get; set; }

    public virtual DateTime Created { get; set; }
    public virtual DateTime Modified { get; set; }

    public virtual Site Site { get; set; }
    public virtual Customer Account { get; set; }

    public virtual IList<InvoiceLineItem> LineItems { get; set; }
    public virtual IList<InvoicePayment> Transactions { get; set; }


    public Invoice()
    {
        Created = DateTime.Now;
        Modified = DateTime.Now;

        Site = new Site();
        Account = new Customer();

        LineItems = new List<InvoiceLineItem>();
        Transactions = new List<InvoicePayment>();
    }

    public override bool Equals(object obj)
    {
        return base.Equals(obj);
    }

    public override int GetHashCode()
    {
        return base.GetHashCode();
    }
}

And now my customer mapping

public sealed class CustomerMap : ClassMap<Customer>
{
    public CustomerMap()
    {
        Table("Customers");

        Id(x => x.CustomerId).GeneratedBy.Identity();

        Map(x => x.CustomerType).CustomType<CustomerType>();
        Map(x => x.DriversLicense).CustomType<TrimmedString>();
        Map(x => x.LicenseState).CustomType<TrimmedString>();
        Map(x => x.Notes).CustomType<TrimmedString>();

        References<Site>(x => x.Site, "SiteId");

        HasOne<CustomerOptions>(x => x.Options)
            .Cascade.All();

        Component(x => x.Name, y =>
        {
            y.Map(x => x.Name1).CustomType<TrimmedString>();
            y.Map(x => x.Name2).CustomType<TrimmedString>();
        });

        Component(x => x.Address, y =>
        {
            y.Map(x => x.Address1).CustomType<TrimmedString>();
            y.Map(x => x.Address2).CustomType<TrimmedString>();
            y.Map(x => x.City).CustomType<TrimmedString>();
            y.Map(x => x.State).CustomType<TrimmedString>();
            y.Map(x => x.ZipCode).CustomType<TrimmedString>();
            y.Map(x => x.Country).CustomType<TrimmedString>();
        });

        Component(x => x.ContactInfo, y =>
        {
            y.Map(x => x.EMail).CustomType<TrimmedString>();
            y.Map(x => x.Fax).CustomType<TrimmedString>();
            y.Map(x => x.Phone1).CustomType<TrimmedString>();
            y.Map(x => x.Phone2).CustomType<TrimmedString>();
        });

        HasMany<FTNI.Core.Model.Invoice.Invoice>(x => x.Invoices)
            .KeyColumn("CustomerId")
            .Inverse()
            .Cascade.All()
            .Where("Status = 0")
            .OrderBy("DueDate, InvoiceDate")
            .Fetch.Join();
    }
}

and my invoices mapping

    public InvoiceMap()
    {
        Table("InvoiceView");

        Map(x => x.InvoiceId).Generated.Always();

        CompositeId()
            .KeyProperty(Reveal.Member<FTNI.Core.Model.Invoice.Invoice>("SiteId"))
            .KeyProperty(Reveal.Member<FTNI.Core.Model.Invoice.Invoice>("CustomerId"))
            .KeyProperty(x => x.InvoiceNumber);

        Map(x => x.Amount);
        Map(x => x.Created).Generated.Insert();
        Map(x => x.ClosedAmount);
        Map(x => x.DateDue, "DueDate");
        Map(x => x.InvoiceDate);
        Map(x => x.OpenAmount);
        Map(x => x.Status).CustomType<InvoiceStatus>();

        References<Site>(x => x.Site, "SiteId");
        References<Customer>(x => x.Account, "CustomerId");

        HasMany<InvoiceLineItem>(x => x.LineItems)
            .KeyColumns.Add("SiteId", "CustomerId", "InvoiceNumber")
            .Cascade.All();

        HasMany<InvoicePayment>(x => x.Transactions)
            .Where("Status IN (0, 1)")
            .KeyColumns.Add("SiteId", "CustomerId", "InvoiceNumber")
            .Cascade.All();
    }

I have to join to the other tables on those three fields because one of our clients completely dumps their data and reloads all invoices from scratch (don't ask why). So, in order to maintain connectivity, I join to those tables on fields that allow the new invoice to hook back up with the refreshed data.

What I am trying to do is show all invoices for all members in a group, separated by customer (ordered on customer name) and then have the invoices ordered by due date.

so, my site looks something like this:

Customer Name (number)

• Invoice 1 information
• Invoice 2 information
• Invoice 3 information

Next Customer (number)

• Invoice A information
• Invoice B information
• Invoice C information

So, I made a query

results = Session.CreateQuery(String.Format(@"select distinct customer from Customer customer join fetch customer.Invoices where customer.Options.Group.GroupId = {0}", 
                groupId)).List().Cast<Customer>();

This still causes an N+1 issue. Any ideas on how to make the query work?

Ideally, The query would be by group Id where the customer has invoices (count > 0), then ordered by customer name and invoice due date. That all seems straight forward to me -- I am doing the ordering and exclusionary where after I get the initial set. However, I still get the n+1 problem.

In the profiler, I see it doing the join from the customer to the invoices. However, it then proceeds to get the details of each invoice.

I suspect this is because in my code I translate my model from the Data Model (mapped to nhibernate) to a view model (not mapped to nhibernate) in an attempt to separate the data so that it will not call back to the database.

I need some guidance on how to handle the data so I can loop through the data set (foreach customer foreach invoice) to render my page. Here is the linq that does the transformation.

selected invoices is a dictionary storing invoices that are selected on the front end to be paid. When I load the page, I want to grab the selected invoices to not if they are being paid, how much is being applied, and some other information.

var customerModels = from c in customers
     let invoices = c.Invoices
     select new CustomerModel()
     {
         CustomerNumber = c.CustomerNumber,
         CustomerId = c.CustomerId,
         Name = c.Name.DisplayName,
         Invoices = (from i in invoices
                    join s in selectedInvoices on i.InvoiceId equals s.Key into selected
                    from inv in selected.DefaultIfEmpty()
                    select new InvoiceModel()
                    {
                        Amount = i.Amount,
                        ClosedAmount = i.ClosedAmount,
                        DueDate = i.DateDue,
                        InvoiceDate = i.InvoiceDate,
                        InvoiceId = i.InvoiceId,
                        InvoiceNumber = i.InvoiceNumber,
                        OpenAmount = i.OpenAmount,
                        Condensed = false,

                        Selected = inv.Key > 0,
                        ReasonValue = inv.Key > 0 ? inv.Value.Item3 : String.Empty,
                        OtherReason = inv.Key > 0 ? inv.Value.Item4 : String.Empty,
                        PaymentAmount = inv.Key > 0 ? inv.Value.Item2 : i.OpenAmount
                    }).Sort(sortIndex.Value, sortOrder.Value).ToList(),

         EnableReason = enableReasons,
         EnableReasonSelector = enableReasonSelector,
         Reasons = reasons,
         Condensed = false,

         SortIndex = sortIndex.Value,
         SortOrder = newSortOrder
     };

model.Customers = customerModels.ToList();

I do this because I assumed that the .ToList() would cause the data to transform immediately and separate from nhibernate and not have to perform the n+1 calls to the database. However it still manages to make those calls.

Answer 1

I see that you are using Composite ID for Invoice. Maybe you are affected with the Equals() problem.

In summary, you must have an override of GetHashCode() and Equals() that knows how to do comparison on all properties of composite ID.

Stuart's answer links to the NHibernate and Composite Keys post on nhibernate.info where you can find additional information.