What is the right syntax for this joined EF Linq query

Question

I am trying to get a query that returns everything properly formatted for my ViewModel so I do not have to manually copy everything over from my entity models. I have this Linq query that is giving me an error. :

var query = from i in context.Invoices 
            join l in context.LineItems on i.InvoiceID equals l.InvoiceID into il
            where i.InvoiceID == id
            select new InvoiceViewModel()
                {
                InvoiceID = i.InvoiceID,
                CustomerID = i.CustomerID,
                InvoiceNote = i.Note,
                InvoiceDate = i.InvoiceDate,
                Terms = i.Terms,
                LineItems = il.ToList<LineItemViewModel>()
                };

This is my ViewModel

 public class InvoiceViewModel        {
    public int InvoiceID { get; set; }
    public int CustomerID { get; set; }
    public string InvoiceNote { get; set; }
    public DateTime InvoiceDate { get; set; }
    public string Terms { get; set; }
    public virtual ICollection<LineItemViewModel> LineItems { get; set; }
    }

public class LineItemViewModel        {
    public int LineItemID { get; set; }
    public int InvoiceID { get; set; }
    public int Quantity { get; set; }
    public string Item { get; set; }
    public decimal Amount { get; set; }
    public string LineItemNote { get; set; }
    }

The error I am getting is (the red squigly is under the il in LineItems = il.ToList())

'IEnumerable<LineItem>' does not contain a definition for 'ToList' and the best extension method overload 'Enumerable.ToList<LineItemViewModel>(IEnumerable<LineItemViewModel>)' requires a receiver of type 'IEnumerable<LineItemViewModel>'

Which I (sorta, kinda, a little) understand. So what is the proper syntax for this?

Answer 1

You need to explicitly initialize your LineItemViewModel instances from the LineItem entities. You might be better off writing this as a correlated subquery rather than a join:

var query = 
        from i in context.Invoices 
        where i.InvoiceID == id
        select new InvoiceViewModel()
        {
            InvoiceID = i.InvoiceID,
            CustomerID = i.CustomerID,
            InvoiceNote = i.Note,
            InvoiceDate = i.InvoiceDate,
            Terms = i.Terms,
            LineItems = 
            (
                from li in context.LineItems
                where li.InvoiceID == i.InvoiceID
                select new LineItemViewModel
                {
                    LineItemID = li.LineItemID, 
                    InvoiceID = li.InvoiceID,
                    Quantity = li.Quantity,
                    Item = li.Item,
                    Amount = li.Amount,
                    LineItemNote = li.LineItemNote,
                }
            ).ToList()
        };