如何使用 LINQ/Lambda 从关系导航键中获取"多选"?

Question

我们与实体模型有不同的关系。我想找到以下内容。

表：客户

[Table("CustomerTable")]
public partial class CustomerTable
{
[Key]
public int Id { get; set; }
[Required]
[StringLength(50)]
public string Name { get; set; }
[Required]
[StringLength(20)]
public string Country { get; set; }

public virtual CompanyTable CompanyTable { get; set; }
public virtual CurrencyTable CurrencyTable { get; set; }
public virtual LoginTable LoginTable { get; set; }
public virtual ICollection<ProcessTable> ProcessTables { get; set; }
}

表：公司表

[Table("CompanyTable")]
public partial class CompanyTable
{
public CompanyTable()
{
CustomerTables = new HashSet<CustomerTable>();
ProcessTables = new HashSet<ProcessTable>();
DateRegistered = DateTime.Now;
PaymentDate = DateTime.Now;
}
[Key]
public int Id { get; set; }
[Required]
[StringLength(50)]
public string Name { get; set; }
public bool IsActive { get; set; }
public bool IsDeleted { get; set; }
public virtual ICollection<CustomerTable> CustomerTables { get; set; }
public virtual ICollection<ProcessTable> ProcessTables { get; set; }
}

表：流程

[Table("ProcessTable")]
public partial class ProcessTable
{
[Key]
public int Id { get; set; }
[ForeignKey("CustomerTable")]
[Required]
public int CustomerId { get; set; }

[Required]
public DateTime DateCreated { get; set; }

[ForeignKey("CompanyTable")]
[Required]
public int Company { get; set; }

[Required]
public int TotalPayment { get; set; }
public int PaymentReceived { get; set; }// Store time of create process,1st time only
public virtual CompanyTable CompanyTable { get; set; }
public virtual CustomerTable CustomerTable { get; set; }
public virtual ICollection<ProcessPaymentMapping> 
ProcessPaymentMappingList { get; set; }
}

表：流程付款映射(用于付款历史记录)

[Table("ProcessPaymentMapping")]
public partial class ProcessPaymentMapping
{
public ProcessPaymentMapping()
{
CreatedOn = DateTime.Now;
isDeleted = false;
}
[Key]
public int Id { get; set; }
[ForeignKey("ProcessTable")]
public int ProcessId { get; set; }
[Required]
public decimal PaymentReceived { get; set; }
public string Remarks { get; set; }
public DateTime CreatedOn { get; set; }
public bool isDeleted { get; set; }
public virtual ProcessTable ProcessTable { get; set; }
}

我想显示客户明智的记录，包括剩余付款和总付款。

• 公司有权创建新客户。(我们正在存储的时间 同时来自客户的总付款和初始给定付款 将初始值存储到流程付款映射表)

• 一家公司有多个流程。

• 一个流程有多个流程付款映射记录。
• 一个客户有一个或多个由公司注册的流程，
• 流程付款映射表存储带有日期的付款历史记录
• 每次客户为运行过程付费。

流程表仅存储了公司注册的新流程时的总金额值

我想显示客户明智的付款，如下所示。

Index | Customer Name | Process ID | Total Amount | Paid Amount | Remaining Amount 
1. | Jhon Michel  | 544545 | 150 USD | 100 USD | 50 USD
2. | Arena Bosch  | 544546 | 200 USD | 50 USD | 150 USD

喜欢明智。 如何在实体中使用分组..？

Answer 1

试试这个

CompanyTable companyTable = new CompanyTable();
var results = (from ct in companyTable.CustomerTables
join pt in companyTable.ProcessTables on ct.Id equals pt.CustomerId
select new { customerName = ct.CompanyTable.Name, processId = pt.Id, totalPayment = pt.TotalPayment, paidAmount = pt.PaymentReceived, remainingAmount = pt.TotalPayment - pt.PaymentReceived }).ToList();

---

如果你还想要索引，那么使用这个

var results = (from ct in companyTable.CustomerTables
join pt in companyTable.ProcessTables on ct.Id equals pt.CustomerId
select new { customerName = ct.CompanyTable.Name, processId = pt.Id, totalPayment = pt.TotalPayment, paidAmount = pt.PaymentReceived, remainingAmount = pt.TotalPayment - pt.PaymentReceived })
.Select((x,i) => new { index = i, customerName = x.customerName, processId = x.processId, totalPayment = x.totalPayment,  paidAmount = x.paidAmount, remainingAmount = x.remainingAmount})
.ToList();

---

添加分组依据。 不确定如何处理这三个金额。 猜了一猜

var results = (from ct in companyTable.CustomerTables
join pt in companyTable.ProcessTables on ct.Id equals pt.CustomerId
select new { customerName = ct.CompanyTable.Name, processId = pt.Id, totalPayment = pt.TotalPayment, paidAmount = pt.PaymentReceived, remainingAmount = pt.TotalPayment - pt.PaymentReceived })
.GroupBy(x => x.processId)
.Select((x,i) => new { index = i, customerName = x.FirstOrDefault().customerName, processId = x.FirstOrDefault().processId, 
totalPayment = x.Sum(y => y.totalPayment),  paidAmount = x.Sum(y => y.paidAmount), remainingAmount = x.Sum(y => y.remainingAmount)})
.ToList();