Entity Framework include children that grand children are null

asp.net-core c# entity-framework-6 linq

Question

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()); } }
}
1
1
3/2/2017 7:09:51 AM

Accepted Answer

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.

2
3/20/2017 7:03:39 AM

Popular Answer

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


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow