what is best approach to query based on navigation property with EntityFramework

90 Views Asked by At

Consider these two classes:

class Invoice {
    // some props ... 
    public virtual List<InvoiceStatusLog> StatusLogs { get; set; }
}

class StatusLogs {
    public InvoiceStatus Status { get; set; }
    public int InvoiceID { get; set; }
    public virtual Invoice Invoice { get; set; }
}

What I want is to get invoices by their last status witch is last StatusLog status. I can do something like this:

var invoices = repository.SelectList(c => (c.Date >= _fromDate && c.Date <= _toDate)
             && (c.StatusLogs.OrderBy(l => l.CreateDate).Last().Status == InvoiceStatus.Paid || c.StatusLogs.OrderBy(l => l.CreateDate).Last().Status == InvoiceStatus.MinorPaid));

However, I really don't think this would be good idea as matter of performance and coding (repeating some expression everywhere ).

I know I can write a function which generates an expression based on status and can use that expression in body of selectList(), but first we have various conditions based on status ( && ... || && && .. ) and cant be generalized, and second: again I don't believe something like:

c.StatusLogs.OrderBy(l => l.CreateDate).Last().Status == InvoiceStatus.MinorPaid

would have good performance. I would appreciate any suggestion

0

There are 0 best solutions below