Seem like a simple one, but not coming up with the right thing. All that I need to do is get a count of child items that have it's own child null. Here is what I have been working with:
var data = await _context.award.Include(a => a.expenses.Select(p => p.invoice == null)).ToListAsync();
I have also tried other combinations here with no luck. The error I get is
InvalidOperationException: The property expression 'a => {from expense p in [a].expenses select ([p].invoice == null)}' is not valid. The expression should represent a property access: 't => t.MyProperty'.
I change it to match and it just triggers a new error.
I just want to get a list of award
with it's list of expenses
listed (fine with just the .ID
if that influences the solution) where the invoice
parent object is not set and is null
.
UPDATE requested models
public class invoice
{
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public int ID { get; set; }
[ForeignKey("INV_NUM_ForeignKey")]
public invoice_number fin_invoice_number { get; set; }
}
public class invoice_number
{
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public int ID { get; set; }
public int number { get; set; }
public invoice invoice { get; set; }
public string display { get { return string.Format("sps-{0}", (new String('0', 6) + number.ToString())).Substring(number.ToString().Count()-7, number.ToString().Count()); } }
}
You have to use .Include
together with .ThenInclude
. Docs explains it clearly here (Including multiple levels).
var data = await _context.award
.Include(a => a.expenses)
.ThenInclude(e => e.invoice)
.ToListAsync();
Notice: But notice, that ThenInclude
has two overloads and chances are big, that Visual Studio will select the wrong one or just display one (wrong one) and give you either errors while typing or do not offer autocompetition for e
if e is not a collection. If you ignore the error and type the correct property and close the bracket, the error will disappear.
Try re-writing your code like this
var data = await _context.award.Include(a => a.expenses).Where(p => p.expenses.Any(a => a.invoice == null)).ToListAsync();