具有三元关系的 Nhibernate n+1.想要三元中的中间实体

我在使用nhibernate n + 1时遇到了很大的问题，但我尝试的似乎都没有解决问题。 Nhibernate探查器仍然显示n+1选择命中数据库。

这是我的模型：

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();
    }

我必须连接到这三个字段的其他表，因为我们的一位客户完全转储了他们的数据并从头开始重新加载所有发票（不要问为什么）。因此，为了保持连接，我在允许新发票与刷新的数据挂钩的字段上联接到这些表。

我正在尝试做的是显示组中所有成员的所有发票，按客户分隔（按客户名称排序），然后按截止日期订购发票。

所以，我的网站看起来像这样：

客户名称（编号）

发票 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 问题。关于如何使查询工作的任何想法？

理想情况下，查询将按客户有发票的组 ID 进行（计数> 0），然后按客户名称和发票到期日期排序。这一切对我来说似乎很简单——我正在做排序和排除，在我得到初始设置之后。但是，我仍然遇到 n+1 问题。

在探查器中，我看到它正在执行从客户到发票的联接。但是，它随后继续获取每张发票的详细信息。

我怀疑这是因为在我的代码中，我将我的模型从数据模型（映射到 nhibernate

）转换为视图模型（未映射到 nhibernate），以尝试分离数据，以便它不会回调到数据库。

我需要一些有关如何处理数据的指导，以便我可以循环浏览数据集（针对每个客户的每个发票）来呈现我的页面。下面是执行转换的 linq。

所选发票是存储要在前端选择要支付的发票的字典。当我加载页面时，我想获取选定的发票，而不是它们是否被支付，正在应用多少以及其他一些信息。

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()会导致数据立即转换并与 nhibernate 分离，而不必对数据库执行 n+1 调用。但是，它仍然设法拨打这些电话。

我看到您正在为发票使用复合 ID。也许您受到Equals()问题的影响。

总之，您必须具有知道如何对复合 ID 的所有属性进行比较的 GetHashCode() 和 Equals() 的覆盖。

Stuart的回答链接到 nhibernate.info 上的NHibernate和Composite Keys帖子，您可以在其中找到更多信息。

相关内容

最新更新

热门标签：