NHibernate的N + 1三元关系。要在三元中间实体 [英] Nhibernate n+1 with ternary relationship. Want the middle entity in the ternary
问题描述
我有一个巨大的问题与nhibernate n + 1和没有我尝试似乎解决这个问题。 Nhibernate分析器仍然显示n + 1个选择击中数据库。
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.
这是我的模型：
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>();
}
}
我的客户
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; }
}
以及选项
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;
}
}
最后，我的发票
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();
}
}
现在我的客户映射
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();
}
}
和我的发票映射
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:
客户名称（号码）
- 发票1信息
- 发票2信息
- 发票3信息
下一个客户（号码）
- 发票A信息
- 发票B信息
- 发票C信息
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>();
这仍然会导致N + 1问题。有关如何使查询工作的任何想法？
This still causes an N+1 issue. Any ideas on how to make the query work?
理想情况下，查询将按组Id，其中客户有发票（计数> 0），然后按客户名称和发票到期日。所有这一切似乎直接到我 - 我在做排序和排斥，在我得到初始设置后。然而，我仍然得到n + 1的问题。
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.
我怀疑这是因为在我的代码中，我将我的模型从数据模型（映射到nhibernate）翻译成一个视图模型（未映射到nhibernate）尝试分离数据，以便它不会回调到数据库。
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.
我需要一些指导如何处理数据使我可以循环通过数据集（foreach客户foreach发票）来呈现我的页面。这里是进行转换的linq。
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();
我这样做是因为我假设 .ToList c $ c>会导致数据立即转换并与nhibernate分开，而不必对数据库执行n + 1调用。
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.
推荐答案
我看到您正在使用发票的复合ID。也许您受到 Equals（）
I see that you are using Composite ID for Invoice. Maybe you are affected with the Equals() problem.
总之，您必须覆盖 GetHashCode（） code> Equals（）知道如何对复合ID的所有属性进行比较。
In summary, you must have an override of GetHashCode() and Equals() that knows how to do comparison on all properties of composite ID.
Stuart的回答链接到 NHibernate和Composite Keys 在 nhforge 上发布，其中可以找到更多信息。
Stuart's answer links to the NHibernate and Composite Keys post on nhforge where you can find additional information.
这篇关于NHibernate的N + 1三元关系。要在三元中间实体的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
