使用连接强制转换linq到实体模型

Question

我有两个实体，它们之间有外键，佣金和发票。我试图使用ViewModel来包含来自连接的数据，但我不知道如何转换它。如果我只使用关系映射，它每次被调用时都会做一个选择，这很恶心。

var commissions = from comm in db.Lead_Commission_Request
                  join inv in db.Lead_Referral_Invoice on comm.lead_commission_request_id equals inv.lead_commission_request_id
                  where inv.invoice_status_id == 0
                  select new InvoiceView
                  {
                      commission = comm,
                      invoices = inv
                  };

我已经得到它的工作使用子选择和转换到IEnumerable<>，但这仍然看起来很尴尬。

var commissions = from comm in db.Lead_Commission_Request
                  join inv in db.Lead_Referral_Invoice on comm.lead_commission_request_id equals inv.lead_commission_request_id
                  where inv.invoice_status_id == 0
                  select new InvoiceView
                  {
                      commission = comm,
                      invoices = (from invb in db.Lead_Referral_Invoice where comm.lead_commission_request_id == invb.lead_commission_request_id select invb)
                  };
public class InvoiceView
{
    public Lead_Commission_Request commission;
    public IEnumerable<Lead_Referral_Invoice> invoices;
}

Answer 1

使用GroupBy将您的行放在一起:

var commissions = from comm in db.Lead_Commission_Request
                  join inv in db.Lead_Referral_Invoice on comm.lead_commission_request_id equals inv.lead_commission_request_id
                  where inv.invoice_status_id == 0
                  group new { comm, inv } by comm.Id into g
                  select new InvoiceView
                  {
                      commission = g.FirstOrDefault().comm,
                      invoices = g.Select(x => x.inv)
                  };