c# – 具有三元关系的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","InvoiceNumber")
            .Cascade.All();
    }

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

我要做的是显示一个组中所有成员的所有发票,由客户分开(按客户名称排序),然后按截止日期订购发票.

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

客户名称(编号)

>发票1信息
>发票2信息
>发票3信息

下一位客户(编号)

>发票信息
>发票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),试图分离数据,以便它不会回调到数据库.

我需要一些关于如何处理数据的指导,以便我可以遍历数据集(foreach customer foreach invoice)来呈现我的页面.这是进行转换的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,SortIndex = sortIndex.Value,SortOrder = newSortOrder
     };

model.Customers = customerModels.ToList();

我这样做是因为我假设.ToList()会导致数据立即转换并与nhibernate分离,而不必对数据库执行n 1次调用.然而,它仍然设法进行这些调用.