Filter list by grandchild using LINQ

Question

I need to filter a list by the DamageCodeName field in the DamageCode class.

public partial class DamageCategory
{
    public string DamageCategoryId { get; set; }
    public string CategoryName { get; set; }
}

public partial class DamageGroup
{
    public string DamageGroupId { get; set; }
    public string DamageCategoryId { get; set; }
    public string GroupName { get; set; }
}

public partial class DamageCode
{
    public string DamageCodeId { get; set; }
    public string DamageGroupId { get; set; }
    public string DamageCodeName { get; set; }
}

I pull the records using EF CORE 5 into a list:

private List<DamageCategory> _DamageCodeList { get; set; } = new();

_DamageCodeList = _contextDB.DamageCategories
                            .Include(i => i.DamageGroups)
                            .ThenInclude(d => d.DamageCodes).AsSingleQuery().ToListAsync();

Now I need to filter this list by the DamageCode.DamageCodeName property.

private string _SearchText { get; set; } = "Bubble";
private List<DamageCategory> _CategoryList { get; set; } = new();

_CategoryList = _DamageCodeList.Where(g => g.DamageGroups.SelectMany(c => c.DamageCodes
                               .Where(w => w.DamageCodeName.ToLower().Contains(_SearchText.ToLower()))).Any()).ToList();

The code above only filters for the DamageCategory. It brings back all the records for the DamageGroup and all the records for the DamageCodes.

I need the linq query result to produce a list like the one below (Filtered by "Bubble") and bring back only the DamageCategory, DamageGroup, and DamageCodes filtered by DamageCode.DamageCodeName.Contains("Bubble"):

Here is the SQL that produces the result above that I need:

SELECT 
    CT.[DamageCategoryID],
    CT.[CategoryName],
    DG.[DamageGroupID],
    DG.[DamageCategoryID],
    DG.[GroupName],
    DC.[DamageCodeID],
    DC.[DamageGroupID],
    DC.[DamageCodeName]
  FROM 
    [dbo].[DamageCategory] AS CT
    INNER JOIN [dbo].[DamageGroup] AS DG ON CT.[DamageCategoryID] = DG.[DamageCategoryID]
    INNER JOIN [dbo].[DamageCode] AS DC ON DG.[DamageGroupID] = DC.[DamageGroupID]
  WHERE 
    DC.[DamageCodeName] LIKE '%Bubble%'

Answer 1

This is where query syntax shines.

from dc in _contextDB.DamageCategories
from dg in dc.DamageGroups
from dc in dg.DamageCodes
where dc.DamageCodeName.Contains("Bubble")
select new
{
    dc.DamageCategoryID,
    dc.CategoryName,
    dg.DamageGroupID,
    dg.DamageCategoryID,
    dg.GroupName,
    dc.DamageCodeID,
    dc.DamageGroupID,
    dc.DamageCodeName
}

The query shape from ... from is the query syntax equivalent of SelectMany.

You use ToLower in your code. That may not be necessary. The query is translated into SQL and if the database field has a case-insensitive collation you don't need ToLower.