c# - Nhibernate n+1 具有三元关系。想要三元中的中间实体

Question

我在使用 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();
    }

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

我想做的是显示一组中所有成员的所有发票，按客户分隔(按客户名称排序)，然后按到期日排序发票。

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

客户名称(编号)

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

理想情况下，查询将按客户有发票(计数 > 0)的组 ID，然后按客户名称和发票到期日排序。这一切对我来说似乎都是直截了当的——我在获得初始设置后进行排序和排除。但是，我仍然遇到 n+1 问题。

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

我怀疑这是因为在我的代码中我将我的模型从数据模型(映射到 nhibernate)转换为 View 模型(未映射到 nhibernate)以尝试分离数据，这样它就不会回调到数据库。

我需要一些关于如何处理数据的指导，以便我可以遍历数据集(foreach customer foreach invoice)来呈现我的页面。这是执行转换的 linq。

selected invoices是一个字典，存放的是前端选中的要支付的发票。当我加载页面时，我想获取选定的发票，而不是它们是否已付款、应用了多少以及其他一些信息。

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 次调用。然而，它仍然设法进行这些调用。

Answer 1

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

总而言之，您必须重写 GetHashCode() 和 Equals()，它知道如何对复合 ID 的所有属性进行比较。

Stuart's answer链接到 NHibernate and Composite Keys在 nhibernate.info 上发帖您可以在哪里找到更多信息。