I have a huge problem with nhibernate n + 1, and nothing I try seems to fix the problem. The Nhibernate profiler still shows that n + 1 selects hits on the database.
Here is 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 client
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 parameters
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 client 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 display of invoices
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 need to join the other tables of these three fields, because one of our customers completely discards their data and reloads all invoices from scratch (do not ask why). Thus, in order to keep in touch, I join these tables in the fields, which allow the new account to collect backups with updated data.
What I'm trying to do is show all invoices for all group members separated by customer (sorted by customer name) and then order invoices by date.
so my site looks something like this:
Customer Name (Number)
- Invoice Information 1
- Invoice Information
- Invoice Information
Next customer (number)
- Invoice Information
- Invoice Information
- Invoice Information
So I made a request
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 N + 1 problem. Any ideas on how to make the request work?
Ideally, the request will be by the identifier of the group where the customer has an invoice (count> 0), and then ordered by the customer’s name and the date of the invoice. All this seems straightforward to me - I make an order and an exception, where after receiving the initial set. However, I am still getting n + 1 problem.
In the profiler, I see that he is making a connection with the client on the invoice. However, he then receives information about each invoice.
I suspect this is due to the fact that in my code I was translating my model from a data model (mapped in nhibernate) to a presentation model (not mapped in nhibernate), trying to separate the data so that it would not call the database back.
I need some data processing guide so that I can iterate over the data set (foreach client foreach invoice) to display my page. Here is the linq that does the conversion.
Selected invoices is a dictionary that stores invoices that are selected on the front side for payment. When I load the page, I want to capture the selected invoices, if they are not paid, how much is 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 suggested that .ToList() will immediately convert the data and separate it from nhibernate and will not make n + 1 calls to the database. However, he still manages to complete these challenges.