Cast a LINQ-to-Entites model with a join

Question

I've got two entities with a foreign key between them, commissions and invoices. I'm trying to use a ViewModel to include the data from the join, but I can't figure out how to cast it. If I just use the relationship mapping it does a select every time it's called, which is gross.

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

I've gotten it working using a subselect and casting to IEnumerable<>, but that still seems awkward.

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

Use GroupBy to put your rows together:

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