How to query on child records which are coming from `.Include`?

Question

I am leveraging EF Core and using .Include to get data from two tables by using some filters. Here, I have a filter which requires to query on child table (the one I am getting from .Include) and I am not sure how to do that.

As shown below: my filters are, location, stockNumber, exceptionType, supplierName where I want to run my query for supplier but, not sure how to do that.

Table Design:

public class Operation
{
    [Key]
    public int OperationId { get; set; }
    [Required, MaxLength(10), Unicode(false)]
    public string Location { get; set; }
    [Required]
    public long StockNumber { get; set; }
    [Required, MaxLength(17), Column(TypeName = "char(17)")]
    public string Vin { get; set; }
    public decimal TotalCarliCost { get; set; }
    public decimal TotalCostSubmitted { get; set; }
    public DateTimeOffset? EarliestPaymentDate { get; set; }
    public bool? HasPartLineItems { get; set; }
    public bool? HasSubletItems { get; set; }
    [Required]
    public List<SupplierByOperation> Suppliers { get; set; }
}

public class SupplierByOperation
{
    [Key] 
    public int SupplierByOperationId { get; set; }
    [Required]
    public long StockNumber { get; set; }
    [Required, MaxLength(500), Unicode(false)]
    public string SupplierName { get; set; }
    public bool? TiedToPart { get; set; }
    public bool? TiedToSublet { get; set; }
    public int OperationId { get; set; }
}

Repo.cs

private IQueryable<Operation> GenerateBaseQuery(string location, string stockNumber = "", 
string exceptionType = "", string supplierName = "")
{
    var query = _dbContext.Operation.AsNoTracking()
        .Include(x => x.Suppliers)
        .Where(_ => _.Location.Equals(location));

    if (string.IsNullOrWhiteSpace(stockNumber)) return query;
    var convertedStockNumber = long.Parse(stockNumber);
    query = query.Where(e => e.StockNumber.Equals(convertedStockNumber));
    
    if (string.IsNullOrWhiteSpace(exceptionType)) return query;
    query = exceptionType.ToLower() switch
    {
        // TODO: use constants
        "parts" => query.Where(e => e.HasPartLineItems.Equals(true)),
        "sublets" => query.Where(e => e.HasSubletItems.Equals(true)),
        "all" => query.Where(e => e.HasPartLineItems.Equals(true) && e.HasSubletItems.Equals(true)),
        _ => query
    };
    
    // Following throws these 2 errors
 // Cannot implicitly convert type 'System.Collections.Generic.IEnumerable<SupplierByOperation>' to 'bool'

 // Cannot convert lambda expression to intended delegate type because some of the return types in the block are not implicitly convertible to the delegate return type
    if (string.IsNullOrWhiteSpace(supplierName)) return query;
    query = query.Where(o => o.Suppliers.Where(s => s.SupplierName == supplierName));

    return query;
}

What you want to use is [filtered `Include`](https://learn.microsoft.com/en-us/ef/core/querying/related-data/eager#filtered-include). — NetMage, Apr 21 '23 at 20:30

abolfazl sadeghi · Accepted Answer · 2023-04-21T20:55:49.980

1

if you want filter child,i think this code can help you


        query = query.Where(o => o.Suppliers.Any(s => s.SupplierName == supplierName));

if you want filtered Include,i think this code can help you

 query = query.Include(o => o.Suppliers.Where(s => s.SupplierName == supplierName));

Filtering on Include in EF Core

https://learn.microsoft.com/en-us/ef/core/querying/related-data/eager#filtered-include

edited Apr 21 '23 at 20:55

answered Apr 21 '23 at 20:50

abolfazl sadeghi

2,277
2
12
20

1

I need filtered query, one which worked. Thank you. – GThree Apr 21 '23 at 21:06

score -1 · Answer 2 · answered Apr 21 '23 at 19:37

-1

After the .Include() use .ThenInclude(). More information here

answered Apr 21 '23 at 19:37

idvr

1

1

how would that help me in query? i.e., I still see `Suppliers` in my `query = query.Where(x => x.Suppliers)` (still seeing suppliers, nothing from .ThenInclude`. Example will be great. – GThree Apr 21 '23 at 19:57
Sorry I think I misunderstood. In the where put .Where(o=>o.Suppliers.Where(s=>s.SupplierName==supliersName). It’s a nested where, instead of select – idvr Apr 21 '23 at 20:03
That was typo (question updated). I tried that already. It throws these 2 errors: `Cannot implicitly convert type 'System.Collections.Generic.IEnumerable' to 'bool' Cannot convert lambda expression to intended delegate type because some of the return types in the block are not implicitly convertible to the delegate return type` – GThree Apr 21 '23 at 20:15
I think I got it this time .Where(o=>o.Suppliers.Any(s=>s.SupplierName==supliersName)) – idvr Apr 21 '23 at 20:28
your `.Any()` approach worked. But I needed filtered one so picked other answer. – GThree Apr 21 '23 at 21:08
@idvr: Please [edit] your answer to reflect your new understanding of the question and the solution you believe to best address it. Future readers shouldn't need to read through comments to find the answer. – Jeremy Caney Apr 25 '23 at 00:19
As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Apr 26 '23 at 00:41

How to query on child records which are coming from `.Include`?

2 Answers2