Why can't Entity Framework translate this linq query? "The LINQ expression could not be translated."

Question

Why can't this query be translated? Just with one "First()" it works, with two it doesn't:

Exception:

The LINQ expression 'DbSet (whole query here) could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync*

await RecipesComparatorContext.ItemRecipesComparisonResults
    .Where(compResult => (compResult.IdItemRecipeFromNavigation // From or two the same, both exist
                                                   .ItemRecipesTreeIdItemRecipeParentNavigations.First() 
                                                   .ItemRecipesTreesRoots.First()
                                                   .IdItemRecipeRootNavigation
                                                   .MRPublishedVersions.First().PlantName == plantName)
                                                   
                            )
            )
    .ExecuteUpdateAsync(result => result.SetProperty(r => r.InvalidState, true));

If it's solved with the ToListAsync(), where should I put it?

The Model is as follow:

public partial class ItemRecipesComparisonResult
{
    public int IdItemRecipesComparisonResult { get; set; }
    public int IdItemRecipeFrom { get; set; }
    public int IdItemRecipeTo { get; set; }
    public bool InvalidState { get; set; }
    public virtual ItemRecipe IdItemRecipeFromNavigation { get; set; }
    public virtual ItemRecipe IdItemRecipeToNavigation { get; set; }
}


public partial class ItemRecipe
{
    public ItemRecipe()
    {
        ItemRecipesTreeIdItemRecipeChildNavigations = new HashSet<ItemRecipesTree>();
        ItemRecipesTreeIdItemRecipeParentNavigations = new HashSet<ItemRecipesTree>();
        MRPublishedVersions = new HashSet<MRPublishedVersion>();
    }

    public int IdItemRecipe { get; set; }
    
    public virtual ICollection<ItemRecipesTree> ItemRecipesTreeIdItemRecipeChildNavigations { get; set; }
    public virtual ICollection<ItemRecipesTree> ItemRecipesTreeIdItemRecipeParentNavigations { get; set; }
    
    public virtual ICollection<ItemRecipesTreesRoot> ItemRecipesTreesRoots { get; set; }
    public virtual ICollection<MRPublishedVersion> MRPublishedVersions { get; set; }
}

public partial class ItemRecipesTree
{
    public ItemRecipesTree()
    {
        ItemRecipeInstances = new HashSet<ItemRecipeInstance>();
        ItemRecipesTreesRoots = new HashSet<ItemRecipesTreesRoot>();
    }

    public int IdItemRecipeParent { get; set; }
    public int IdItemRecipeChild { get; set; }
    public virtual ItemRecipe IdItemRecipeChildNavigation { get; set; }
    public virtual ItemRecipe IdItemRecipeParentNavigation { get; set; }
    public virtual ICollection<ItemRecipesTreesRoot> ItemRecipesTreesRoots { get; set; }
}


public partial class ItemRecipesTreesRoot
{
    public int IdItemRecipeTreeRoot { get; set; }
    public int IdItemRecipeParent { get; set; }
    public int IdItemRecipeChild { get; set; }
    public int IdItemRecipeRoot { get; set; }
    public virtual ItemRecipesTree IdItemRecipeNavigation { get; set; }
    public virtual ItemRecipe IdItemRecipeRootNavigation { get; set; }
}

public partial class MRPublishedVersion
{
    public int idVersion {get;set;}
    public string PlantName {get;set;}
}

Answer 1

Rewrite query in the following way, it should be translatable:

var itemsToUpdate = RecipesComparatorContext.ItemRecipesComparisonResults
    .Where(c => c.IdItemRecipeFromNavigation.ItemRecipesTreeIdItemRecipeParentNavigations 
        .SelectMany(p => p.ItemRecipesTreesRoots)
        .SelectMany(r => r.IdItemRecipeRootNavigation.MRPublishedVersions)
        .Any(v => v.PlantName == plantName)
    );

await itemsToUpdate.ExecuteUpdateAsync(result => result.SetProperty(r => r.InvalidState, true));